Extract Schema from Postgres Database
This will read various metadata from your postgres database and return a js object. This module is being used by Kanel to generate Typescript types and Schemalint to provide linting of database schemas.
You hand it an initialized knex instance and the name of the schema you want to read.
Installation
npm i extract-pg-schema knex pg
Usage
const knex = require('knex');
const { extractSchema } = require('extract-pg-schema');
async function run() {
const knexConfig = {
client: 'pg',
connection: {
host: 'localhost',
database: 'postgres',
user: 'postgres',
password: 'postgres'
}
};
const db = knex(knexConfig);
const { tables, views, types } = await extractSchema('public', db);
console.log('Tables:');
console.log(tables);
console.log('Views:');
console.log(views);
console.log('Types:');
console.log(types);
}
run();
Reference
This module exposes one function:
async extractSchema(schemaName, knexInstance)
It returns an object that has three properties: tables
, views
and types
. All arrays.
Table
The tables
array consists of objects that correspond to the tables in the schema. It could look like this:
{
"name": "member",
"comment": "Members of an organization",
"tags": {},
"columns": [
{
"name": "id",
"tags": {},
"indices": [
{
"name": "person_pkey",
"isPrimary": true
}
],
"maxLength": null,
"nullable": false,
"defaultValue": "nextval('person_id_seq'::regclass)",
"isPrimary": true,
"type": "int4",
"comment": null,
"rawInfo": {...},
},
{
"name": "createdAt",
"tags": {},
"indices": [],
"maxLength": null,
"nullable": false,
"defaultValue": null,
"type": "timestamptz",
"comment": null
},
{
"name": "displayName",
"tags": {},
"indices": [],
"maxLength": null,
"nullable": false,
"defaultValue": null,
"type": "text",
"comment": "Name that will be displayed in the UI",
"rawInfo": {...},
},
{
"name": "organizationId",
"tags": {},
"parent": "organization.id",
"indices": [
{
"name": "member_organizationId_index",
"isPrimary": false
}
],
"maxLength": null,
"nullable": false,
"defaultValue": null,
"type": "int4",
"comment": null,
"rawInfo": {...},
}
]
}
Basically, a table has four properties: name
which is the name of the table, comment
which is the postgres table comment, tags
which is a map of tags parsed out of the comment, and columns
which represents the columns.
You can set the comment for a table with the following SQL:
COMMENT ON TABLE "member" IS 'Members of an organization';
The tags feature uses the @-symbol, so you if you write a comment like this: 'Members of an organization @cached @alias:person'
, you will get
- a
comment
with the value'Members of an organization'
, and - a
tags
value of{ cached: true, alias: 'person' }
You can use tags for any sort of metadata that you want to store for further processing.
Column
The columns
array on a table
has the following properties:
name
which is the column name,parent
which is the referenced table and column if the column has a foreign keyindices
, an array describing the indices that apply. These have two properties:name
andisPrimary
.maxLength
, which specifies the max string length the column has if that applies.nullable
which indicates if the column is nullable,defaultValue
which states the possible default value for the column,type
which specifies the datatype of the columncomment
which specifies the column comment.tags
which is a map of tags parsed from the column commentrawInfo
which contains all the column information that is extracted from postgres.
You can set the comment for a column with the following SQL:
COMMENT ON COLUMN "member"."displayName" IS 'Name that will be displayed in the UI';
View
Views have exactly the same shape as tables.
Type
The second property in the result is the types
array. This contains the user-specified types, currently only postgres enum types.
A type could look like this:
{
"type": "enum",
"name": "AccountState",
"comment": "Determines the state of an account",
"values": [
"active",
"pending",
"closed"
]
}
This would be the output if you had created the type with the following:
CREATE TYPE "AccountState" AS ENUM ('active', 'pending', 'closed');
COMMENT ON TYPE "AccountState" IS 'Determines the state of an account';
For an example of a generated object, take a look at dvdrental.json file which is generated from the sample Database from www.postgresqltutorial.com.