This article is a short list of useful SQLite commands to make your life easier.
SQLite is an SQL engine intended mainly for embed on systems. It’s serverless, there isn’t a client-server process but direct access to the database file. Also, there aren’t configuration files and the whole system only depends on the C-Library.
Surely you already have a lot of sqlite databases running on your computer or even your phone. But maybe you don’t have the tools to access them, you can download sqlite tools from https://www.sqlite.org/download.html
I think this is enough for an introduction, lets see how to…
Create a a new database
It’s as easy as run:
Then you can use the standard SQL commands like CREATE TABLE, INSERT, SELECT,…
Delete a database
Simply remove the file, on unix-like OS:
On Windows, click on the file and press the delete key.
Dump to SQL
I found this very useful to take backups or to migrate between database systems (i.e. migrate to PostgreSQL or MySQL/MariaDB). Run:
Where object is optional and can be part of the name of table (treat it as sql LIKE statment with % as wildcard), a trigger, etc. By default, sqlite outputs to screen, even sql dumps, to output to a file you first need to run:
.output filename .dump
If you want only the schema but not the data, the dump command is
Restore a sql dump
To restore a backup from a sql dump, use the command:
Show tables of a database
To list every table of a database, use the command
On larger (and real) databases with several tables you can filter the output with LIKE wildcards:
Get database information
Run the command
.dbinfo to get some information about the database:
I think this is the most useful SQLite command in this article, because helps you with the available commands:
To get help on an specific command:
I’m not showing SQL statements like SELECT or UPDATE, because if you are here you probably already know them.
The ones I’ve showed are the most useful sqlite commands… to me; I think that with the .help command and a basic SQL knowledge you can do anything you need on your sqlite databases.