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.Code language: Shell Session (shell)

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:

.helpCode language: CSS (css)

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.

.quitCode language: Shell Session (shell)

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.dbCode language: CSS (css)

Output:

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

Opening a SQLite database file using sqlite3

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

.open FILENAMECode language: CSS (css)

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.dbCode language: Shell Session (shell)

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.dbCode language: CSS (css)

Output:

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

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:

.databasesCode language: CSS (css)

Output:

main: c:\sqlite\db\sales.db r/wCode language: Shell Session (shell)

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;Code language: Shell Session (shell)

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

.databasesCode language: CSS (css)

Output:

main: c:\sqlite\db\sales.db r/w
chinook: c:\sqlite\db\chinook.db r/wCode language: Shell Session (shell)

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:

.tablesCode language: CSS (css)

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.dbCode language: CSS (css)

Step 2. Show all the tables:

.tablesCode language: Shell Session (shell)

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'Code language: JavaScript (javascript)

Output:

employees    genres       invoices     media_typesCode language: Shell Session (shell)

Showing table structures

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

.schema TABLECode language: CSS (css)

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 albumsCode language: CSS (css)

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]);Code language: CSS (css)

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

.schemaCode language: CSS (css)

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

.fullschemaCode language: CSS (css)

Showing indexes

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

.indexesCode language: CSS (css)

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_InvoiceLineTrackIdCode language: Shell Session (shell)

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 TABLECode language: CSS (css)

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

.indexes albumsCode language: CSS (css)

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;Code language: JavaScript (javascript)

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 NULLCode language: CSS (css)

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;Code language: JavaScript (javascript)

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>Code language: JavaScript (javascript)

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.txtCode language: JavaScript (javascript)

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:

.outputCode language: CSS (css)

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;Code language: Shell Session (shell)

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

.read C:\sqlite\db\commands.txtCode language: CSS (css)

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 SpadesCode language: Shell Session (shell)

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

Error: cannot open "C:\sqlite\db\commands.txt"Code language: JavaScript (javascript)

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

Was this tutorial helpful ?