sparebytes/pg-structure

Get PostgreSQL database structure as a detailed JS Object.

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

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:

• MkDocs using a theme provided by Read the Docs.
• Markdown is generated by jsdoc-to-markdown

Contributions

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