May I run a script on SQLite command line?

Question

May I run a script with SQL statements and . SQLite commands?

Answer 1

Mixing SQL statements with SQLite .commands can be a bit tricky:

$ sqlite3 test.db 'create table X(x integer); .dump'
Error: near ".": syntax error

The easiest and most universal way to deal with this is probably to provide all commands to the standard input of the SQLite3 command line shell, separated by newlines as you would type them:

$ sqlite3 test.db <<EOF
> CREATE TABLE TEST(X INTEGER);
> .dump
> EOF
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE TEST(X INTEGER);
COMMIT;

Since here documents are not available in all shells, you could use an intermediate file to bypass this limitation:

$ cat test.sql
CREATE TABLE TEST(X INTEGER);
.dump
$ sqlite3 test.db <test.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE TEST(X INTEGER);
COMMIT;

Answer 2

For me works this query:

sqlite3 test.db "select * from sometable"

Answer 3

Sure you can. Use .read directive in the command line and you'll be there.

Create your script:

sample.sql

DROP TABLE IF EXISTS test;
CREATE TABLE test (id PRIMARY KEY,
                   rand1 REAL DEFAULT (RANDOM()),
                   rand2 REAL DEFAULT (RANDOM()));
INSERT INTO test (id) VALUES(1);
INSERT INTO test (id) VALUES(2);
.dump

Then you can run your script:

$ sqlite3 test.db '.read sample.sql'
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (id PRIMARY KEY, rand1 REAL DEFAULT (RANDOM()), rand2 REAL DEFAULT (RANDOM()));
INSERT INTO test VALUES(1,-479816953303475072.01,7897905953557517311.9);
INSERT INTO test VALUES(2,3449088292611145728.0,-595585280596709760.01);
COMMIT;