PostgresCatalog.jl

PostgresCatalog is a Julia library for introspecting Postgres databases and generating models of the database structure. It provides information about database schemas, types, tables, columns, unique and foreign key constraints.

Ability to modify the database structure will be added in a future release.

Installation

Use the Julia package manager.

julia> using Pkg
julia> Pkg.add("PostgresCatalog")

Usage Guide

To demonstrate PostgresCatalog, we create a database containing just one table.

using LibPQ

conn = LibPQ.Connection("")

execute(conn, "BEGIN")

execute(conn,
        """
        CREATE TYPE patient_sex_enum AS ENUM ('male', 'female', 'other', 'unknown');

        CREATE TABLE patient (
            id int4 NOT NULL,
            mrn text NOT NULL,
            sex patient_sex_enum NOT NULL DEFAULT 'unknown',
            mother_id int4,
            father_id int4,
            CONSTRAINT patient_uk UNIQUE (id),
            CONSTRAINT patient_pk PRIMARY KEY (mrn),
            CONSTRAINT patient_mother_fk FOREIGN KEY (mother_id) REFERENCES patient (id),
            CONSTRAINT patient_father_fk FOREIGN KEY (father_id) REFERENCES patient (id)
        );
        """)

Function PostgresCatalog.introspect generates a PostgresCatalog.PGCatalog object containing a model of this database.

using PostgresCatalog

cat = PostgresCatalog.introspect(conn)
#-> DATABASE " … "

By traversing the catalog, we can obtain the table model represented by a PostgresCatalog.PGTable object.

scm = cat["public"]
#-> SCHEMA "public"

tbl = scm["patient"]
#-> TABLE "patient"

The table owns column models, which are represented by PostgresCatalog.PGColumn objects.

foreach(println, tbl)
#=>
COLUMN "patient"."id" "int4" NOT NULL
COLUMN "patient"."mrn" "text" NOT NULL
COLUMN "patient"."sex" "patient_sex_enum" NOT NULL
COLUMN "patient"."mother_id" "int4" NULL
COLUMN "patient"."father_id" "int4" NULL
=#

Column properties can be discovered through model attributes.

col = tbl["sex"]
#-> COLUMN "patient"."sex" "patient_sex_enum" NOT NULL

col.name
#-> "sex"

col.type
#-> TYPE "patient_sex_enum"

col.type.labels
#-> ["male", "female", "other", "unknown"]

col.not_null
#-> true

col.default
#-> "'unknown'::patient_sex_enum"

The table also owns the models of its unique and foreign key constraints.

tbl.primary_key
#-> CONSTRAINT "patient"."patient_pk" PRIMARY KEY ("mrn")

tbl.primary_key.name
#-> "patient_pk"

foreach(println, tbl.primary_key.columns)
#-> COLUMN "patient"."mrn" "text" NOT NULL

foreach(println, tbl.unique_keys)
#=>
CONSTRAINT "patient"."patient_pk" PRIMARY KEY ("mrn")
CONSTRAINT "patient"."patient_uk" UNIQUE ("id")
=#

foreach(println, tbl.foreign_keys)
#=>
CONSTRAINT "patient"."patient_father_fk" FOREIGN KEY ("father_id") REFERENCES "patient" ("id")
CONSTRAINT "patient"."patient_mother_fk" FOREIGN KEY ("mother_id") REFERENCES "patient" ("id")
=#

API Reference

PostgresCatalog.introspectFunction
introspect(conn::LibPQ.Connection) :: PGCatalog

Introspects a Postgres database and generates a model of the database structure.

source
PostgresCatalog.PGSchemaType

Model of a database schema.

  • catalog: database that owns the schema;
  • name: name of the schema;
  • comment: comment on the schema;
  • type: collection of types owned by the schema;
  • tables: collection of tables owned by the schema.
source
PostgresCatalog.PGTypeType

Model of a type.

  • schema: schema that owns the type;
  • name: name of the type;
  • labels: vector of labels for an ENUM type; nothing otherwise;
  • comment: comment on the type;
  • columns: set of columns of this type.
source
PostgresCatalog.PGTableType

Model of a table.

  • schema: schema that owns the table;
  • name: name of the table;
  • comment: comment on the table;
  • columns: collection of table columns;
  • primary_key: primary key of the table, if any;
  • unique_keys: collection of unique keys defined on the table;
  • foreign_keys: collection of foreign keys defined on the table;
  • referring_foreign_keys: set of foreign keys that refer to this table.
source
PostgresCatalog.PGColumnType

Model of a column.

  • table: table that owns the column;
  • name: name of the column;
  • type: type of the column;
  • not_null: set if the column has NOT NULL constraint;
  • default: SQL expression that calculates the default column value; or nothing;
  • comment: comment on the column.
  • unique_keys: set of unique keys that include this column;
  • foreign_keys: set of foreign keys that include this column;
  • referring_foreign_keys: set of foreign keys that target this column.
source
PostgresCatalog.PGUniqueKeyType

Model of a unique key constraint.

  • table: table that owns the key;
  • name: name of the constraint;
  • columns: columns included in the key;
  • primary: set if this is the primary key;
  • comment: comment on the constraint.
source
PostgresCatalog.PGForeignKeyType

Model of a foreign key constraint.

  • table: table that owns the key;
  • name: name of the constraint;
  • columns: columns included in the key;
  • target_table: table targeted by the key;
  • target_columns: columns targeted by the key;
  • on_delete: ON DELETE action;
  • on_update: ON UPDATE action;
  • comment: comment on the constraint.
source