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.
#!/usr/bin/env sh
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
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].
Recently, I deployed a Go-powered app backed by an SQLite database on Fly.io for the first time. Here’s the process that I went through, along with some of the issues that I encountered along the way.
If you need to find out which image a Docker Compose service is using, you need the docker compose images command. In this short tutorial, I’ll show you how it works.
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.
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.
Please consider buying me a coffee. It really helps me to keep producing new tutorials.
Join the discussion
comments powered by Disqus