@leafac/sqlite-migrationdeprecated

A lightweight migration system for @leafac/sqlite

Usage no npm install needed!

<script type="module">
  import leafacSqliteMigration from 'https://cdn.skypack.dev/@leafac/sqlite-migration';
</script>

README

@leafac/sqlite-migration

A lightweight migration system for @leafac/sqlite

Source Package Continuous Integration

Installation

$ npm install @leafac/sqlite @leafac/sqlite-migration

Features, Usage, and Example

@leafac/sqlite-migration provides a databaseMigrate() function which you must call with a @leafac/sqlite database and an array of migrations. For example:

import { Database, sql } from "@leafac/sqlite";
import databaseMigrate from "@leafac/sqlite-migration";

const database = new Database(":memory:");

// First time running migration.
console.log(
  databaseMigrate(database, [
    sql`CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT NOT NULL);`,
  ])
); // => 1

// Run it again, and nothing changes. For best results, run databaseMigrate() at your application startup.
console.log(
  databaseMigrate(database, [
    sql`CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT NOT NULL);`,
  ])
); // => 0

// At some point in the future, a new migration comes in.
console.log(
  databaseMigrate(database, [
    sql`CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT NOT NULL);`,
    sql`CREATE TABLE "threads" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "author" INTEGER NOT NULL REFERENCES "user", "title" TEXT NOT NULL);`,
  ])
); // => 1

The databaseMigrate() function returns the number of migrations that were executed.

No Down Migrations

Most migration systems (if not all), provide a way to undo migrations; something called down migrations. @leafac/sqlite-migration doesn’t provide a down migration mechanism.

I believe that down migrations are more trouble to maintain (they can be a lot of work!) than they’re worth, particularly in small applications. Why? Because down migrations have two main selling points:

  1. You may go back and forward with the database schema in development (think of alternating back and forth while working on different feature branches that change the database schema).
  2. You may rollback a deployment that goes wrong in production.

But I don’t think these selling points hold up:

  1. You may recreate the database from scratch whenever you need in development.
  2. You almost never want to run a down migration in production because that would make you lose data.

In case something goes wrong, @leafac/sqlite-migration requires you to write a new migration that undoes the troublesome previous migration. The only way through is forward!

How It Works

@leafac/sqlite-migration creates a table in your database with the following schema:

CREATE TABLE IF NOT EXISTS leafacMigrations (id INTEGER PRIMARY KEY AUTOINCREMENT, source TEXT NOT NULL);

When migrating, it checks the migrations that you passed against the ones stored in the leafacMigrations. Any inconsistencies cause errors, and any new migration is run and stored for next time.

You must never manipulate leafacMigrations by hand; most likely that’ll lead to a corrupted migration state.

Related Projects

Prior Art

  • https://npm.im/sqlite, and https://npm.im/better-sqlite3-helper: These packages include lightweight migration systems. @leafac/sqlite-migration is even more lightweight: It doesn’t support down migrations and it requires the migrations to be passed as an array, as opposed to, for example, being stored in SQL files. (But you can come up with this array in any way you want, including, for example, reading from a bunch of SQL files.) Also, these other packages don’t support @leafac/sqlite and its sql tagged template literal (naturally).
  • https://github.com/trevyn/turbosql: After having published @leafac/sqlite-migration the author of Turbosql reached out to say that they independently arrived at a similar design, but in the Rust ecosystem instead of Node.js. It’s great to have company!