Bylaw.Postgres

Validate Postgres database structure and enforce schema conventions with bylaw_postgres.

This package owns Bylaw.Db.Adapters.Postgres and Bylaw.Db.Adapters.Postgres.Checks.*. It also includes Ecto helper modules used by the changeset constraint checks.

Installation

Add bylaw_postgres to applications that want Postgres schema validation:

def deps do
  [
    {:bylaw_postgres, "~> 0.3.0", only: [:dev, :test]}
  ]
end

bylaw_postgres depends on bylaw_db, ecto_sql, and postgrex; consuming applications should already have an Ecto SQL repo and Postgres driver available.

Usage

Most projects run database-shape checks from ExUnit after the test database has been created and migrated:

defmodule MyApp.BylawDbTest do
  use ExUnit.Case, async: false

  alias Bylaw.Db.Adapters.Postgres

  @checks [
    Bylaw.Db.Adapters.Postgres.Checks.MissingForeignKeyIndexes,
    Bylaw.Db.Adapters.Postgres.Checks.MissingForeignKeyConstraints,
    Bylaw.Db.Adapters.Postgres.Checks.ForeignKeyNullability,
    Bylaw.Db.Adapters.Postgres.Checks.DuplicateIndexes
  ]

  test "database structure satisfies Bylaw checks" do
    assert :ok = Postgres.validate(MyApp.Repo, @checks)
  end
end

Postgres.validate/2 validates one repo per call. Pass :dynamic_repo to validate/3 when a specific dynamic repo should be inspected.

test "tenant database structure satisfies Bylaw checks" do
  assert :ok = Postgres.validate(MyApp.Repo, @checks, dynamic_repo: :tenant_one)
end

For multiple repos, make separate calls:

test "database structure satisfies Bylaw checks" do
  assert :ok = Postgres.validate(MyApp.Repo, @checks)
  assert :ok = Postgres.validate(MyApp.AnalyticsRepo, @checks)
end

See each check module's documentation for its examples, notes, and options.

Ecto query unique keys

Bylaw.Db.Adapters.Postgres.UniqueKeys.fetch!/1 reads primary and conservative unique column sets for use with Bylaw.Ecto.Query.Checks.DeterministicOrder:

defp query_checks do
  [
    {Bylaw.Ecto.Query.Checks.DeterministicOrder,
     unique_keys: fn ->
       Bylaw.Db.Adapters.Postgres.UniqueKeys.fetch!(__MODULE__)
     end}
  ]
end

The result is a map keyed by {database_schema, table}:

%{
  {"public", "posts"} => [
    ["id"],
    ["slug"],
    ["organisation_id", "sequence"]
  ]
}

The helper is stateless and performs one catalogue query per invocation. DeterministicOrder skips the resolver when Ecto primary-key reflection already proves the ordering.

Rules DSL

Every built-in check accepts the same rules: DSL. Checks with default behavior can be passed as bare modules to run globally:

@checks [
  Bylaw.Db.Adapters.Postgres.Checks.DuplicateIndexes
]

Use {Check, rules: [...]} when a check needs required rule options or should run only when at least one rule matches. Rules use shared scope keys and check-specific rule options side by side:

@checks [
  {Bylaw.Db.Adapters.Postgres.Checks.RequiredColumns,
   rules: [columns: ["tenant_id"]]},
  {Bylaw.Db.Adapters.Postgres.Checks.ForeignKeyActions,
   rules: [
     [where: [referenced_tables: ["lookup_statuses"]], on_delete: :restrict, on_update: :restrict],
     [where: [tables: ["messages"]], except: [constraints: ["messages_status_id_fkey"]], on_delete: :cascade]
   ]},
  Bylaw.Db.Adapters.Postgres.Checks.DuplicateIndexes
]

Shared scope keys:

where: applies a rule when any matcher matches. Omit it for a global rule.
except: suppresses a rule that would otherwise match.

Postgres matchers use plural keys with non-empty list values: schemas:, tables:, columns:, constraints:, types:, referenced_schemas:, referenced_tables:, and referenced_columns: where supported by the check. Matcher values can be strings or regexes. Unknown rule keys and missing required check-specific options raise ArgumentError messages that name the check. Top-level validate: false disables the whole check.

Checks with no check-specific rule options accept only shared scope keys inside rules. Checks with required rule options document those options in their module docs with copyable rule examples.