Sqlite.Migrate

Apply or preview migrations to a SQLite database.

type alias Permissions = { secureContext : SecureContext }

The permissions needed to run migrations.

  • secureContext is used to hash migrations and create hash chains.
type alias Migration = { name : String, statements : Array String }

A single migration represented as a name and set of SQL statements to be applied to the database.

All strings in statements must contain a single SQL statement. Additional statements in the same string are silently ignored, will not be executed when the migration happens, and do not throw errors. This is the default behavior of the Sqlite.execute function which this package uses to apply the given migrations to the database. However, these additional statements will be used to calculate the hash of this migration. As a result, once applied to a database, these no-op statements will need to be kept around or else corrupt the hash chain.

While this is not necessarily going to corrupt your database, it may make it difficult to understand the state of a database by looking at its migrations or may cause your runtime code to expect a specific database schema that isn't accurate. You've been warned!

For example, if you want to create two tables in a single migration, it might look something like this:

{ name = "Add tables"
, statements =
    [ "CREATE TABLE IF NOT EXISTS menu (id INTEGER)"
    , "CREATE TABLE IF NOT EXISTS dishes (id INTEGER)"
    ]
}

Do not do this:

{ name = "Add tables"
, statements =
    [ "CREATE TABLE IF NOT EXISTS menu (id INTEGER); CREATE TABLE IF NOT EXISTS dishes (id INTEGER)"
    ]
}

Run migrations

migrate :
Database
-> Permissions
-> Array Migration
-> Task Error Database

Run a set of given migrations (Array Migration) in order on the given database. This will:

  1. Open a savepoint, making this function safe to call from inside an existing SQLite transaction.
  2. Create a new __gren_sqlite_migrate table if it does not already exist.
  3. Retrieve any existing migrations from the __gren_sqlite_migrate table.
  4. Check the given migrations to ensure there aren't any issues that will cause problems or corruption.
  5. Check the given migration hash chain against the databases hash chain, ensuring everything checks out and there are no deviations. This also builds a list of migrations that still need to be applied.
  6. Applies the still pending migrations and updates the __gren_sqlite_migrate table with a record of those applied.

While the SQL savepoint helps ensure the SQL runs without issue (and properly rolls back all changes if necessary), it's highly recommended that you run this function completely separate from other SQL.

Preview migrations

type alias Preview =
{ applied : Array { name : String, migrationHash : String, chainHash : String, appliedAt : Int }
, pending : Array { name : String, migrationHash : String, chainHash : String }
}

The result of running the preview function.

  • applied are the migrations the database already records as applied, in order.
  • pending are the migrations that would be applied if migrate were to be called with the same arguments, in order.
preview :
Database
-> Permissions
-> Array Migration
-> Task Error Preview

Run the migration pipeline up to (but not including) applying any pending migrations. Instead of running those migrations, produce a Preview record.

Errors

type Error
= SqliteError Error
| LocalMigrationsEmptyError
| MoreRemoteThanLocalMigrationsError
| EmptyMigrationNameError
| ConflictingMigrationNameError String
| EmptyMigrationStatementsError String
| MigrationStatementMentionsMigrationTableError String
| MigrationHashesDoNotMatchError String
| MigrationChainDivergedError String
| MigrationHashDecodingFailedError

Errors that can happen when running or previewing a migration.

  • SqliteError is an error with the Gren SQLite library. This is most likely to happen due to problems with the given SQL statements in a migration.
  • LocalMigrationsEmptyError is when the Array Migrations asked for by the preview and migrate functions is empty ([]).
  • MoreRemoteThanLocalMigrationsError is when there are more applied migrations in the database than there are in a set of migrations. This doesn't make sense! The local migrations given to the migrate or preview functions must always be equal to or greater than the migrations already applied to the database.
  • EmptyMigrationNameError is when a migration's name is empty or contains only whitespace. Each migration must have a non-empty name.
  • ConflictingMigrationNameError is when two or more migrations share the same name (after trimming surrounding whitespace). Each migration name must be unique.
  • EmptyMigrationStatementsError is when the given statements in a Migration are empty ([]).
  • MigrationStatementMentionsMigrationTableError is when one or more of the statements in a migration contain an exact reference to the __gren_sqlite_migrate table. While unlikely to happen, this prevents accidentally altering the migrations ledger data which would cause problems!
  • MigrationHashesDoNotMatchError is when a migration hash does not match a hash in the database. This points towards a migration having been altered from when it was first applied to the database.
  • MigrationChainDivergedError is when the calculated hash chain in a set of migrations does not align with the hash chain in the database. This points to the order of migrations previously applied being changed.
  • MigrationHashDecodingFailedError is when there is a problem with decoding the base64 encoded strings for migration and chain hashes in the database. This points towards data corruption, where the data on the database may have been changed outside of the functions in this module.