Drops.SQL.Postgres (drops_relation v0.1.0)

View Source

PostgreSQL database adapter for introspecting table metadata.

This module implements the Drops.SQL.Database behavior to provide PostgreSQL-specific database introspection capabilities. It uses PostgreSQL's system catalogs and information schema to extract comprehensive table metadata including columns, constraints, indices, and foreign keys.

PostgreSQL-Specific Features

PostgreSQL has rich metadata capabilities that this adapter leverages:

  • System catalogs - Direct access to pg_* tables for detailed metadata
  • Array types - Full support for PostgreSQL array types
  • Custom types - Handles user-defined types and enums
  • Advanced constraints - Check constraints, exclusion constraints
  • Inheritance - Table inheritance relationships
  • Partitioning - Partitioned table metadata

System Catalogs Used

The adapter queries several PostgreSQL system catalogs:

  • pg_attribute - Column information
  • pg_type - Type information including arrays
  • pg_class - Table and index information
  • pg_constraint - Constraint information
  • pg_index - Index details
  • pg_namespace - Schema information
  • pg_attrdef - Default value expressions

Type Mapping

PostgreSQL types are mapped to Ecto types through the Drops.SQL.Compilers.Postgres compiler. The adapter handles:

  • Standard types - integer, text, boolean, etc.
  • Array types - integer[], text[], etc. → {:array, base_type}
  • Timestamp types - With and without timezone
  • JSON types - json and jsonb → :map
  • UUID type - Native UUID support
  • Geometric types - point, polygon, etc.

Usage

# Direct usage (typically not needed)
{:ok, table} = Drops.SQL.Postgres.table("users", MyApp.Repo)

# Preferred usage through main interface
{:ok, table} = Drops.SQL.Database.table("users", MyApp.Repo)

Implementation Notes

  • Uses complex SQL queries to extract complete metadata
  • Handles PostgreSQL's internal type names (e.g., int4 → integer)
  • Supports array type detection and mapping
  • Processes default value expressions correctly
  • Handles composite primary keys and foreign keys
  • Extracts check constraints from system catalogs

Error Handling

The adapter handles various PostgreSQL-specific error conditions:

  • Table not found in specified schema
  • Permission denied on system catalogs
  • Invalid type mappings
  • Constraint parsing errors

Summary

Functions

adapter()

Returns the database adapter identifier.

introspect_table(table_name, repo)

Introspects a PostgreSQL table and returns its complete metadata as an AST.

list_tables(repo)

Lists all tables in the PostgreSQL database.

opts()

Returns the complete adapter configuration options.

table(name, repo)

Introspects and compiles a database table into a structured representation.

Functions

adapter()

@spec adapter() :: atom()

Returns the database adapter identifier.

This function provides quick access to the adapter identifier atom that was specified in the use Drops.SQL.Database configuration.

Returns

The adapter identifier atom.

Examples

MyAdapter.adapter()
# => :postgres

introspect_table(table_name, repo)

@spec introspect_table(String.t(), module()) ::
  {:ok, Drops.SQL.Database.table()} | {:error, term()}

Introspects a PostgreSQL table and returns its complete metadata as an AST.

This function implements the Drops.SQL.Database behavior for PostgreSQL databases. It uses PostgreSQL's system catalogs to extract comprehensive table information including columns, foreign keys, and indices.

Process

  1. Introspects foreign keys using system catalog queries
  2. Introspects indices using pg_index and related catalogs
  3. Introspects columns using pg_attribute with cross-referenced FK/index data
  4. Combines all metadata into a table AST structure

Parameters

  • table_name - The name of the PostgreSQL table to introspect
  • repo - The Ecto repository configured for PostgreSQL

Returns

  • {:ok, Database.table()} - Successfully introspected table AST
  • {:error, term()} - Error during introspection (table not found, etc.)

AST Structure

Returns a table AST in the format: {:table, {{:identifier, table_name}, columns, foreign_keys, indices}}

PostgreSQL-Specific Behavior

  • Handles PostgreSQL's rich type system including arrays
  • Processes complex default value expressions
  • Extracts check constraints from system catalogs
  • Handles composite primary keys and foreign keys
  • Supports PostgreSQL-specific constraint types
  • Maps internal type names to standard PostgreSQL types

list_tables(repo)

@spec list_tables(module()) :: {:ok, [String.t()]} | {:error, term()}

Lists all tables in the PostgreSQL database.

This function implements the list_tables/1 callback for PostgreSQL databases. It queries the information schema to retrieve all base tables in the public schema.

Parameters

  • repo - The Ecto repository configured for PostgreSQL

Returns

  • {:ok, [String.t()]} - Successfully retrieved list of table names
  • {:error, term()} - Error during query execution

Examples

{:ok, tables} = Drops.SQL.Postgres.list_tables(MyApp.Repo)
# => {:ok, ["users", "posts", "comments"]}

Implementation Notes

  • Only returns tables from the 'public' schema
  • Excludes views, materialized views, and other non-table objects
  • Results are ordered alphabetically by table name

opts()

@spec opts() :: keyword()

Returns the complete adapter configuration options.

This function provides access to all configuration options passed to the use Drops.SQL.Database macro, including the adapter identifier and compiler module.

Returns

A keyword list containing all adapter configuration options.

Examples

MyAdapter.opts()
# => [adapter: :postgres, compiler: Drops.SQL.Compilers.Postgres]

table(name, repo)

@spec table(String.t(), module()) ::
  {:ok, Drops.SQL.Database.Table.t()} | {:error, term()}

Introspects and compiles a database table into a structured representation.

This function provides the main interface for the adapter, combining both introspection and compilation steps into a single operation. It calls the adapter's introspect_table/2 implementation and then processes the resulting AST through the configured compiler.

Parameters

  • name - The name of the database table to introspect (as a string)
  • repo - The Ecto repository module configured for database access

Returns

  • {:ok, Table.t()} - Successfully compiled table with complete metadata
  • {:error, term()} - Error during introspection or compilation

Examples

# Introspect a users table
{:ok, table} = MyAdapter.table("users", MyApp.Repo)

# Access the compiled table data
table.name          # :users
table.columns       # [%Column{...}, ...]
table.primary_key   # %PrimaryKey{...}

Error Handling

case MyAdapter.table("users", MyApp.Repo) do
  {:ok, table} ->
    process_table(table)
  {:error, reason} ->
    Logger.error("Failed to introspect table: #{inspect(reason)}")
end