#!/usr/bin/env sh
sqlite3 -batch "$PWD/data/database.sqlite" <"$PWD/docker/php/scripts/initdb.sql"
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.
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…
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:
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 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/create-database.sh \
&& chmod a+rw ./data/database.sqliteIf 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:
Installs 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, 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.
Join the discussion
comments powered by Disqus