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.introspect
— Functionintrospect(conn::LibPQ.Connection) :: PGCatalog
Introspects a Postgres database and generates a model of the database structure.
PostgresCatalog.PGCatalog
— TypeModel of a Postgres database.
name
: name of the database;schemas
: collection of schemas owned by the database.
PostgresCatalog.PGSchema
— TypeModel 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.
PostgresCatalog.PGType
— TypeModel of a type.
schema
: schema that owns the type;name
: name of the type;labels
: vector of labels for anENUM
type;nothing
otherwise;comment
: comment on the type;columns
: set of columns of this type.
PostgresCatalog.PGTable
— TypeModel 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.
PostgresCatalog.PGColumn
— TypeModel 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 hasNOT NULL
constraint;default
: SQL expression that calculates the default column value; ornothing
;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.
PostgresCatalog.PGUniqueKey
— TypeModel 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.
PostgresCatalog.PGForeignKey
— TypeModel 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.