TIL: SQLite queries as Bash one-liners
I just learned a new way of running SQLite queries from my terminal without opening the sqlite3 shell.
Here's some quick setup for a test.db database file with a person table:
$ sqlite3 test.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> create table person(id int, name text);
sqlite> insert into person values (1, "goku");
sqlite> select * from person;
id name
-- ----
1 goku
1. Echo (new to me)
This method works well for short queries that you don't mind writing every time. You echo the query and pipe that output into the sqlite3 shell:
$ echo "select name from person" | sqlite3 test.db
name
-------
goku
To run longer queries, or multiple queries at once, write a multiline echo command:
$ echo '
insert into person values (2, "yamcha");
select name from person order by name desc;
' | sqlite3 test.db
name
-------
yamcha
goku
Note: You have to use single quotes
'for the multiline string. Double quotes"won't work.
2. Reading from a file (what I already knew)
The second method is to write your queries in a .sql file and then execute the script against the database:
-- myfile.sql
insert into person values (3, "krillin");
select name from person order by name desc;
$ sqlite3 test.db < myfile.sql
name
-------
yamcha
krillin
goku
I've really liked this one for database setup (initial seeding and integration tests) in actual applications. Collaborators who don't know the project's ORM or backend language can still contribute and update the data if they know SQL.