Skip to content
Get PostgreSQL database structure as a detailed JS Object.
Branch: master
Clone or download
Latest commit a0e1d39 Feb 20, 2019
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.idea Updated: pg module and fixed depreciation warnings. Nov 16, 2017
bin v3 Jan 25, 2016
docs fix doc Nov 28, 2018
lib lint Nov 28, 2018
site Doc update. Feb 20, 2019
sql delete trashed comments Feb 20, 2019
test fix cross schema foreign key references. fixes #21 Feb 20, 2019
.editorconfig Eslint updated to previous version. May 30, 2017
.eslintrc Eslint updated to previous version. May 30, 2017
.gitattributes Added: Schema comment. May 30, 2017
.gitignore
LICENSE v2 initial Nov 15, 2015
README.md fix doc Nov 28, 2018
index.js SQL Typo Mar 12, 2017
mkdocs.yml Doc update. Feb 20, 2019
package-lock.json 4.2.2 Feb 20, 2019
package.json 4.2.2 Feb 20, 2019

README.md

Documentation

See full documentation at http://www.pg-structure.com

Welcome to pg-structure

pg-structure is a Node.js library to get structure of a PostgreSQL database automatically as a detailed object.

Tested
Every part of the library is tested.
Documented
Everything is documented, no hidden features.
Utilitarian
Beyond database objects (i.e. many to many relation, description data).

pg-structure examines given PostgreSQL database by reverse engineering and lets you easily code, analyze, operate on PostgreSQL database structure by providing details about DB, Schema, Table, Column, Constraint, Relation and Index.

Created object can be used to auto generate documentation or ORM models from database. It is much easier to work with JS object than working manually with database.

Example

var pgStructure = require('pg-structure');
       
pgStructure({database: 'db', user: 'user', password: 'password'}, ['public', 'other_schema'])
    .then((db) => { console.log( db.get('public.account').columns.get('is_active').type ); })
    .catch(err => console.log(err.stack));

Detailed Example

var pgStructure = require('pg-structure');

pgStructure({database: 'db', user: 'user', password: 'password', host: 'localhost', port: 5432}, ['public', 'other_schema'])
    .then((db) => {
        // Basic
        var tables = db.schemas.get('public').tables;  // Map of Table objects.

        // List of table names
        for (let table of tables.values()) {
            console.log(table.name);
        }

        // Long chain example for:
        // public schema -> cart table -> contact_id column -> foreign key constraints of contact_id.
        var constraints = db.get('public.cart.contact_id').foreignKeyConstraints;
        var sameName = db.schemas.get('public').tables.get('cart').columns.get('contact_id').foreignKeyConstraints;

        // Many to many relation. Returns cart_line_item for cart --< cart_line_item >-- product
        var joinTable = [...db.get('public.cart').m2mRelations.values()][0].joinTable;    // See JS Map  on https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/Map
    })
    .catch(err => console.log(err.stack));

Load & Save Example

You can save reverse engineered database for later to load. If you use .zip extension, pg-structure automatically compresses file as zip file.

var pgStructure = require('pg-structure');

pgStructure({database: 'db', user: 'user', password: 'password', host: 'localhost', port: 5432}, ['public', 'other_schema'])
    .then(db => pgStructure.save('./db.zip', db))
    .catch(err => console.log(err.stack));

... Later, you can load pg-structure. Loading is 10 times faster than reverse engineering database.

var pgStructure = require('pg-structure');

pgStructure.load('./db.zip')
    .then(db => console.log(db.schemas.get('public').name))
    .catch(err => console.log(err.stack));

Caveat: pgStructure cannot load files saved by incompatible pg-structure module versions and returns undefined. In this case you should fetch structure from database and create a new save file.

Features

  • Fully tested
  • Fully documented with JSDOC and HTML
  • Supports load, save, serialize, deserialize, toString, parse.
  • All PostgreSQL data types including array, JSON and HSTore
  • Support composite keys (Multiple field keys)
  • Schema support
  • Constraints (Primary Key, Foreign Key, Unique).
  • Supports multi-column constraints.
  • Identifies one to many (hasMany) relationships.
  • Identifies reverse of one to many (belongsTo) relationships
  • Identifies all possible many to many (belongs to many & has many through) relationships
  • Objects can be accessed by name or by order. (Uses Map to save order and allow named access.)
  • Objects can be iterated via callbacks.
  • Allows to store and extract JSON data from Database objects. (See Description Data in concepts.)
  • Very detailed column meta data:
    • Allow null
    • Description
    • Auto Increment
    • onUpdate
    • onDelete
    • etc. (Full details can be found in Column doc)
  • Support for:
    • DB
    • Schema
    • Table
    • Column
    • Constraint
    • Index
    • Relation
    • Custom Data Type

Where to Start?

First have look at concepts to understand a few key points. You may want to read examples to see how pg-structure can be used. To start coding read main pg-structure module's documentation. During development API references helps you.

Special Thanks

Documentation is auto generated thanks to:

Contributions

  • For contribution please send pull requests with tests on GitHub.
  • Send bugs and feature requests to GitHub Issues.

Thanks for Contributions

PhilWaldmann ShaunParsons cyberinferno viniciuspinto

You can’t perform that action at this time.