How to run local SQLite commands in a Docker Container

SQLite is a very simple and powerful database engine. This is a free library that implements a stand-alone, serverless, zero-configuration, transactional SQL database engine. The entire database (declarations, tables, indexes, and data) is stored in a file independent of the platform.

The database is represented by a file which often has the extension .db. For example: database.db. This file can be loaded into a project with an SQLite package or can be directly accessed with a library to run SQL queries or SQLite commands.

To directly access this file, one of the best-known libraries is the sqlite3 C library. It is easy to use, but it is mandatory to install it or have the binaries to run it. But thanks to Docker containers it is possible to run local SQLite commands without installing it.

Dump and Restore SQLite database with Docker

Let’s have a project structure with two files in a data folder:

.
..
data/database.db
data/dump.sql

where

• database.db contains the SQLite database.
• dump.sql contains the dump of the database.

Dump

To generate a dump with Docker, execute:

docker run --rm \
  --user $(id -u):$(id -g) \
  -v /$(pwd)/data:/db \
  keinos/sqlite3 \
  sh -c "sqlite3 /db/database.db .dump > /db/dump.sql"

--rm to automatically clean up the container.

--user $(id -u):$(id -g) is an option to get the UID and GID of the host to generate files with the same user as the host. Without it, there could be an error in the permissions to generate the file on the host.

-v /$(pwd)/data:/db is an option to mount the host/data folder in the container/db folder. It allows to write files on the container and access them directly on the host.

keinos/sqlite3 is a container image for sqlite3.

sh -c "sqlite3 /db/database.db .dump > /db/dump.sql" is the command to dump the database. The container will write dump.sql from the database.db and with the volume, these files are directly accessed on the host. dump.sql are human-readable files and can be verified by opening them.

Dumping the schema can be done with:

docker run --rm \
  --user $(id -u):$(id -g) \
  -v /$(pwd)/data:/db \
  keinos/sqlite3 \
  sh -c "sqlite3 /db/database.db .schema > /db/schema.sql"

Restore

To restore a database from a dump with Docker, execute:

docker run --user $(id -u):$(id -g) \
  -v /$(pwd)/data:/db \
  keinos/sqlite3 \
  sh -c "sqlite3 /db/database.db < /db/dump.sql"

sh -c "sqlite3 /db/database.db < /db/dump.sql" is the command to restore the database. The container will write database.db from the dump.sql and with the volume, these files can be directly accessed on the host.

For the explanation of the other options, see the Dump chapter above.

Conclusion

SQLite provides a light portable database that can be associated directly with services source files, and sometimes it might be useful to dump or restore a database on a system that did not have any SQLite libraries.

Docker solves it by providing a simple way to execute SQLite commands. It can be useful to simplify deployment by preventing the installation of additional libraries.

Thanks to Nelson and Samir for helping me find this solution.