How to Run and use SQLite Database with Docker Containers

Posted January 8, 2024
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: How Embed 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. 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"]

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 .

How to Run and use SQLite database with Docker containers

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.

How to Run and use SQLite database with Docker containers

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

How to Run and use SQLite database with Docker containers

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;

How to Run and use SQLite database with Docker containers

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:
    external: true

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.

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

How to Run and use SQLite database with Docker containers

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:

How to Run and use SQLite database with Docker containers

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:

How to Run and use SQLite database with Docker containers

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!

How to Run and use SQLite Database with Docker Containers

Written By:

Joseph Chege