How to run command-line SQLite query and exit?

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

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

Documentation: Command Line Shell For SQLite.

The general command syntax

This was confusing me a bit partly because man sqlite3 uses a weird notation so here goes, the syntax in more common GNU-like notation is:

sqlite3 [opt]... [<db> [sql]...]

This means that the first argument that starts without a dash - has to be the database name.

And then, every other argument is a separate SQL statement that happens on that database.

Some valid example commands:

sqlite3 
sqlite3 db.sqlite3
sqlite3 db.sqlite3 "select * from t"
sqlite3 db.sqlite3 "select col1 from t" "select col2 from t"
sqlite3 db.sqlite3 "select col1 from t; select col2 from t"
sqlite3 -line
sqlite3 -line db.sqlite3
sqlite3 -line db.sqlite3 "select * from t"
sqlite3 -line db.sqlite3 "select col1 from t" "select col2 from t"
sqlite3 db.sqlite3 ".schema"
sqlite3 db.sqlite3 ".schema" "select * from t"

If there is one or more positional sql commands, then there is no interactive shell at the end

Therefore the following exit without interactive shell:

• sqlite3 db.sqlite3 "select col1 from t": one SQL statement
• sqlite3 db.sqlite3 "select col1 from t" "select col2 from t": two SQL statements
• sqlite3 db.sqlite3 ".schema": one SQL statement
• sqlite3 db.sqlite3 ".schema" "select col1 from t": two SQL statements

but the following open an interactive shell because they have no positional SQL commands:

sqlite3
sqlite3 -cmd "select col1 from t" -cmd "select col2 from t" db.sqlite3 

So we see that what -cmd does is to allow running a SQL statement while still getting an interactive shell at the end.

If commands come from non interactive stdin (e.g. pipe), then there is also no interactive shell at the end

E.g. this exits immediately:

echo 'select 1' | sqlite3

To run a query without a DB and exit you can use the magic :memory: temporary in-memory database

E.g. this can be useful for quick function testing:

sqlite3 ':memory:' 'select 1 + 1'

And if you really want to show off you can also use that with a CTE to show off your SELECT skills with single statements:

sqlite3 ':memory:' 'WITH t (i, j) AS (VALUES (1, -1), (2, -2)) SELECT * FROM t'

-cmd is only needed when you want to run a command before stdin

E.g. a common use case for me is to .mode csv before running a large command from stdin to get CSV output via Bash EOF e.g.:

sqlite3 db.sqlite3 -cmd '.mode csv' <<EOF
SELECT * FROM (
  SELECT * FROM (
    SELECT * FROM t
  )
)
EOF

This form is useful because dot . commands like .mode don't seem to be allowed from stdin.

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.)

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;"