Beginner

// File: src/infrastructure/db/migrations/001_create_products.ts
import { SqlClient } from "@effect/sql"; // => Import SqlClient service tag
import { Effect } from "effect"; // => Import Effect runtime
 
// => Migration is exported as the default export
// => The migrator discovers and runs it by filename
export default Effect.gen(function* () {
  // => yield* resolves SqlClient from the Effect dependency graph
  // => If SqlClient is not provided, Effect fails with a missing dependency error
  const sql = yield* SqlClient.SqlClient;
 
  // => sql is a tagged template literal function
  // => Calling sql`...` executes the SQL string against the connected database
  yield* sql`
    CREATE TABLE IF NOT EXISTS products (
      id SERIAL PRIMARY KEY
    )
  `;
  // => If the SQL executes successfully, Effect returns void
  // => If the SQL fails, Effect propagates a SqlError in the error channel
});

// package.json dependencies for a PostgreSQL Effect SQL project
// {
//   "@effect/sql": "^0.28.0",      // => Core service tags and migration runner
//   "@effect/sql-pg": "^0.28.0",   // => PostgreSQL client (PgClient, PgMigrator)
//   "effect": "^3.12.0"            // => Effect runtime (required peer dependency)
// }
 
// In migration files: only import from @effect/sql
import { SqlClient } from "@effect/sql"; // => SqlClient.SqlClient service tag
import { Effect } from "effect"; // => Effect.gen, Effect.fail, etc.
 
// In main.ts / app entry point: import driver-specific packages
import { PgClient } from "@effect/sql-pg"; // => PgClient.layer factory
import { PgMigrator } from "@effect/sql-pg"; // => PgMigrator.layer factory
// => Migration files themselves never import @effect/sql-pg
// => This preserves database-agnosticism in migration definitions
 
// For SQLite (tests or embedded):
import { SqliteClient } from "@effect/sql-sqlite-node"; // => SqliteClient.layer factory
import { SqliteMigrator } from "@effect/sql-sqlite-node"; // => SqliteMigrator.layer
// => Same migration files run on both PostgreSQL and SQLite unchanged

import { SqlClient } from "@effect/sql"; // => Namespace containing the SqlClient tag
import { Effect } from "effect";
 
const demonstrateSqlClient = Effect.gen(function* () {
  // => yield* SqlClient.SqlClient resolves the live client from the Layer context
  // => Type: SqlClient.SqlClient (the interface, not the tag)
  // => Fails with "Service not found" if no database layer was provided
  const sql = yield* SqlClient.SqlClient;
 
  // => sql is callable as a tagged template literal
  // => sql`SELECT 1` is equivalent to sql.unsafe("SELECT 1")
  // => Template interpolations are automatically parameterized to prevent SQL injection
  const rows = yield* sql`SELECT 1 AS value`;
  // => rows is ReadonlyArray<{ value: number }>
  // => Effect SQL infers row types from the query when possible
 
  // => sql also exposes sql.safe, sql.unsafe, sql.and, sql.or for composing queries
  // => For migrations we almost always use the template literal form
  console.log(rows);
  // => Output: [ { value: 1 } ]
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
// => Effect.gen takes a generator function and returns an Effect value
// => The function* syntax declares a generator; function* () {} is required (not arrow function)
export default Effect.gen(function* () {
  // => Step 1: resolve the database client
  const sql = yield* SqlClient.SqlClient; // => sql: SqlClient.SqlClient interface
 
  // => Step 2: execute first SQL statement
  // => yield* sql`...` suspends the generator until the query completes
  yield* sql`CREATE TABLE IF NOT EXISTS orders (id SERIAL PRIMARY KEY)`;
  // => Resumes here after CREATE TABLE succeeds
  // => If CREATE TABLE fails, the generator does NOT resume — Effect propagates the error
 
  // => Step 3: execute second SQL statement sequentially
  // => Effect.gen guarantees these run in order, not concurrently
  yield* sql`CREATE INDEX IF NOT EXISTS idx_orders_id ON orders(id)`;
  // => Both statements are now complete
 
  // => Effect.gen automatically returns Effect<void, SqlError, SqlClient>
  // => The migrator wraps this in a transaction before running it
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => Static SQL: no interpolations, executes as-is
  yield* sql`
    CREATE TABLE IF NOT EXISTS categories (
      id   SERIAL PRIMARY KEY,
      name VARCHAR(100) NOT NULL
    )
  `;
  // => SQL sent to database: CREATE TABLE IF NOT EXISTS categories (...)
 
  // => Dynamic value: interpolated as a bound parameter
  const defaultName = "Uncategorized";
  // => The value is parameterized — sent as $1 with value "Uncategorized"
  // => This prevents SQL injection even with user-supplied strings
  yield* sql`INSERT INTO categories (name) VALUES (${defaultName})`;
  // => SQL sent: INSERT INTO categories (name) VALUES ($1)
  // => Parameters: ["Uncategorized"]
 
  // => For DDL (CREATE, ALTER, DROP), use static strings only
  // => Dynamic identifiers (table names, column names) require sql.unsafe() or Identifier
  // => sql`CREATE TABLE ${tableName}` would parameterize the name, which is invalid DDL
});

// File: src/infrastructure/db/migrations/index.ts
 
// => Import each migration module's default export
import m001 from "./001_create_users.js"; // => Effect.gen computation for migration 1
import m002 from "./002_create_tokens.js"; // => Effect.gen computation for migration 2
import m003 from "./003_create_products.js"; // => Effect.gen computation for migration 3
 
// => Export a record mapping migration keys to migration Effects
// => Key format: "\d+_<name>" — leading digits determine execution order
export const migrations = {
  "0001_create_users": m001, // => Runs first; key "0001" sorts before "0002"
  "0002_create_tokens": m002, // => Runs second
  "0003_create_products": m003, // => Runs third
  // => Keys are sorted lexicographically by the migrator
  // => Use zero-padded numbers (0001, 0002) to ensure correct sort order
  // => Non-padded numbers would sort "10" before "2" lexicographically
};
 
// => The migrations object is passed to PgMigrator.fromRecord() or SqliteMigrator.fromRecord()
// => The migrator checks the effect_sql_migrations table to skip already-applied migrations
// => Only new keys (not yet in the migrations table) are executed

import { PgClient, PgMigrator } from "@effect/sql-pg";
import { Layer, Effect, Redacted } from "effect";
import { migrations } from "./migrations/index.js"; // => Migration registry
 
// => Step 1: Create the database client layer
// => PgClient.layer accepts a config object with connection details
const dbLayer = PgClient.layer({
  url: Redacted.make("postgresql://user:pass@localhost:5432/mydb"),
  // => Redacted.make wraps the URL to prevent accidental logging
});
 
// => Step 2: Create the migrator layer
// => PgMigrator.layer wraps the migration registry into a runnable Layer
const migratorLayer = PgMigrator.layer({
  loader: PgMigrator.fromRecord(migrations), // => Load migrations from the registry record
  table: "effect_sql_migrations", // => Table name for tracking applied migrations
}).pipe(
  Layer.provide(dbLayer), // => Provide the database connection to the migrator
);
 
// => Step 3: Build the layer to actually run migrations
// => Layer.build executes the layer's acquisition (runs migrations)
// => Effect.scoped ensures the layer's resources are released after migrations complete
const runMigrations = Layer.build(migratorLayer).pipe(Effect.scoped);
 
// => runMigrations is an Effect<void, MigrationError, never>
// => Awaiting it with yield* or runPromise applies all pending migrations

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient; // => Resolve database client
 
  // => CREATE TABLE IF NOT EXISTS is idempotent — safe to run multiple times
  // => Without IF NOT EXISTS, re-running the migration would throw "table already exists"
  yield* sql`
    CREATE TABLE IF NOT EXISTS customers (
      id           SERIAL PRIMARY KEY,
      -- => SERIAL is PostgreSQL shorthand for INTEGER + sequence + DEFAULT nextval(...)
      -- => Equivalent to: id INTEGER NOT NULL DEFAULT nextval('customers_id_seq')
      name         VARCHAR(255) NOT NULL,
      -- => VARCHAR(255): variable-length string, maximum 255 characters
      -- => NOT NULL: database rejects INSERT/UPDATE with NULL in this column
      email        VARCHAR(255) NOT NULL,
      created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
      -- => TIMESTAMPTZ: timestamp with timezone (stores in UTC, displays in session TZ)
      -- => DEFAULT NOW(): database fills this automatically on INSERT
    )
  `;
  // => Table "customers" is now created (or already existed — IF NOT EXISTS guards it)
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => ALTER TABLE adds a column to an existing table
  // => IF NOT EXISTS (PostgreSQL 9.6+) makes this idempotent
  // => Without IF NOT EXISTS, running this migration twice throws "column already exists"
  yield* sql`
    ALTER TABLE customers
    ADD COLUMN IF NOT EXISTS phone VARCHAR(20)
  `;
  // => phone column added: nullable VARCHAR(20)
  // => Nullable by default — existing rows get NULL for the new column
 
  // => Add a second column with a default value
  // => New columns with NOT NULL must have a DEFAULT or the migration will fail
  // => (unless the table is empty, which is never safe to assume in production)
  yield* sql`
    ALTER TABLE customers
    ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT TRUE
  `;
  // => is_active column added: NOT NULL BOOLEAN, existing rows get TRUE
  // => The DEFAULT ensures no constraint violation on existing rows
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => Create the table first
  yield* sql`
    CREATE TABLE IF NOT EXISTS orders (
      id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      user_id    UUID        NOT NULL,
      status     VARCHAR(20) NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `;
 
  // => Single-column index on user_id for fast user order lookups
  // => IF NOT EXISTS prevents "index already exists" errors on re-runs
  yield* sql`
    CREATE INDEX IF NOT EXISTS idx_orders_user_id
    ON orders(user_id)
  `;
  // => Index created: B-tree on orders.user_id
 
  // => Single-column index on status for fast status filtering
  yield* sql`
    CREATE INDEX IF NOT EXISTS idx_orders_status
    ON orders(status)
  `;
  // => Index created: B-tree on orders.status
  // => Both indexes are built within the same migration transaction
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => Create the parent table first (referenced table must exist before the FK)
  yield* sql`
    CREATE TABLE IF NOT EXISTS departments (
      id   UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      name VARCHAR(100) NOT NULL
    )
  `;
 
  // => Create child table with inline foreign key definition
  yield* sql`
    CREATE TABLE IF NOT EXISTS employees (
      id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      department_id UUID        NOT NULL REFERENCES departments(id),
      -- => REFERENCES departments(id) creates an unnamed foreign key constraint
      -- => The database auto-generates a constraint name like "employees_department_id_fkey"
      name          VARCHAR(255) NOT NULL
    )
  `;
  // => employees.department_id must always match a valid departments.id
  // => INSERT/UPDATE on employees with non-existent department_id will fail
 
  // => Alternatively, use a named constraint for better error messages:
  // => CONSTRAINT fk_employees_department FOREIGN KEY (department_id) REFERENCES departments(id)
  // => Named constraints are easier to DROP later if you need to restructure
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  yield* sql`
    CREATE TABLE IF NOT EXISTS users (
      id       UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      username VARCHAR(50) NOT NULL,
      email    VARCHAR(255) NOT NULL,
 
      -- => Named UNIQUE constraint on username
      -- => Name "uq_users_username" is used in error messages and can be dropped explicitly
      CONSTRAINT uq_users_username UNIQUE (username),
 
      -- => Named UNIQUE constraint on email
      -- => PostgreSQL creates a B-tree index for each UNIQUE constraint automatically
      CONSTRAINT uq_users_email UNIQUE (email)
    )
  `;
  // => Both username and email are enforced unique at the database level
  // => Concurrent inserts with the same username/email: one succeeds, one fails with
  // => ERROR: duplicate key value violates unique constraint "uq_users_username"
 
  // => To add a unique constraint to an existing table:
  // => ALTER TABLE users ADD CONSTRAINT uq_users_username UNIQUE (username);
  // => This also creates the backing B-tree index
});

import { PgClient, PgMigrator } from "@effect/sql-pg";
import { NodeContext } from "@effect/platform-node";
import { Layer, Effect, Redacted } from "effect";
import { migrations } from "./migrations/index.js";
 
// => Database connection layer — provides SqlClient to all layers above it
const dbLayer = PgClient.layer({
  url: Redacted.make(process.env["DATABASE_URL"] ?? "postgresql://localhost:5432/mydb"),
});
 
// => Migrator layer — depends on SqlClient and NodeContext (for filesystem loader)
// => PgMigrator.fromRecord does not need NodeContext; only fromFileSystem does
const migratorLayer = PgMigrator.layer({
  loader: PgMigrator.fromRecord(migrations), // => Load from in-memory record (no filesystem)
  table: "effect_sql_migrations", // => Tracking table name
}).pipe(
  Layer.provide(dbLayer), // => Wire database connection into the migrator
  Layer.provide(NodeContext.layer), // => NodeContext needed for some internal Effect APIs
);
 
// => Build the layer: acquisition runs all pending migrations
// => Effect.scoped: releases the scoped resources (connection) when done
const runMigrations = Effect.gen(function* () {
  yield* Layer.build(migratorLayer).pipe(Effect.scoped);
  // => All pending migrations have now been applied
  // => effect_sql_migrations table updated with new entries
  console.log("Migrations complete");
});
 
// => Run migrations as part of application startup
Effect.runPromise(runMigrations).catch(console.error);
// => If any migration fails, the Promise rejects with the SqlError

-- => The effect_sql_migrations table is created automatically by PgMigrator
-- => Its structure (conceptually) is:
CREATE TABLE IF NOT EXISTS effect_sql_migrations (
  migration_id   TEXT        NOT NULL PRIMARY KEY,
  -- => Stores the migration key from the registry (e.g., "0001_create_users")
  -- => PRIMARY KEY prevents the same migration from being recorded twice
  applied_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
  -- => Timestamp of when the migration was applied
  -- => Useful for auditing and debugging
);
 
-- => On each application startup, the migrator:
-- => 1. Creates effect_sql_migrations if it does not exist
-- => 2. Reads all migration_id values from the table
-- => 3. Compares against the registry keys
-- => 4. Runs only the keys NOT yet in the table
-- => 5. Inserts a new row for each successfully applied migration

// => You can configure a custom table name in PgMigrator.layer:
import { PgMigrator } from "@effect/sql-pg";
import { Layer } from "effect";
 
const migratorLayer = PgMigrator.layer({
  loader: PgMigrator.fromRecord({}), // => Empty registry for illustration
  table: "my_schema_migrations", // => Custom table name (default: "effect_sql_migrations")
  // => Useful when multiple migrators share the same database schema
  // => or when integrating with other migration tools in the same database
});
// => migratorLayer will use "my_schema_migrations" instead of "effect_sql_migrations"

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  yield* sql`
    CREATE TABLE IF NOT EXISTS audit_logs (
      id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      -- => gen_random_uuid() generates a UUID v4; available in PostgreSQL 13+ and with pgcrypto
 
      action     VARCHAR(100) NOT NULL,
      -- => NOT NULL with no DEFAULT: caller MUST provide this value
      -- => INSERT without action column fails: "null value in column action"
 
      performed_by VARCHAR(255) NOT NULL DEFAULT 'system',
      -- => NOT NULL + DEFAULT 'system': existing rows get 'system', INSERT can omit this field
      -- => Useful for audit fields when migrating pre-audit tables
 
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      -- => NOT NULL + DEFAULT NOW(): always has a value, auto-populated on INSERT
      -- => NOW() is evaluated at statement execution time, not at DDL time
 
      metadata   TEXT NOT NULL DEFAULT ''
      -- => Empty string default: avoids NULL in string columns
      -- => Some applications prefer '' over NULL for "no value" semantics
    )
  `;
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => UUID primary key pattern used throughout the demo-be-ts-effect codebase
  yield* sql`
    CREATE TABLE IF NOT EXISTS sessions (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      -- => UUID: 128-bit identifier, stored as 16 bytes in PostgreSQL
      -- => gen_random_uuid(): PostgreSQL 13+ built-in; earlier versions need pgcrypto
      -- => DEFAULT gen_random_uuid(): database generates UUID if not provided in INSERT
      -- => Application can also generate UUID client-side: crypto.randomUUID()
 
      user_id   UUID        NOT NULL,
      -- => UUID foreign key (no REFERENCES here — added separately for clarity)
      -- => Type consistency: UUID-to-UUID references never have type mismatch errors
 
      token     VARCHAR(512) NOT NULL,
      -- => Session token stored as a string (typically a signed JWT or opaque token)
 
      expires_at TIMESTAMPTZ NOT NULL,
      -- => Expiry stored in the database for server-side session validation
 
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `;
  // => "id UUID PRIMARY KEY DEFAULT gen_random_uuid()" is the canonical Effect SQL pattern
  // => Seen in 001_create_users.ts, 002_create_refresh_tokens.ts, etc. in demo-be-ts-effect
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  yield* sql`
    CREATE TABLE IF NOT EXISTS articles (
      id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
      title      VARCHAR(500) NOT NULL,
      body       TEXT         NOT NULL,
 
      created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
      -- => TIMESTAMPTZ vs TIMESTAMP: TIMESTAMPTZ stores UTC and converts on retrieval
      -- => TIMESTAMP has no timezone awareness — avoid it for distributed/multi-region apps
      -- => DEFAULT NOW(): database fills created_at on INSERT if not specified
      -- => Application should NOT provide this value; let the database manage it
 
      created_by VARCHAR(255) NOT NULL DEFAULT 'system',
      -- => Audit field: who created the record
      -- => 'system' default covers programmatic inserts without an actor
 
      updated_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
      -- => Does NOT auto-update on UPDATE — this is a creation timestamp with wrong semantics
      -- => To auto-update on UPDATE, use a PostgreSQL trigger or set it in the repository layer
      -- => The demo-be-ts-effect repos update this column explicitly in UPDATE statements
 
      updated_by VARCHAR(255) NOT NULL DEFAULT 'system'
      -- => Audit field: who last updated the record
    )
  `;
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
// => This migration creates multiple tables in a single transaction
// => If any CREATE TABLE fails, all changes are rolled back atomically
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => Statement 1: Create tags table
  yield* sql`
    CREATE TABLE IF NOT EXISTS tags (
      id   UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      name VARCHAR(50) NOT NULL,
      CONSTRAINT uq_tags_name UNIQUE (name)
    )
  `;
  // => tags table created (or already exists)
 
  // => Statement 2: Create article_tags junction table
  yield* sql`
    CREATE TABLE IF NOT EXISTS article_tags (
      article_id UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
      tag_id     UUID NOT NULL REFERENCES tags(id)     ON DELETE CASCADE,
      PRIMARY KEY (article_id, tag_id)
      -- => Composite primary key: no separate id column needed
      -- => Prevents duplicate (article, tag) pairs
    )
  `;
  // => article_tags table created (or already exists)
 
  // => Statement 3: Create index for reverse lookup (find articles by tag)
  yield* sql`
    CREATE INDEX IF NOT EXISTS idx_article_tags_tag_id
    ON article_tags(tag_id)
  `;
  // => All three statements succeed or all three roll back together
});

import { PgClient } from "@effect/sql-pg"; // => PostgreSQL-specific client
import { Layer, Redacted } from "effect";
 
// => Option 1: Connection URL (most common)
const pgLayerFromUrl = PgClient.layer({
  url: Redacted.make("postgresql://user:password@localhost:5432/mydb"),
  // => Redacted.make wraps the value so it prints as "<redacted>" in logs
  // => Without Redacted, the password would appear in Effect traces and error messages
});
 
// => Option 2: Individual connection parameters
const pgLayerFromParams = PgClient.layer({
  host: "localhost", // => Hostname of the PostgreSQL server
  port: 5432, // => Default PostgreSQL port
  database: "mydb", // => Database name
  username: "user", // => Database user
  password: Redacted.make("password"), // => Password wrapped in Redacted
  // => ssl: { rejectUnauthorized: false } — for development with self-signed certs
  // => maxConnections: 10 — connection pool size (default: 10)
});
 
// => Both produce a Layer<SqlClient, SqlError, never>
// => Provide this layer to migrations, repositories, and all SQL-dependent services
const appLayer = Layer.mergeAll(
  pgLayerFromUrl,
  // => other service layers...
);
 
// => The PgClient layer manages a connection pool internally
// => Connections are acquired per-Effect and released after each Effect completes

import { SqliteClient, SqliteMigrator } from "@effect/sql-sqlite-node";
// => @effect/sql-sqlite-node wraps better-sqlite3, a synchronous SQLite driver
// => Despite better-sqlite3 being synchronous, Effect SQL wraps it in the async Effect runtime
import { Layer } from "effect";
import { migrations } from "./migrations/index.js";
 
// => In-memory SQLite: fastest option for unit/integration tests
// => :memory: database is created fresh for each Layer instance
const sqliteLayerInMemory = SqliteClient.layer({
  filename: ":memory:", // => SQLite special value for in-memory database
  // => In-memory database: no disk I/O, automatically destroyed when connection closes
  // => Perfect for tests: each test suite gets a fresh, isolated database
});
 
// => File-based SQLite: for development or embedded applications
const sqliteLayerFile = SqliteClient.layer({
  filename: "./local.db", // => Creates or opens SQLite file at this path
  // => File-based SQLite persists across process restarts
  // => Useful for CLI tools, desktop apps, or development without PostgreSQL
});
 
// => SQLite migrator: same API as PgMigrator but for SQLite
// => No NodeContext required (no filesystem loader needed with fromRecord)
const sqliteMigratorLayer = SqliteMigrator.layer({
  loader: SqliteMigrator.fromRecord(migrations), // => Same migrations record
  table: "effect_sql_migrations", // => Same table name
}).pipe(Layer.provide(sqliteLayerInMemory));
 
// => The same migration files (001_create_users.ts, etc.) run on SQLite unchanged
// => SQLite does not support all PostgreSQL types (e.g., UUID is stored as TEXT)
// => Stick to portable SQL: VARCHAR, INTEGER, BOOLEAN, TEXT, REAL

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => Create a PostgreSQL custom enum type
  // => IF NOT EXISTS guard: PostgreSQL 9.6+ (prevents duplicate type errors)
  yield* sql`
    CREATE TYPE IF NOT EXISTS user_role AS ENUM ('USER', 'ADMIN', 'MODERATOR')
  `;
  // => user_role is now a valid column type in this database
 
  // => Use the custom enum as a column type
  yield* sql`
    CREATE TABLE IF NOT EXISTS user_roles_demo (
      id   UUID      PRIMARY KEY DEFAULT gen_random_uuid(),
      role user_role NOT NULL DEFAULT 'USER'
      -- => role is strongly typed: only 'USER', 'ADMIN', 'MODERATOR' are valid
      -- => INSERT with role = 'SUPERUSER' fails: "invalid input value for enum user_role"
    )
  `;
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => VARCHAR + CHECK: works on PostgreSQL, SQLite, and MySQL
  // => Less type-safe than enum: database validates at write time, not at DDL definition time
  yield* sql`
    CREATE TABLE IF NOT EXISTS accounts (
      id     UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
        CHECK (status IN ('ACTIVE', 'SUSPENDED', 'DELETED'))
      -- => CHECK constraint validates status on every INSERT and UPDATE
      -- => Violation: "check constraint accounts_status_check violated"
    )
  `;
  // => The demo-be-ts-effect codebase uses this VARCHAR pattern for portability
  // => (enables SQLite test runs alongside PostgreSQL production runs)
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  yield* sql`
    CREATE TABLE IF NOT EXISTS products (
      id       UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
      name     VARCHAR(255)   NOT NULL,
 
      price    DECIMAL(10, 2) NOT NULL,
      -- => DECIMAL(10, 2): 10 total digits, 2 after decimal point
      -- => Supports values from -99999999.99 to 99999999.99
 
      quantity INTEGER NOT NULL DEFAULT 0,
      -- => quantity starts at 0, must be non-negative
 
      CONSTRAINT chk_products_price_positive
        CHECK (price > 0),
      -- => Named CHECK: price must be strictly positive
      -- => Violation message: "check constraint chk_products_price_positive violated"
 
      CONSTRAINT chk_products_quantity_non_negative
        CHECK (quantity >= 0),
      -- => Separate named constraint for quantity
      -- => Naming each constraint separately allows targeted application error handling
 
      CONSTRAINT chk_products_name_not_empty
        CHECK (length(trim(name)) > 0)
      -- => CHECK can use PostgreSQL functions like length(), trim(), lower()
      -- => Prevents inserting a name that is purely whitespace
    )
  `;
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  yield* sql`
    CREATE TABLE IF NOT EXISTS transactions (
      id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      user_id    UUID        NOT NULL,
      account_id UUID        NOT NULL,
      type       VARCHAR(20) NOT NULL,
      amount     DECIMAL(19,4) NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `;
 
  // => Single-column index: fast for queries filtering on user_id alone
  yield* sql`
    CREATE INDEX IF NOT EXISTS idx_transactions_user_id
    ON transactions(user_id)
  `;
 
  // => Composite index: fast for queries filtering on (user_id, type) together
  // => Column order: user_id first (higher cardinality / more selective)
  // => This index also supports queries filtering on user_id alone
  // => But NOT queries filtering on type alone (no leading user_id)
  yield* sql`
    CREATE INDEX IF NOT EXISTS idx_transactions_user_id_type
    ON transactions(user_id, type)
  `;
  // => Query "WHERE user_id = ? AND type = ?" benefits from this composite index
  // => Query "WHERE type = ?" does NOT benefit (type is not the leading column)
 
  // => Composite index with different column order for a different access pattern
  yield* sql`
    CREATE INDEX IF NOT EXISTS idx_transactions_account_id_created_at
    ON transactions(account_id, created_at DESC)
  `;
  // => Supports queries: "WHERE account_id = ? ORDER BY created_at DESC"
  // => DESC in index avoids sort operation for descending queries
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => Parent table 1: students
  yield* sql`
    CREATE TABLE IF NOT EXISTS students (
      id   UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      name VARCHAR(255) NOT NULL
    )
  `;
 
  // => Parent table 2: courses
  yield* sql`
    CREATE TABLE IF NOT EXISTS courses (
      id    UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      title VARCHAR(255) NOT NULL
    )
  `;
 
  // => Junction table: student_courses (many-to-many)
  // => A student can enroll in many courses; a course can have many students
  yield* sql`
    CREATE TABLE IF NOT EXISTS student_courses (
      student_id UUID NOT NULL REFERENCES students(id) ON DELETE CASCADE,
      -- => ON DELETE CASCADE: if a student is deleted, their enrollments are deleted too
      course_id  UUID NOT NULL REFERENCES courses(id)  ON DELETE CASCADE,
      -- => ON DELETE CASCADE: if a course is deleted, all enrollments for it are deleted
      enrolled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      -- => Additional columns are fine in junction tables
      -- => This one records when the student enrolled
 
      PRIMARY KEY (student_id, course_id)
      -- => Composite PK: prevents duplicate enrollments
      -- => Same student cannot enroll in the same course twice
    )
  `;
 
  // => Index for reverse lookup: find all students in a course
  yield* sql`
    CREATE INDEX IF NOT EXISTS idx_student_courses_course_id
    ON student_courses(course_id)
  `;
  // => Without this index, "SELECT * FROM student_courses WHERE course_id = ?" is a full scan
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => Create the lookup table first
  yield* sql`
    CREATE TABLE IF NOT EXISTS currencies (
      code   VARCHAR(3)   PRIMARY KEY,
      -- => ISO 4217 currency code (USD, EUR, IDR) as the primary key
      name   VARCHAR(100) NOT NULL,
      symbol VARCHAR(10)  NOT NULL
    )
  `;
 
  // => Seed common currencies
  // => ON CONFLICT DO NOTHING: if a row with the same code already exists, skip it
  // => This makes the seed migration idempotent — safe to re-run
  yield* sql`
    INSERT INTO currencies (code, name, symbol) VALUES
      ('USD', 'US Dollar',         '$'),
      ('EUR', 'Euro',              '€'),
      ('IDR', 'Indonesian Rupiah', 'Rp'),
      ('GBP', 'British Pound',     '£')
    ON CONFLICT (code) DO NOTHING
  `;
  // => If currencies table already has these rows (from a previous run), no error is thrown
  // => If the table is empty, all four rows are inserted
 
  // => For seed data that should update on re-run, use ON CONFLICT DO UPDATE:
  // => ON CONFLICT (code) DO UPDATE SET name = EXCLUDED.name, symbol = EXCLUDED.symbol
  // => EXCLUDED refers to the row that was not inserted due to the conflict
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => CREATE TABLE IF NOT EXISTS: table creation is idempotent
  yield* sql`CREATE TABLE IF NOT EXISTS feature_flags (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(100) NOT NULL)`;
 
  // => CREATE INDEX IF NOT EXISTS: index creation is idempotent (PostgreSQL 9.5+)
  yield* sql`CREATE INDEX IF NOT EXISTS idx_feature_flags_name ON feature_flags(name)`;
 
  // => CREATE UNIQUE INDEX IF NOT EXISTS: unique index creation is idempotent
  yield* sql`CREATE UNIQUE INDEX IF NOT EXISTS uq_feature_flags_name ON feature_flags(name)`;
 
  // => CREATE TYPE IF NOT EXISTS: enum type creation is idempotent (PostgreSQL 9.6+)
  // => Note: some older PostgreSQL versions do not support IF NOT EXISTS for CREATE TYPE
  yield* sql`CREATE TYPE IF NOT EXISTS flag_status AS ENUM ('enabled', 'disabled', 'rollout')`;
 
  // => ALTER TABLE ADD COLUMN IF NOT EXISTS: column addition is idempotent (PostgreSQL 9.6+)
  yield* sql`ALTER TABLE feature_flags ADD COLUMN IF NOT EXISTS status flag_status NOT NULL DEFAULT 'disabled'`;
 
  // => DROP TABLE IF EXISTS: table removal is idempotent
  // => Used in cleanup migrations when removing obsolete tables
  yield* sql`DROP TABLE IF EXISTS obsolete_legacy_table`;
 
  // => DROP INDEX IF EXISTS: index removal is idempotent
  yield* sql`DROP INDEX IF EXISTS obsolete_index_name`;
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => DROP TABLE IF EXISTS: removes the table and all its data
  // => IF EXISTS prevents errors if the table does not exist
  // => CASCADE: also drops dependent objects (views, foreign key constraints referencing this table)
  // => RESTRICT (default): refuses to drop if any dependent objects exist
  yield* sql`DROP TABLE IF EXISTS deprecated_sessions CASCADE`;
  // => Use CASCADE only when you are certain dropping dependents is intentional
  // => Use RESTRICT (or omit the keyword) to fail safely if dependents exist
 
  // => DROP COLUMN: removes a column and all its data
  // => IF EXISTS: prevents errors if the column does not exist
  yield* sql`ALTER TABLE users DROP COLUMN IF EXISTS legacy_avatar_url`;
  // => legacy_avatar_url column removed from users table
  // => Data in that column is permanently gone
 
  // => Best practice: add a migration to mark column as deprecated (rename, set to NULL)
  // => before the migration that drops it
  // => This gives time to update application code before the schema change
  // => Two-phase migration: phase 1 (nullify/rename), phase 2 (drop after deployment)
});

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
export default Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  yield* sql`
    CREATE TABLE IF NOT EXISTS projects (
      id   UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      name VARCHAR(255) NOT NULL
    )
  `;
 
  // => ON DELETE CASCADE: deleting a project deletes all its tasks
  // => Appropriate when tasks have no meaning without their project
  yield* sql`
    CREATE TABLE IF NOT EXISTS tasks (
      id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      project_id UUID        NOT NULL
        REFERENCES projects(id) ON DELETE CASCADE,
      -- => If projects row is deleted, all tasks with that project_id are deleted
      -- => No manual cleanup needed; database handles it atomically
      title      VARCHAR(255) NOT NULL
    )
  `;
 
  // => ON DELETE SET NULL: deleting a project NULLs the project_id on tasks
  // => Appropriate when tasks should persist but become "unassigned"
  // => (column must be nullable for this to work)
  yield* sql`
    CREATE TABLE IF NOT EXISTS archived_tasks (
      id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      project_id UUID
        REFERENCES projects(id) ON DELETE SET NULL,
      -- => project_id is nullable (no NOT NULL constraint)
      -- => If projects row is deleted, archived_tasks.project_id becomes NULL
      title      VARCHAR(255) NOT NULL
    )
  `;
  // => See 005_create_attachments.ts in demo-be-ts-effect:
  // => expense_id UUID NOT NULL REFERENCES expenses(id) ON DELETE CASCADE
  // => Attachments are meaningless without their expense — CASCADE is correct there
});

// => Migration file naming convention (from demo-be-ts-effect):
//
// 001_create_users.ts         => key: "0001_create_users"
// 002_create_refresh_tokens.ts => key: "0002_create_refresh_tokens"
// 003_create_revoked_tokens.ts => key: "0003_create_revoked_tokens"
// 004_create_expenses.ts       => key: "0004_create_expenses"
// 005_create_attachments.ts    => key: "0005_create_attachments"
//
// => File name uses 3-digit padding (001, 002)
// => Registry key uses 4-digit padding (0001, 0002) for future-proofing
// => Either is fine as long as you are consistent; lexicographic sort must match intent
 
// => Correct registry — sorted by zero-padded key:
import m001 from "./001_create_users.js";
import m002 from "./002_create_refresh_tokens.js";
 
export const migrations = {
  "0001_create_users": m001, // => Runs first
  "0002_create_refresh_tokens": m002, // => Runs second (depends on users table)
};
 
// => WRONG — non-padded numbers break sort order at 10+:
// export const wrongMigrations = {
//   "1_create_users": m001,      // => "10_..." would sort before "2_..." lexicographically
//   "2_create_tokens": m002,
// };
// => Use zero-padded numbers to avoid this class of bug
 
// => Naming convention for the description part:
// => Use snake_case, starting with a verb describing the operation:
// => create_users, add_phone_to_customers, drop_legacy_sessions, add_idx_orders_user_id

import { SqlClient } from "@effect/sql";
import { Effect } from "effect";
 
// => Standard migration: errors propagate automatically
// => The PgMigrator catches SqlError and rolls back the transaction
export const standardMigration = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
  yield* sql`CREATE TABLE IF NOT EXISTS widgets (id UUID PRIMARY KEY DEFAULT gen_random_uuid())`;
  // => If CREATE TABLE fails, SqlError propagates up to the migrator
  // => Migrator rolls back the transaction and does not update effect_sql_migrations
});
 
// => Migration with diagnostic logging (Effect.tap does not change the Effect's return type)
export const migrationWithLogging = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
 
  // => Effect.tap: runs a side effect, then continues with the original value
  // => Use for logging without affecting the Effect's result
  yield* Effect.tap(sql`CREATE TABLE IF NOT EXISTS widgets (id UUID PRIMARY KEY DEFAULT gen_random_uuid())`, () =>
    Effect.sync(() => console.log("widgets table created successfully")),
  );
  // => Console logs "widgets table created successfully" on success
  // => On SqlError, the tap's inner Effect does not run; error propagates
 
  // => Effect.catchAll: handle specific errors, re-throw others
  // => Use sparingly in migrations — most errors should abort and roll back
  yield* sql`CREATE INDEX IF NOT EXISTS idx_widgets_id ON widgets(id)`.pipe(
    Effect.catchAll((error) =>
      // => Log the error but continue the migration (not recommended for most cases)
      Effect.sync(() => console.warn("Index creation failed (non-fatal):", String(error))),
    ),
  );
}).pipe(
  // => Effect.mapError at the migration level: transform SqlError to a domain error
  // => Useful when the migrator needs to distinguish schema errors from connection errors
  Effect.mapError((e) => new Error(`Migration failed: ${String(e)}`)),
);
 
export default standardMigration;
// => For most migrations, export the standard form without manual error handling