SQLite cheatsheet

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:

sqlite3 newDB.sqlite

Then you can use the standard SQL commands like CREATE TABLE, INSERT, SELECT,…

Delete a database

Simply remove the file, on unix-like OS:

rm file.sqlite

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:

.dump [object] 

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 .schema:

Restore a sql dump

To restore a backup from a sql dump, use the command:

.read file

Show tables of a database

To list every table of a database, use the command .tables

On larger (and real) databases with several tables you can filter the output with LIKE wildcards: .tables part_of_tablename%

Get database information

Run the command .dbinfo to get some information about the database:

Get help

I think this is the most useful SQLite command in this article, because helps you with the available commands: .help

To get help on an specific command:

.help .command

Last words

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.