Create a SQLite Database While Building a Docker Image

DevOps 
Create a SQLite Database While Building a Docker Image

Do you need to have a SQLite database available in a Docker image? I did, and here’s the solution I came up with.


:idseparator: - :idprefix: :experimental: :source-highlighter: rouge :rouge-style: pastie :imagesdir: /images

Recently, I was deploying a link:/tags/php[PHP] application to a staging environment using https://deploywithdockercompose.com/[Docker Compose], and noticed that, when deployed, the SQLite database that it depended upon wasn’t available. At first, this seemed strange as, in my local deployment it was there and the application was working fine.

On closer inspection of the link:/tags/git[Git] log, I saw that the directory that the database file was in was excluded from version control, as the directory was listed in .gitignore.

== What to do?

My first thought was to make an exception and add the database file to Git. However, my follow-up thought was that that was a mistake. To put this into greater context, the application is, effectively, a test harness for a PHP library that I’ve been developing for a client. Given that, when initially deployed, the database’s tables should always be empty.

So, if the database was tracked by Git, the likelihood of random records being added over time would be introduced. Therefore, having random database records may lead to myself or someone else, in the future, to think that the application doesn’t work as expected.

So, how could the SQLite database be available in the Docker image, yet not tracked by Git? There are only two tables in the database, and they’re not all that sophisticated. So that was one less thing to think about.

I thought about using a migrations tool, such as https://www.doctrine-project.org/projects/migrations.html[Doctrine Migrations] or https://laravel.com/docs/9.x/migrations[Laravel Migrations], in the image’s Dockerfile. However, while they’re excellent tools, they seemed like overkill.

So, again, how could the database be provisioned when the applicable Docker image was built? Good question…

== My Solution

After a bit more thought, I decided to go with the most elementary solution I could come up with: create it using a small Bash script in the image’s Dockerfile. For what it’s worth, I took a similar approach recently, link:/deploy-go-sqlite-app-flydotapp/[when deploying a Go app to Fly.io].

To me, this approach had several, clear benefits:

. It would be clear where and how the database was created . No extra software was required nor had to be learned . The database would be available in the base image, with no records

Given that, I first created a new SQL file with the https://www.sqlite.org/lang_createtable.html[CREATE TABLE] statements for the two tables. Then, I created a small Bash script, which you can see below, which uses SQLite’s CLI tool to create the database from the SQL file.

[source,bash]

#!/usr/bin/env sh

sqlite3 -batch “$PWD/data/database.sqlite” <"$PWD/docker/php/scripts/initdb.sql"

Then, I added the following https://docs.docker.com/engine/reference/builder/#run[RUN instruction] to the https://docs.docker.com/engine/reference/builder/[Dockerfile] that created the relevant Docker image

[source,dockerfile]

RUN apk –update-cache add sqlite
&& rm -rf /var/cache/apk/*
&& ./docker/php/scripts/create-database.sh
&& chmod a+rw ./data/database.sqlite

If you’re not familiar with Docker, https://www.alpinelinux.org/[Alpine Linux] (which the Docker image is based on) or with the Linux command-line, here’s what the instruction does:

. Installs https://www.sqlite.org/cli.html[the SQLite CLI] to be used to create the database . Removes the APK cache after installing sqlite3 so that the image is kept as small as possible . Runs the database creation script to create the database . Sets the database files as being read/write. The read/write permissions on the directory containing the database, link:/sqlite-attempt-to-write-to-readonly-database/[required when working with PDO’s SQLite driver], were set in an earlier step in the Dockerfile.

After the changes were made and tested, they were committed to Docker. Now, I have a nice and uncomplicated solution for ensuring that the SQLite database is available, without (directly) tracking the database under version control.

What do you think of the approach? How would you do it?

New to Docker Compose and want to get a head-start? Check out my free book: https://deploywithdockercompose.com/[Deploy With Docker Compose].

Do you need to get your head around Docker Compose quickly?

What about needing to dockerize existing applications to make them easier to deploy, reducing the time required for develwpers to get started on projects, or learning how to debug an existing Docker Compose-based app? Then this free book is for you!

You might also be interested in these tutorials too...

How to Deploy a PHP App to Production With Docker Compose
Sun, Nov 12, 2023

How to Deploy a PHP App to Production With Docker Compose

Want to know how to deploy a PHP app to production (or any other remote environment)? In this tutorial, I’ll show you all the steps involved, and provide background information along the way, so you can build on what you’ll learn.

How to Test PHP Apps Running in Docker Containers
Sat, Nov 11, 2023

How to Test PHP Apps Running in Docker Containers

Docker and Docker Compose simplify setting up local development environments for developing PHP apps and for deploying them, whether locally or remotely. But, when your PHP app is running inside a Docker container, how do you run your tests — especially test suites that depend on resources such as database or caching servers? In this tutorial, I’ll show you how to do so.


Want more tutorials like this?

If so, enter your email address in the field below and click subscribe.

You can unsubscribe at any time by clicking the link in the footer of the emails you'll receive. Here's my privacy policy, if you'd like to know more. I use Mailchimp to send emails. You can learn more about their privacy practices here.

Join the discussion

comments powered by Disqus