Simple .sql migration file runner

migrator.gleam

import gleam/erlang/file
import gleam/list
import gleam/string
import gleam/result
import gleam/io
import gleam/pgo
import gleam/dynamic

/// Files are to be named like 1_init.sql, 2_add_column.sql, etc.
pub fn migrate(db: pgo.Connection, dir: String) {
  ensure_migration_tables(db)

  file.list_directory(dir)
  |> result.unwrap(or: [])
  |> list.filter(fn(file) { string.ends_with(file, ".sql") })
  |> list.sort(string.compare)
  |> list.map(fn(file) {
    #(
      string.split(file, "_")
      |> list.at(0)
      |> result.unwrap(or: "0"),
      file,
    )
  })
  |> list.each(fn(info) {
    let version = info.0
    let file = info.1

    run_migration(db, version, dir <> "/" <> file)
  })

  Ok(Nil)
}

fn run_migration(db: pgo.Connection, key: String, path: String) {
  case file.read(path) {
    Ok(migration) -> {
      case
        pgo.execute(
          "SELECT version FROM migrations WHERE version = $1",
          db,
          [pgo.text(key)],
          Ok,
        )
      {
        Ok(existing_migration) -> {
          case existing_migration.count >= 1 {
            True -> {
              io.println(
                "[INFO] Skipping migration " <> key <> " because it has already been run",
              )
              Ok(Nil)
            }
            False -> {
              io.println("[INFO] Running migration " <> key)
              case pgo.execute(migration, db, [], Ok) {
                Ok(_) -> {
                  case
                    pgo.execute(
                      "INSERT INTO migrations (version) VALUES ($1)",
                      db,
                      [pgo.text(key)],
                      Ok,
                    )
                  {
                    Ok(_) -> {
                      io.println("[INFO] Successfully ran migration " <> key)
                      Ok(Nil)
                    }
                    Error(e) -> {
                      io.println(
                        "[WARN] Successfully ran migration " <> key <> " but failed to record it as having been run",
                      )
                      Error(e)
                    }
                  }
                }
                Error(e) -> {
                  io.println("[ERROR] Failed to run migration " <> key)
                  io.debug(e)
                  Error(e)
                }
              }
            }
          }
          Ok(Nil)
        }
        Error(e) -> {
          io.println(
            "[ERROR] Failed to check existance of the migration " <> key,
          )
          io.debug(e)
          Error(e)
        }
      }
    }
    Error(_e) -> {
      io.println("[ERROR] Failed to read migration file " <> path)
      Ok(Nil)
    }
  }
}

fn ensure_migration_tables(db: pgo.Connection) {
  pgo.execute(
    "CREATE TABLE IF NOT EXISTS migrations (version TEXT PRIMARY KEY);",
    db,
    [],
    Ok,
  )
}