A Simple Shell Script-based SQLite Migration Tool

Background

I recently migrated an internal tool from Cloudflare Worker to self-hosting it on fly.io. The main reason driving this move is that we had ran into some weird issues with using fetch calls on the server-side. (Like custom domain would somehow cause it to not work so we had to redirect users to .pages.dev domain first, and POST for some reason does not work in our Rust-based wasm code.)

We were using the D1 database which is a SQLite wrapped in some easy to use APIs (both in JS and over HTTP, however, no DB GUI tools have support for these APIs natively yet so management is a bit annoying) so we need to move them. I decided to also just use a sqlite DB but store it on fly.io’s LiteFS. Importing data is easy, we just need to export the data as SQL, put it in a sqlite3 db and put that file into LiteFS. However, now we do not have a CLI to do migrations anymore. Therefore, I wrote a simple script to facilitate this. The code can be found at the bottom of the post.

Usage

To use the script, call it by: Usage: migration.sh <dbpath> <list|up>.

The list command list out all migrations and if they’re applied, and up runs the migrations. We just use the d1_migration table that Cloudflare D1 created, if you do not have it in your DB, you can create one via:

CREATE TABLE d1_migrations(
	id         INTEGER PRIMARY KEY AUTOINCREMENT,
	name       TEXT UNIQUE,
	applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

Use it with LiteFS

LiteFS provides a mount command that took care of setting itself up and also running your application. You can take advantage of that mechanism to run this migration script, to do so, add this to your exec section:

exec:
  - cmd: "/app/scripts/migration.sh /litefs/<dbname> up"
    if-candidate: true
  - cmd: "<your app command>"

The if-candidate option will ensure it runs on the primary node.

Source Code

#!/usr/bin/env bash
# SQLite3 Migration Tool
#
# Copyright 2024 Zero Cho
#
# MIT License
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE.
set -euo pipefail
 
function check_migration_applied {
  local migration="$1"
 
  if sqlite3 $DBPATH "SELECT 1 FROM d1_migrations WHERE name = '$migration'" | grep -q 1; then
    echo 1
  else
    echo 0
  fi
}
 
function print_help_and_exit() {
  echo "Usage: $0 <dbpath> <list|up>"
  exit 1
}
 
cd $(dirname $0)/..
MIGRATIONS=$(
  cd migrations
  ls -1 *.sql | sort -V
)
 
DBPATH="$1"
CMD="$2"
 
if [[ -z "$DBPATH" ]]; then
  print_help_and_exit
fi
 
if [[ ! -f "$DBPATH" ]]; then
  echo "Database file not found: $DBPATH"
  exit 1
fi
 
case $CMD in
list)
  echo "List of migrations:"
  echo
 
  for f in $MIGRATIONS; do
    applied=$(check_migration_applied $f)
    id=$(basename $f | cut -d '_' -f 1)
    echo -n "$id "
    if [[ "$applied" -eq 0 ]]; then
      echo -n "❌ "
    else
      echo -n "✅ "
    fi
    echo $f
  done
  ;;
up)
  for f in $MIGRATIONS; do
    applied=$(check_migration_applied $f)
    if [[ "$applied" -eq 0 ]]; then
      echo -n "Applying $f ..."
      set +e
      if output=$(sqlite3 $DBPATH <migrations/$f 2>&1); then
        if output=$(sqlite3 $DBPATH "INSERT INTO d1_migrations (name) VALUES ('$f')" 2>&1); then
          set -e
          echo " ✅"
        else
          set -e
          echo " ❌"
          echo
          echo "FATAL ERROR: Failed to update migration status. The migration have been APPLIED. Please manually inspect and fix the database."
          echo
          echo $output
          exit 1
        fi
      else
        set -e
        echo " ❌"
        echo
        echo "Migration failed:"
        echo
        echo $output
        exit 1
      fi
    fi
  done
  echo "All migrations applied successfully."
  ;;
*)
  print_help_and_exit
  ;;
esac