How to Run and use SQLite Database with Docker Containers

Just like any database, SQLite runs on Docker as a container. This way, you have Docker to manage the SQLite file system within a single container.

In this guide, you will learn how to use Docker to run an SQLite database in a container. You’ll explore these options:

• Using Dockerfile instructions for building an SQLite Docker image.
• Creating a docker-compose.yml to run SQLite Docker container with Docker Compose.
• Running a Dockerized SQLite database with a flask/Node.js backend API with Docker.

Related: Guide to TypeORM SQLite with Nest.js and Typescript

Step One: Embeding SQLite Database to Docker Container with Dockerfile

To run SQLite as a container, let’s use a Dockerfile. It sets all the instructions that best fit your SQLite container. In your working directory create a file, name is dockerfile. In this example, I will use Linux to run SQLite (SQLite doesn’t have its own Docker image). You can do the same if you prefer using an Ubuntu-based image:

FROM alpine:latest
# Install SQLite
RUN apk --no-cache add sqlite
# Create a directory to store the database
WORKDIR /db
# Copy your SQLite database file into the container
COPY initial-db.sqlite /db/
# Expose the port if needed
# EXPOSE 1433
# Command to run when the container starts
CMD ["sqlite3", "/data/initial-db.sqlite"]

You need to know, Linux and Ubuntu Docker images will have SQLite ready, just like on the actual OS. This way, you only need to use the alpine Linux or Ubuntu image an go ahead to ensure the SQLite working directory is ready and that the SQLite itself is running.

In this example, the image requires you to have initial-db.sqlite file in your working directory. This file will be empty and it acts as an SQLite database file in the container.

From here, run the Docker build command to compile an SQLite Docker image.

docker build -t sqlite_db .

Step Two: Running an SQLite Docker Container

If your image is ready, you only the following command to run a container:

docker run -it -v sqlite_data:/data --name sqlite_container sqlite_db

You should have a running SQLite database inside a Docker container. In this case, the -v sqlite_data:/data flag will create a volume to persist your database files.

This command should also open an SQLite shell for your database inside the running Docker container:

Step Three: Interacting with SQLite Database and Running Operations

The above shell will allow you to create Databases and add data. It’s just like the SQL syntax that you would perform in other SQL Databases.

For example:

• Create a table:

CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);

Add one record:

INSERT INTO test (name, age) VALUES ('Joseph Kim', 43);

Add another record:

INSERT INTO test (name, age) VALUES ('Andrew Davila', 30);

• Use SQLite and Query the added data:

SELECT * FROM test;

Step Four: Persisting Data Outside the Container

You have database files persisted on -v sqlite_data:/data. However, this is happening inside the container and you can’t access the SQLite Docker files.

Data within a Docker container is always ephemeral. On the other side, SQLite is file-based and volume sqlite_data:/data is right for the job.

To work on this step, we will use Docker Compose as follows:

Step Five: Running SQLite Container with Docker Compose

Docker Compose allows you to describe all the services, networks, and volumes required for a complete application stack. You do all these in a single file docker-compose.yml.

This step will be unique. We will try to

• Create a Dockerized SQLite database,
• Populate it with sample data,
• Be able to access database files outside the Container
• Create a Dockerized SQLite browser UI to have a visual of Docker SQLite files.

In your working directory, create a database folder and add initial-db.sqlite and init.sql files.

init.sql will contain the SQL code you need to pullulate your database. This way you will have the initial SQL to set up your database. Unlike the previous step, you had to run the SQL command on the SQLite Docker shell. Update the init.sql file as follows:

CREATE TABLE example_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

INSERT INTO example_table (name) VALUES ('Test UserName');
INSERT INTO example_table (name) VALUES ('Mark Silva');
INSERT INTO example_table (name) VALUES ('Hector Kamara');
INSERT INTO example_table (name) VALUES ('Greg Anthony');

Create a docker-compose.yml file as follows:

version: '3'

services:
  sqlite-container:
    image: alpine:latest
    volumes:
      - ./database:/data
    command: sh -c "apk --no-cache add sqlite && cp /data/initial-db.sqlite /data/db.sqlite && sqlite3 /data/db.sqlite < /data/init.sql && tail -f /dev/null"
    container_name: sqlite-container

  sqlitebrowser:
    image: linuxserver/sqlitebrowser
    ports:
      - "3000:3000"
    depends_on:
      - sqlite-container
    environment:
      - PUID=1000
      - PGID=1000
      - TZ=UTC
      - ENABLE_UWSGI=true
    volumes:
      - sqlite_data:/data/db
    container_name: sqlitebrowser

volumes:
  sqlite_data:

In this file, you:

• Define a service sqlite-container to run the alpine:latest image as a base image.
• Mounts the local ./database directory into the container at /data ON Docker.
• The command will handle all you need to run SQLite with the initial data and create SQLite files on your home directory.
• To access your database, the service SQLite browser will set up a UI within Docker.

In deeper details:

• image: base image for the container, which is Alpine Linux in this case.
• volumes: Mounts the local directory ./database into the container at /data. This allows the container to access files in ./database.
• command: runs when the container starts. It first installs SQLite using apk, then copies an initial SQLite database file (initial-db.sqlite) from /data to /data/db.sqlite. After that, it runs sqlite3 to execute the SQL commands in init.sql against the db.sqlite database. Finally, it tails /dev/null to keep the container running in the foreground.

The main reason I used Alpine Linux here, is because I can Install SQLite directly to the container while using using Linux as the base Image. Initially, I tried using SQLite directly to run the container, but it failed telling me pull access denied for sqlite, repository does not exist or may require ‘docker login’: denied: requested access to the resource is denied. Here is what I has used:

version: '3'
services:
  sqlite-container:
    container_name: sqlite-container  # Container name
    image: sqlite:latest  # Docker image for SQLite
    volumes:
      - ./database:/data  # Mounting volume for SQLite data
    restart: always  # Restart policy

volumes:
  sqlite_data:  # Volume definition for SQLite data

It seems SQLite doesn’t have its own Docker image. Based on that, using Linux or Ubuntu to run SQLite on Docker turned out to be the better option.

Step Six: Running the SQLite Docker Compose File

To run this Docker Compose ensure you are in the docker-compose.yml file path. Then, execute the following command:

docker-compose up -d --build

This command should start an SQLite database. Your database folder should now have a db.sqlite file. If you are on VS CODE, you can download the SQLite extension and access it as follows:

The SQLite browser interface should be accessible at http://localhost:3000. Use Open database and open the db.sqlite file. with your SQLite browser container as follows:

Conclusion

You’ve explored SQLite with Docker using Dockerfile instructions for building an SQLite Docker image and docker-compose.yml to run SQLite Docker container with Docker Compose. I hope this post was helpful!