@wmfs/pg-info v1.20.1

pg-info

Grabs metadata from a PostgreSQL database

Install

$ npm install pg-info --save

Usage

const pg = require('pg')
const pgInfo = require('@wmfs/pg-info')

// Make a new Postgres client
const client = new pg.Client('postgres://postgres:postgres@localhost:5432/my_test_db')
client.connect()

const info = await pgInfo({
    client: client,
    schemas: [
      'pginfo_people_test',
      'pginfo_planets_test',
      'pginfo_not_exists'
    ]    
})

API

pgInfo(options, callback)

Options:

Output

Taking a look at the tests is a good place to get a feel for what pg-info will produce.

In summary, the structure of the output is:

• info object

  • schema object(s)
    • table object(s)
      • column object(s)
      • index object(s)
      • trigger object(s)
      • fkConstraint object(s)
    • view object(s)
      • column object(s)
      • trigger object(s)

info object

Example

{
  generated: '2017-05-21T21:53:42.594Z',
  schemas: {} // Keys are schema names
}

Properties

schema object

Example

{
  schemaExistsInDatabase: true,
  comment: 'Simple schema created to support testing of the pg-info package!',
  tables: {...} // Keys are table names
}

Properties

table object

Example

{
  comment: 'For storing a list of planets',
  pkColumnNames: [
    'planet_name'
  ],
  columns: {...}, // Keys are column names
  triggers: {...}, // Keys are trigger names
  indexes: {...}, // Keys are index names
  fkConstraints: {...} // Keys are foreign key constraint names
}

Properties

view object

Example

{
  columns: {...}, // Keys are column names
  triggers: {...}, // Keys are trigger names
  comment: 'View of just those planets in our solar system',
  sql: 'SELECT * FROM my_schema.planets WHERE solar_system = true;'
}

Properties

column object

Example

{
  array: false,
  columnDefault: null,
  isNullable: 'YES',
  dataType: 'integer',
  characterMaximumLength: null,
  numericScale: 0,
  comment: 'Age in years'
}

Properties

index object

Example

{
  columns: [
    [
      'moons_id'
    ]
  ],
  unique: false,
  method: 'btree'
}

Properties

trigger object

Example

{
  triggers: {
    someInsertTriggerName: {
      eventManipulation: 'INSERT',      
      actionCondition: null,
      actionStatement: 'EXECUTE PROCEDURE append_inserted_craters_row()',
      actionOrientation: 'STATEMENT',
      actionTiming: 'BEFORE'
    }
  }
}

Properties

fkConstraint object

Example

{
  targetTable: 'pginfo_planets_test.moons',
  sourceColumns: [
    'moons_id'
  ],
  targetColumns: [
    'id'
  ],
  updateAction: 'NO_ACTION',
  deleteAction: 'CASCADE',
  matchType: 'SIMPLE'
}

Properties

• Note the order of the columns provided in sourceColumns and targetColumns arrays correlate with each other.

Testing

Before running these tests, you'll need a test PostgreSQL database available and set a PG_CONNECTION_STRING environment variable to point to it, for example:

PG_CONNECTION_STRING=postgres://postgres:postgres@localhost:5432/my_test_db

$ npm test

License

MIT