Command line SQLite recipes

As a software developer or tester you get in touch with SQLite? Here are a few recipes that make your work easier.

Backup SQLite

Create a copy of the entire database

# change directory (where Database is located)
$ cd /path/to/folder

# start full dump of entire database
$ sqlite3 myDatabase.db .dump > myFullBackup.sql

Create copy of a specific table

# change directory (where Database is located)
$ cd /path/to/folder

# dump only specific table
$ sqlite3 myDatabase.db ".dump myTable" > mySpecificBackup.sql

Create copy of specific table as CSV or HTML

# change directory (where Database is located)
$ cd /path/to/folder

# cvs without header
$ sqlite3 -csv myDatabase.db "SELECT * FROM myTable;" > mySpecificBackup.csv

# csv with header
$ sqlite3 -header -csv myDatabase.db "SELECT * FROM myTable;" > mySpecificBackup.csv

# html without header
$ sqlite3 -html myDatabase.db "SELECT * FROM myTable;" > mySpecificBackup.html

# html with header
$ sqlite3 -header -html myDatabase.db "SELECT * FROM myTable;" > mySpecificBackup.html

Create SQLite database

# change directory (where Database should located)
$ cd /path/to/folder

# restore from dump
$ sqlite3 myNewDatabase.db < myCreateFile.sql

Select query from Terminal

# change directory (where Database is located)
$ cd /path/to/folder

# simple output
$ sqlite3 myDatabase.db "SELECT * FROM myTable;"

# with header
$ sqlite3 -header myDatabase.db "SELECT * FROM myTable;"

# with header and column
$ sqlite3 -header -column myDatabase.db "SELECT * FROM myTable;"

# select as command line
$ sqlite3 -line myDatabase.db "SELECT * FROM myTable;"

#select as html
$ sqlite3 -html myDatabase.db "SELECT * FROM myTable;"

Merge sqlite tables with same schema

Imagine there are two databases (db_1.db and db_2.db) with tables (myTable) in same schema. Now we merge the content of table “myTable” from database “db_2.db” into “db_1.db”.

# change to directory where databases are located
$ cd /path/to/folder

# start sqlite commands
$ sqlite db_1.db

# run all needed commands
sqlite> ATTACH 'db_2.db' AS toMerge;
sqlite> BEGIN;
sqlite> INSERT INTO myTable SELECT * FROM toMerge.myTable;
sqlite> COMMIT;
sqlite> .quit