Create a SQLite Database While Building a Docker Image

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.

Recently, I was deploying a PHP application to a staging environment using 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 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 Doctrine Migrations or 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. To me, this approach had several, clear benefits:

  1. It would be clear where and how the database was created

  2. No extra software was required nor had to be learned

  3. The database would be available in the base image, with no records

Given that, I first created a new SQL file with the 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.

#!/usr/bin/env sh

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

Then, I added the following RUN instruction to the Dockerfile that created the relevant Docker image

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

If you’re not familiar with Docker, Alpine Linux (which the Docker image is based on) or with the Linux command-line, here’s what the instruction does:

  1. Installs the SQLite CLI to be used to create the database

  2. Removes the APK cache after installing sqlite3 so that the image is kept as small as possible

  3. Runs the database creation script to create the database

  4. Sets the database files as being read/write. The read/write permissions on the directory containing the 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: 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...

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