Docker backup and restore SQLite

Before you begin

In this tutorial, we’ll learn how to create, backup and restore a SQLite database using Docker. A Linux machine and Docker will be required to follow this tutorial.

Create a Docker SQLite Docker image

Dockerfile:

FROM alpine:3.10
RUN apk add --update sqlite
RUN mkdir /db
WORKDIR /db

ENTRYPOINT ["sqlite3"]
CMD ["test.db"]

Command to build an image using previous Dockerfile. Note: Make sure you are on the same folder or change the . below to the current Dockerfile folder ):

docker build -t some-sqlite .

Finally check the Docker SQLite image has been created:

$ docker images | grep sqlite
some-sqlite                          latest               9ac54b81805c        23 seconds ago      11MB

Create a SQLite database using Docker

Now it’s time to run the previous image to create and use a database called test.db:

docker run --rm -it -v `pwd`:/db some-sqlite test.db

Once the container starts its entrypoint sqlite3 is executed and then you can run the following commands to create a table, insert values and select them:

sqlite> create table test_table(id int, description varchar(10));
sqlite> .tables
test_table
sqlite> insert into test_table values(1, 'foo');
sqlite> insert into test_table values(2, 'bar');
sqlite> select * from test_table;
1|foo
2|bar
sqlite> .exit

You can find more SQLite commands here.

Backup a SQLite database using Docker

Command to backup test.db to a dump.sql file on host:

docker run --rm -it -v `pwd`:/db some-sqlite test.db .dump >> dump.sql

Finally, double check the dump.sql contains all SQLite queries:

$ cat dump.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test_table(id int, description varchar(10));
INSERT INTO test_table VALUES(1,'foo');
INSERT INTO test_table VALUES(2,'bar');
COMMIT;

Restore a SQLite database using Docker

Before restoring the database make sure that the destination database is empty (moving current database to .old):

mv test.db test.db.old

Command to restore test.db database:

cat dump.sql | docker run --rm -i -v `pwd`:/db some-sqlite test.db


Recommended books to expand your SQLite knowledge:

Finally, you should definitely take a look at these books to fuel your Docker knowledge: