Run command-line SQLite query and exit

Just include the command in quotes after the database file argument.

For example, the following creates a table called abc:

sqlite3 test.db 'create table abc (col0 int)'

If you are stuck in a situation where you absolutely "have to" use the -cmd flag when you are running SQLite 3 from the command line, you can use successive blank command to exit.

For example:

sqlite3 test.db "select * from urls;" "" > test.txt

In this example, the "" will cause the SQLite 3 process to exit. (At least it does for me on OS X.)

You can use the .exit command (1), to exit gracefully:

sqlite3 test.db "select * from abc;" ".exit"

Documentation: Command Line Shell For SQLite.

Also it might be useful to have multiline queries:

sqlite3 ./database.db <<EOF
SELECT *
FROM something
LIMIT 5
EOF

As it is mentioned in the question, we can also make use of the '-cmd' option and... apply some shell 'magic' at the end (to imitate user manual 'exit'). Let's evaluate a sql expression:

#!/bin/sh
sqlite3 -cmd "select 5.0/2;" < `echo ".exit"`


2.5

This approach is effective, when we have to submit '.xxx' sqlite meta-commands (i.e. .show or .stat) as well:

sqlite3 -cmd ".stat" < `echo ".exit"`


Memory Used:                         0 (max 56) bytes
Number of Outstanding Allocations:   0 (max 2)
Number of Pcache Overflow Bytes:     0 (max 0) bytes
Largest Allocation:                  40 bytes
Largest Pcache Allocation:           0 bytes
Bytes received by read():            10992
Bytes sent to write():               253
Read() system calls:                 19
Write() system calls:                6
Bytes read from storage:             0
Bytes written to storage:            0
Cancelled write bytes:               0

Note: Tested in dash & bash. Should also play in zsh, fish, etc.

There are queries and there are dot-commands.

What can we pass to sqlite3? Let us check.

% sqlite3 --help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]

We can also use dot-commands in place of [SQL]. However, there might be some exceptions which i am not aware about.

To list all dot-commands,run:

sqlite3 test.sqlite .help

Here, test.sqlite is the FILENAME.

To view the schema of the database, run:

sqlite3 test.sqlite .schema

To list all tables of the database, run:

sqlite3 test.sqlite .tables

If you must use -cmd then the command will look like:

sqlite3 -cmd .tables test.sqlite .quit

Now you can run query on the databases using:

sqlite3 test.sqlite "select * from DATABASE_NAME"

If you must use -cmd then the command will look like:

sqlite3 -cmd "select * from object_store" test.sqlite .quit

You might have already noticed, when using -cmd we are using more than one commands. Yes, we can do something like:

sqlite3 test.sqlite ".print The Tables Are:\n" ".tables" ".print \nThe Schema is:\n" ".schema" ".print \nList Of All Customers:\n" "select * from customers"

If you must use -cmd then the command will look like:

sqlite3 -cmd ".print The Tables Are:\n" -cmd ".tables" -cmd ".print \nThe Schema is:\n" -cmd ".schema" -cmd ".print \nList Of All Customers:\n" -cmd "select * from object_store" test.sqlite .quit

NOTE: From previous examples, we can see that, we only need to use .quit or .exit, if you use -cmd. Otherwise, it is not needed.

This command below inserts a row to person table, then reads person table in db.sqlite3 database then exits at once. *".exit" command is not needed to exit because after all queries complete, sqlite automatically exits:

sqlite3 db.sqlite3 "INSERT INTO person VALUES ('John');" "SELECT * FROM person;"