Gonzalo Rivero
I am Gonzalo, I live in Salta, a city located in the NW of Argentina. I play the guitar and a little harmonica. I also like to bike.

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,…

screenshot of a newly created sqlite database.useful SQLite commands
Creating your first sqlite database

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
Dump a sqlite database.useful SQLite commands
Dump a sqlite database to sql commands.

If you want only the schema but not the data, the dump command is .schema:

showing only the database schema. useful SQLite commands

Restore a sql dump

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

.read file
loading a sql dump into a new database. useful SQLite commands
loading a sql dump into a new database

Show tables of a database

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

showing the tables of a database. useful SQLite commands
showing the tables of a database

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:

database information
database information

Get help

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

listing available commands

To get help on an specific command:

.help .command
getting help on the .dump command
getting help on the .dump 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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest articles

x