Practical SQLite Commands You Don't Want To Miss

Summary: in this tutorial, you will learn how to use the sqlite3 command-line tool to interact with SQLite database files using commands.

The SQLite project offers a simple command-line tool called sqlite3 (or sqlite3.exe on Windows), which allows you to interact with SQLite databases effectively.

Opening sqlite3 tool #

First, open your terminal.

Second, type the sqlite3 command as follows:

sqlite3

Output:

SQLite version 3.44.3 2024-03-24 21:15:01 (UTF-16 console I/O)
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

By default, an SQLite session uses an in-memory database. It means that all the changes you make will be gone when the session ends.

The sqlite3 will start with the following command prompt waiting for your command:

sqlite>

Getting helps #

To show all available sqlite3 commands and their purposes, you use the .help command as follows:

.help

Note that you don’t add a semicolon (;) for the command that starts with the dot (.) like .help.

Quitting sqlite3 tool #

To exit the sqlite3 program, you use the .quit command.

.quit

Creating a new SQLite database #

If you launch the sqlite3 with an SQLite database file that does not exist, the sqlite3 tool will create the database.

For example, the following command creates a database named sales in the C:\sqlite\db\ directory:

sqlite3 c:\sqlite\db\sales.db

Output:

SQLite version 3.44.3 2024-03-24 21:15:01 (UTF-16 console I/O)
Enter ".help" for usage hints.
sqlite>

Opening a SQLite database file using sqlite3 #

To open an existing database file, you use the .opencommand:

.open FILENAME

The FILENAME specifies the SQLite database you want to open.

For example, the following statement opens the chinook.db database located at c:\sqlite\db\chinook.db:

First, launch the sqlite3 tool from your terminal:

sqlite3

Second, open the chinook.db database file:

.open c:\sqlite\db\chinook.db

If you want to open a specific database file when connecting to the SQLite database, you use the following command:

sqlite3 c:\sqlite\db\chinook.db

Output:

SQLite version 3.44.3 2024-03-24 21:15:01 (UTF-16 console I/O)
Enter ".help" for usage hints.
sqlite>

Displaying the current database #

To show all databases in the current connection, you use the .databases command.

The .databases command displays at least one database with the name: main. For example, the following command shows all the databases of the current connection:

.databases

Output:

main: c:\sqlite\db\sales.db r/w

To add a database to the current connection, you use the statement attach databasecommand.

For example, the following command adds the chinook database to the current session.

attach database "c:\sqlite\db\chinook.db" AS chinook;

Now if you run the .database command again, the sqlite3 returns two databases: main and chinook.

.databases

Output:

main: c:\sqlite\db\sales.db r/w
chinook: c:\sqlite\db\chinook.db r/w

To save time typing previous commands, you can use the up and down arrow keys to navigate between them.

Showing tables in a database #

To display all the tables in the current database, you use the .tables command:

.tables

The following commands open a new database connection to the chinook database and display the tables:

Step 1. Open the chinook database:

sqlite3 c:\sqlite\db\chinook.db

Step 2. Show all the tables:

.tables

Output:

albums          employees       invoices        playlists
artists         genres          media_types     tracks
customers       invoice_items   playlist_track

If you want to find tables based on a specific pattern, you use the .table pattern command. The sqlite3 uses the LIKE operator for pattern matching.

For example, the following statement returns the table that ends with the string es:

.table '%es'

Output:

employees    genres       invoices     media_types

Showing table structures #

To display the structure of a table, you use the .schema TABLE command:

.schema TABLE

The TABLE argument can be the table name or a pattern that specifies one or more tables. For example, the following command shows the structure of the albums table:

.schema albums

Output:

CREATE TABLE IF NOT EXISTS "albums"
(
    [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Title] NVARCHAR(160)  NOT NULL,
    [ArtistId] INTEGER  NOT NULL,
    FOREIGN KEY ([ArtistId]) REFERENCES "artists" ([ArtistId])
                ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE INDEX [IFK_AlbumArtistId] ON "albums" ([ArtistId]);

If you omit the TABLE, the .schema command will show the structures of all the tables.

.schema

To show the schema and the content of the sqlite_stat tables, you use the .fullschema command.

.fullschema

Showing indexes #

To show all indexes of the current database, you use the .indexes command as follows:

.indexes

Output:

IFK_AlbumArtistId                  IFK_PlaylistTrackTrackId
IFK_CustomerSupportRepId           IFK_TrackAlbumId
IFK_EmployeeReportsTo              IFK_TrackGenreId
IFK_InvoiceCustomerId              IFK_TrackMediaTypeId
IFK_InvoiceLineInvoiceId           sqlite_autoindex_playlist_track_1
IFK_InvoiceLineTrackId

If you are unfamiliar with indexes, you can learn about them in the SQLite indexes tutorial.

To show the indexes of a specific table, you use the .indexes TABLE command:

.indexes TABLE

For example, to show indexes of the albums table, you use the following command:

.indexes albums

Output:

IFK_AlbumArtistId

Executing an SQL statement #

To execute an SQL statement, you type it in the sqlite3> command and hit the Enter key to start a new line:

sqlite> select *
      > (type the next clause)

As soon as you terminate the SQL statement with a semicolon (;), and hit the Enter key, sqlite3 will execute the statement.

For example:

sqlite> select *
   ...> from albums
   ...> order by AlbumId
   ...> limit 3;

Output:

1|For Those About To Rock We Salute You|1
2|Balls to the Wall|2
3|Restless and Wild|2

The default output is not clear.

Formatting query output #

To format the output of a query, you execute the following commands:

.mode column
.header on
.nullvalue NULL

The .mode command sets the output mode to column.
The .header command shows the column names in the output.
The .nullvalue sets the literal string that represents NULL.

If you execute the above query again, you’ll see the nicely formatted output:

sqlite> select *
   ...> from albums
   ...> order by AlbumId
   ...> limit 3;

Output:

AlbumId  Title
-------  -------------------------------------
1        For Those About To Rock We Salute You
2        Balls to the Wall
3        Restless and Wild

Escaping the …> in the middle of the query #

Suppose you are typing a query and want to escape the …>, you can enter a semicolon (;) and hit the Enter key.

For example:

sqlite> select *
   ...> from alien
   ...> ;
Parse error: no such table: alien
sqlite>

In this example, we found a mistake in the table name (alien) and wanted to terminate the statement earlier. We entered the semicolon (;) and hit the Enter key.

The sqlite> command prompt appears again waiting for the next command to execute.

Saving the result of a query into a file #

To save the result of a query into a file, you use the .output FILENAME command.

Once you issue the .output command, all the results of the subsequent queries will be saved to the file you specify in the FILENAME argument.

If you want to save the result of the next single query only to the file, you issue the .once FILENAME command.

To display the result of the query to the standard output again, you issue the .output command without arguments.

The following commands select the title from the albums table and write the result to the albums.txt file.

First, write the output of the query into the c:/sqlite/db/output.txt file:

.output c:/sqlite/db/output.txt

Second, execute a query that selects title from the albums table:

SELECT title FROM albums;

You can open the output.txt file to view the query result.

Finally, set the output back to the standard output:

.output

Executing SQL statements from a file #

Suppose you have a file named commands.txt in the c:\sqlite\db\ folder with the following content:

SELECT albumid, title
FROM albums
ORDER BY title
LIMIT 10;

To execute the SQL statements in the commands.txt file, you use the .read FILENAME command as follows:

.read C:\sqlite\db\commands.txt

Output:

AlbumId     Title
----------  ----------------------
156         ...And Justice For All
257         20th Century Masters -
296         A Copland Celebration,
94          A Matter of Life and D
95          A Real Dead One
96          A Real Live One
285         A Soprano Inspired
139         A TempestadeTempestade
203         A-Sides
160         Ace Of Spades

If you see the following message, you need to double-check whether the file exists:

Error: cannot open "C:\sqlite\db\commands.txt"

In this tutorial, you have learned many useful commands in the sqlite3 tool to perform various SQLite database tasks.

Was this tutorial helpful?