Non-interactive SQLite3 usage from bash script

Question

I see plenty of examples showing how to use the sqlite3 interactive shell, e.g.:

$ sqlite3
$ sqlite3> SELECT * from x;

but I am looking for a way to create a table in a SQLite3 database with a bash script, aka, non-interactively.

For example, the following doesn't seem to work, it remains interactive:

#!/bin/bash
sqlite3 test.db  "create table n (id INTEGER PRIMARY KEY,f TEXT,l TEXT);"
sqlite3 test.db  "insert into n (f,l) values ('john','smith');"
sqlite3 test.db  "select * from n";

Answer 1

While the above should work, I think it is better not to have to invoke sqlite3 multiple times, therefore I think the following is preferable:

#!/bin/sh
sqlite3 test.db <<EOF
create table n (id INTEGER PRIMARY KEY,f TEXT,l TEXT);
insert into n (f,l) values ('john','smith');
select * from n;
EOF

Note that unless you really need to use bash, you should prefer "/bin/sh" as your shebang, for portability reasons.

Answer 2

Looks like it's as simple as

#!/bin/bash
sqlite3 test.db  "create table n (id INTEGER PRIMARY KEY,f TEXT,l TEXT);"
sqlite3 test.db  "insert into n (f,l) values ('john','smith');"
sqlite3 test.db  "select * from n;"

from https://mailliststock.wordpress.com/2007/03/01/sqlite-examples-with-bash-perl-and-python/