33

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 (compare last 2 characters with accepted answer), 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";
Improve this question
4
  • For example create table tbl1(one varchar(10), two smallint);
    – zon7
    Commented Feb 15, 2017 at 9:51
  • 2
    There is nothing to wake up, SQLite3 does not use a server/daemon.
    – Sir Athos
    Commented Feb 15, 2017 at 10:02
  • thanks for that info. good to know. on a separate note, my answer actually doesn't work - it's still interactive.
    – Alexander Mills
    Commented Feb 15, 2017 at 10:13
  • 2
    Try moving your final semicolon inside the double quote, instead of outside.
    – Matt Zabojnik
    Commented Apr 22, 2023 at 2:16

4 Answers 4

Reset to default
33

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

(last 2 characters swapped) from https://mailliststock.wordpress.com/2007/03/01/sqlite-examples-with-bash-perl-and-python/

Improve this answer
3
  • 2
    ok, I confirmed that this works - this is non-interactive, as long as test.db exists
    – Alexander Mills
    Commented Feb 15, 2017 at 10:29
  • 1
    In the last command, you might have intended the semi-colon needs to be inside the quotes, otherwise it's a command to Bash (which, in this case, does nothing).
    – Paddy Landau
    Commented Oct 17, 2020 at 15:00
  • 1
    thanks Paddy, I fixed that semicolon placement
    – Alexander Mills
    Commented Feb 16, 2022 at 3:29
30

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.

Improve this answer
11
  • 3
    I do not think you should ever prefer /bin/sh as shebang: usually sh is symlinked to bash so that any code that is valid in bash but not in sh will not be noticed until finally someone runs your code on some eon-old platform (Solaris 9, for example) which does really have a difference between sh and bash and it will throw errors.
    – Cheiron
    Commented Apr 20, 2018 at 9:30
  • 1
    @Cheiron: Your point about unnoticed bashisms in a script with a /bin/sh shebang is well-taken, but I still disagree. I don't know offhand how common it is in the Linux word to symlink /bin/sh to /bin/bash, but it isn't universal, e.g, in Lubuntu (the only Linux system handy to me at the moment), /bin/sh is symlinked to /bin/dash, not /bin/bash. More importantly, all the world in not Linux. Apart from "eon-old" platforms, modern, very much in use systems, like the one I use all the time, FreeBSD, simply do not have /bin/bash. I stand by my recommendation.
    – varro
    Commented Apr 20, 2018 at 18:32
  • 1
    is the FreeBSD argument not an argument for /bin/bash? Dash is not sh, bash is not sh and zsh is not sh, so I think one should pick the shebang one is actually working with. If I create a bash script and put /bin/sh above it, I can not actually test wether or not it runs on sh since /bin/sh is symlinked to /bin/bash on my system, so I should put /bin/bash as shebang, right?
    – Cheiron
    Commented Apr 20, 2018 at 18:50
  • 3
    I dont have 30 years of experience, but I don't dare assume that my bash just runs on sh without being able to check. I also do not think that learning your craft is somehow equal to making no mistakes, especially not when tired. I do like portability, but I need at least one test system for every supported platform or I cannot confirm wether or not the code is actually supported everywhere. So as long as I dont have sh, i prefer using /bin/bash as to not give false promises: I can only test for bash so I put /bin/bash.
    – Cheiron
    Commented Apr 21, 2018 at 20:40
  • 3
    @MikeS So why can I write /bin/sh as shebang and then use [[? [[ does not exist in sh. So effectively I am lying when sh is the shebang, since it will only run if you symlinked the sh to a bash-like shell. That is my point here: sh is obsolete and confusing at this point so stick to the shell you know it will work on.
    – Cheiron
    Commented May 1, 2019 at 8:24
12

You can disable interactive mode using the -batch option:

sqlite3 -batch test.db "create table n (id INTEGER PRIMARY KEY,f TEXT,l TEXT);"
Improve this answer
3
  • 2
    Does not work using cmd internal commands, like as sqlite3 -separator ',' -cmd ".import file.csv' tmp" -batch tmp.db.
    – e-info128
    Commented Jul 5, 2021 at 15:10
  • 1
    @e-info128 not sure if you got the answer to this already but cat << EOF | sqlite3 tmp.db <newline> .import file.csv tmp EOF works for me.
    – shriek
    Commented Sep 19, 2021 at 17:11
  • @e-info128 fix your quotes
    – Johan Boulé
    Commented Aug 18 at 0:45
4

This works too

sqlite3 "${DB_FILE_NAME}" < data_model.sql
sqlite3 "${DB_FILE_NAME}" < dml.sql
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.