nim-chat-sdk/chat_sdk/migration.nim

import os, sequtils, algorithm, strutils
import db_connector/db_sqlite
import chronicles

proc ensureMigrationTable(db: DbConn) =
  db.exec(
    sql"""
    CREATE TABLE IF NOT EXISTS schema_migrations (
      filename TEXT PRIMARY KEY,
      applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  """
  )

proc hasMigrationRun(db: DbConn, filename: string): bool =
  for row in db.fastRows(
    sql"SELECT 1 FROM schema_migrations WHERE filename = ?", filename
  ):
    return true
  return false

proc markMigrationRun(db: DbConn, filename: string) =
  db.exec(sql"INSERT INTO schema_migrations (filename) VALUES (?)", filename)

proc runMigrations*(db: DbConn, dir = "migrations") =
  info "Migration process starting up"
  ensureMigrationTable(db)
  let files = walkFiles(dir / "*.sql").toSeq().sorted()
  for file in files:
    if hasMigrationRun(db, file):
      info "Migration already applied", file
    else:
      info "Applying migration", file
      let sqlContent = readFile(file)
      db.exec(sql"BEGIN TRANSACTION")
      try:
        # Split by semicolon and execute each statement separately
        for stmt in sqlContent.split(';'):
          let trimmedStmt = stmt.strip()
          if trimmedStmt.len > 0:
            db.exec(sql(trimmedStmt))
        markMigrationRun(db, file)
        db.exec(sql"COMMIT")
      except:
        db.exec(sql"ROLLBACK")
        raise newException(ValueError, "Migration failed: " & file & " - " & getCurrentExceptionMsg())