Question
What are some basic commands to work with SQLite databases?
Answer
Many areas of the cPanel product utilize SQLite database due to their portability, speed and reliability. However, sometimes as a server administrator you may need to view or manipulate the database to troubleshoot a problem.
Here are some of the most common or basic commands used for SQLite databases.
Important: If you will be making modifications to the database, we recommend that you always make a backup of the database before proceeding to avoid any unintended data loss.
To enter the SQLite command prompt you can do so as follows:
sqlite3 /$PATH/$TO/$DATABASE.db
| .tables | This command will list all the available tables in the database. When working with a database, this is helpful to know the tables available. |
| .schema | Similarly to the tables command, this will display all the tables along with the structure used to build the table. This is useful if you are looking to modify the data or simply understand the structure of the data |
| pragma integrity_check; | This will test the database for corruption or any other errors. |
| .backup $NEW_DATABASE_NAME.sqlite | This will create a backup of the database in SQLite format. |
| .help | For more options refer to the help command |
| .quit | Finally, this can be used to exit the SQLite command-prompt |
All other commands for obtaining data will be very similar to general SQL commands which you can read more about here:
https://mariadb.com/kb/en/basic-sql-statements/