Migrations

Database-agnostic schema migrations for .NET, driven by YAML. The DataProviderMigrate CLI tool reads a YAML schema definition and applies it to SQLite or PostgreSQL — with support for tables, columns, primary keys, foreign keys, indexes, and more.

Raw schema.sql files are intentionally not supported. YAML schemas are the single source of truth so a migration behaves identically on every target database.

Install

dotnet new tool-manifest
dotnet tool install DataProviderMigrate --version 0.9.12-beta

This registers the DataProviderMigrate command via the local tool manifest (.config/dotnet-tools.json).

Optional libraries — only needed if you embed migration logic inside your own application code:

dotnet add package Nimblesite.DataProvider.Migration.Core   --version 0.9.12-beta
dotnet add package Nimblesite.DataProvider.Migration.SQLite --version 0.9.12-beta

Commands

Applying a schema

# SQLite
dotnet DataProviderMigrate migrate \
  --schema example-schema.yaml \
  --output app.db \
  --provider sqlite

# PostgreSQL
dotnet DataProviderMigrate migrate \
  --schema example-schema.yaml \
  --output "Host=localhost;Database=mydb;Username=user;Password=pass" \
  --provider postgres

Exporting a schema

dotnet DataProviderMigrate export \
  --assembly ./bin/Debug/net10.0/MyApp.dll \
  --type MyApp.Schemas.InvoiceSchema \
  --output invoice-schema.yaml

YAML schema format

name: example
tables:
  - name: Customer
    schema: main
    columns:
      - name: Id
        type: Text
        isNullable: false
      - name: CustomerName
        type: Text
        isNullable: false
      - name: Email
        type: Text
        isNullable: true
    primaryKey:
      columns:
        - Id

  - name: Invoice
    schema: main
    columns:
      - name: Id
        type: Text
        isNullable: false
      - name: CustomerId
        type: Text
        isNullable: false
      - name: Amount
        type: Real
        isNullable: false
    primaryKey:
      columns:
        - Id
    foreignKeys:
      - columns:
          - CustomerId
        referencedTable: Customer
        referencedColumns:
          - Id
        onDelete: NoAction

Rules

• Every table must have a single primary key.
• Primary keys must be UUIDs (Text at the column level, generated by your application).
• Foreign keys reference tables by name; onDelete accepts NoAction, Cascade, SetNull, or Restrict.
• Supported column types include Text, Integer, Real, Blob, Boolean, DateTime, Guid, and vector types on PostgreSQL.

Row-Level Security (RLS)

Declare row-level access control directly in YAML. The same definition produces native CREATE POLICY on PostgreSQL and trigger-based emulation on SQLite. Spec: docs/specs/rls-spec.md.

tables:
  - name: documents
    schema: public
    columns:
      - { name: id, type: Uuid, isNullable: false }
      - { name: tenant_id, type: Uuid, isNullable: false }
      - { name: title, type: VarChar(200), isNullable: false }
    primaryKey: { columns: [id] }
    rowLevelSecurity:
      enabled: true
      forced: true                 # Postgres only — forces RLS on table owner too
      policies:
        - name: documents_member
          operations: [All]
          roles: [app_user]
          # LQL — portable. current_user_id() expands per-platform.
          using: "tenant_id = current_user_id()"
          withCheck: "tenant_id = current_user_id()"
        - name: documents_admin_all
          operations: [All]
          roles: [app_admin]
          # Raw SQL escape hatch — Postgres only. Required for SECURITY
          # DEFINER function calls (is_member, is_owner, ...) where LQL
          # exists() rewrites would evaluate under the caller's RLS context.
          usingSql: "true"
          withCheckSql: "true"

Session context

Application code sets the current user identity per-transaction so policies have something to compare against:

The LQL builtin current_user_id() expands to the appropriate read-side expression on each platform.

Predicate expressions

• LQL (using, withCheck) — portable. Comparisons against columns, current_user_id(), and exists(pipeline) for cross-table membership checks.
• Raw SQL (usingSql, withCheckSql) — Postgres only, emitted verbatim. Use when you need SECURITY DEFINER function calls or platform-specific syntax. Takes precedence over the LQL form when both are set.

Drift handling

SchemaDiff.Calculate compares the live database (via pg_policies / SQLite sqlite_master) against your YAML and emits the minimal operation set:

• New table with RLS → CreateTableOperation then EnableRlsOperation then CreateRlsPolicyOperation per policy
• Re-running against a converged database → zero operations (idempotent)
• Policy renamed in YAML → DropRlsPolicyOperation (old name) + CreateRlsPolicyOperation (new name) — but only when allowDestructive: true. Forward-only mode never drops orphans

SQLite emulation

SQLite has no native RLS. The migration tool emits:

• __rls_context shadow table to hold the current user id
• BEFORE INSERT/UPDATE/DELETE triggers per policy that RAISE(ABORT, ...) when the predicate is violated
• {TableName}_secure view filtering SELECT (SQLite triggers don't intercept reads — applications query the _secure view for row-level read enforcement)

Error codes

Wiring into MSBuild

Regenerate the database on every build so developers never run migrations manually:

<Target Name="RunDataProviderMigrate" BeforeTargets="CoreCompile">
  <Exec Command="dotnet DataProviderMigrate migrate --schema example-schema.yaml --output app.db --provider sqlite" />
</Target>

Projects

Related

• DataProvider — generated extension methods for the tables defined here
• LQL — write portable queries against the migrated schema
• Migration spec: docs/specs/migration-spec.md