<!-- SPDX-FileCopyrightText: 2020 Zach Daniel SPDX-License-Identifier: MIT -->

Rules for working with AshPostgres

Understanding AshPostgres

AshPostgres is the PostgreSQL data layer for Ash Framework. It's the most fully-featured Ash data layer and should be your default choice unless you have specific requirements for another data layer. Any PostgreSQL version higher than 13 is fully supported.

Basic Configuration

To use AshPostgres, add the data layer to your resource:

defmodule MyApp.Tweet do
  use Ash.Resource,
    data_layer: AshPostgres.DataLayer

  attributes do
    integer_primary_key :id
    attribute :text, :string
  end

  relationships do
    belongs_to :author, MyApp.User
  end

  postgres do
    table "tweets"
    repo MyApp.Repo
  end
end

PostgreSQL Configuration

Table & Schema Configuration

postgres do
  # Required: Define the table name for this resource
  table "users"

  # Optional: Define the PostgreSQL schema
  schema "public"

  # Required: Define the Ecto repo to use
  repo MyApp.Repo

  # Optional: Control whether migrations are generated for this resource
  migrate? true
end

Foreign Key References

Use the

references

section to configure foreign key behavior:

postgres do
  table "comments"
  repo MyApp.Repo

  references do
    # Simple reference with defaults
    reference :post

    # Fully configured reference
    reference :user,
      on_delete: :delete,      # What happens when referenced row is deleted
      on_update: :update,      # What happens when referenced row is updated
      name: "comments_to_users_fkey", # Custom constraint name
      deferrable: true,        # Make constraint deferrable
      initially_deferred: false # Defer constraint check to end of transaction
  end
end

Foreign Key Actions

For

on_delete

and

on_update

options:

• :nothing

  or

  :restrict

  - Prevent the change to the referenced row

• :delete

  - Delete the row when the referenced row is deleted (for

  on_delete

  only)

• :update

  - Update the row according to changes in the referenced row (for

  on_update

  only)

• :nilify

  - Set all foreign key columns to NULL

• {:nilify, columns}

  - Set specific columns to NULL (Postgres 15.0+ only)

Warning: These operations happen directly at the database level. No resource logic, authorization rules, validations, or notifications are triggered.

Check Constraints

Define database check constraints:

postgres do
  check_constraints do
    check_constraint :positive_amount,
      check: "amount > 0",
      name: "positive_amount_check",
      message: "Amount must be positive"

    check_constraint :status_valid,
      check: "status IN ('pending', 'active', 'completed')"
  end
end

Custom Indexes

Define custom indexes beyond those automatically created for identities and relationships:

postgres do
  custom_indexes do
    index [:first_name, :last_name]

    index :email,
      unique: true,
      name: "users_email_index",
      where: "email IS NOT NULL",
      using: :gin

    index [:status, :created_at],
      concurrently: true,
      include: [:user_id]
  end
end

Custom SQL Statements

Include custom SQL in migrations:

postgres do
  custom_statements do
    statement "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\""

    statement """
    CREATE TRIGGER update_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_timestamp();
    """

    statement "DROP INDEX IF EXISTS posts_title_index",
      on_destroy: true # Only run when resource is destroyed/dropped
  end
end

Migrations and Codegen

Development Migration Workflow (Recommended)

For development iterations, use the dev workflow to avoid naming migrations prematurely:

1. Make resource changes
2. Run

   mix ash.codegen --dev

   to generate and run dev migrations
3. Review the migrations and run

   mix ash.migrate

   to run them
4. Continue making changes and running

   mix ash.codegen --dev

   as needed
5. When your feature is complete, run

   mix ash.codegen add_feature_name

   to generate final named migrations (this will rollback dev migrations and squash them)
6. Review the migrations and run

   mix ash.migrate

   to run them

Traditional Migration Generation

For single-step changes or when you know the final feature name:

1. Run

   mix ash.codegen add_feature_name

   to generate migrations
2. Review the generated migrations in

   priv/repo/migrations

3. Run

   mix ash.migrate

   to apply the migrations

Tip: The dev workflow (

--dev

flag) is preferred during development as it allows you to iterate without thinking of migration names and provides better development ergonomics.

Warning: Always review migrations before applying them to ensure they are correct and safe.

Multitenancy

AshPostgres supports schema-based multitenancy:

defmodule MyApp.Tenant do
  use Ash.Resource,
    data_layer: AshPostgres.DataLayer

  # Resource definition...

  postgres do
    table "tenants"
    repo MyApp.Repo

    # Automatically create/manage tenant schemas
    manage_tenant do
      template ["tenant_", :id]
    end
  end
end

Setting Up Multitenancy

1. Configure your repo to support multitenancy:

defmodule MyApp.Repo do
  use AshPostgres.Repo, otp_app: :my_app

  # Return all tenant schemas for migrations
  def all_tenants do
    import Ecto.Query, only: [from: 2]
    all(from(t in "tenants", select: fragment("? || ?", "tenant_", t.id)))
  end
end

2. Mark resources that should be multi-tenant:

defmodule MyApp.Post do
  use Ash.Resource,
    data_layer: AshPostgres.DataLayer

  multitenancy do
    strategy :context
    attribute :tenant
  end

  # Resource definition...
end

3. When tenant migrations are generated, they'll be in

   priv/repo/tenant_migrations

4. Run tenant migrations in addition to regular migrations:

# Run regular migrations
mix ash.migrate

# Run tenant migrations
mix ash_postgres.migrate --tenants

Advanced Features

Manual Relationships

For complex relationships that can't be expressed with standard relationship types:

defmodule MyApp.Post.Relationships.HighlyRatedComments do
  use Ash.Resource.ManualRelationship
  use AshPostgres.ManualRelationship

  def load(posts, _opts, context) do
    post_ids = Enum.map(posts, & &1.id)

    {:ok,
     MyApp.Comment
     |> Ash.Query.filter(post_id in ^post_ids)
     |> Ash.Query.filter(rating > 4)
     |> MyApp.read!()
     |> Enum.group_by(& &1.post_id)}
  end

  def ash_postgres_join(query, _opts, current_binding, as_binding, :inner, destination_query) do
    {:ok,
     Ecto.Query.from(_ in query,
       join: dest in ^destination_query,
       as: ^as_binding,
       on: dest.post_id == as(^current_binding).id,
       on: dest.rating > 4
     )}
  end

  # Other required callbacks...
end

# In your resource:
relationships do
  has_many :highly_rated_comments, MyApp.Comment do
    manual MyApp.Post.Relationships.HighlyRatedComments
  end
end

Using Multiple Repos (Read Replicas)

Configure different repos for reads vs mutations:

postgres do
  repo fn resource, type ->
    case type do
      :read -> MyApp.ReadReplicaRepo
      :mutate -> MyApp.WriteRepo
    end
  end
end

Best Practices

1. Organize migrations: Run

   mix ash.codegen

   after each meaningful set of resource changes with a descriptive name:

   mix ash.codegen --name add_user_roles
   mix ash.codegen --name implement_post_tagging
   

2. Use check constraints for domain invariants: Enforce data integrity at the database level:

   check_constraints do
     check_constraint :valid_status, check: "status IN ('pending', 'active', 'completed')"
     check_constraint :positive_balance, check: "balance >= 0"
   end
   

3. Use custom statements for schema-only changes: If you need to add database objects not directly tied to resources:

   custom_statements do
     statement "CREATE EXTENSION IF NOT EXISTS \"pgcrypto\""
     statement "CREATE INDEX users_search_idx ON users USING gin(search_vector)"
   end
   

Remember that using AshPostgres provides a full-featured PostgreSQL data layer for your Ash application, giving you both the structure and declarative approach of Ash along with the power and flexibility of PostgreSQL.