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:

Option	Notes
client	An already-connected pg client or pg pool instance to your database
schemas	An array of schema names you'd like information about

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

property	Type	Notes
generated	string	Timestamp of when pg-info interrogated the database
schemas	object	An object where the key refers to a schema name provided via the options, and the value is a schema object

schema object

Example

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

Properties

property	Type	Notes
comment	string	The database comment added for this schema, if available
schemaExistsInDatabase	boolean	Indicates if this schema is present in the database or not
tables	object	An object where the key refers to a table name within this schema, and the value is a table object
views	object	An object where the key refers to a view name within this schema, and the value is a view object

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

property	Type	Notes
comment	string	The database comment added for this table, if available
pkColumnNames	[string]	An array of column names that define this table's primary key
columns	object	An object where the key refers to a column name within this table, and the value is a column object
indexes	object	An object where the key refers to an index name defined for this table, and the value is an index object
triggers	object	An object where the key refers to a trigger name within this table, and the value is a trigger object
fkConstraints	object	An object where the key refers to a foreign-key constraint name defined for this table, and the value is a fkConstraint object

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

property	Type	Notes
columns	object	An object where the key refers to a column name within this view, and the value is a column object
triggers	object	An object where the key refers to a trigger name within this view, and the value is a trigger object
sql	string	The underlying SQL query of this view
comment	string	The database comment added for this view, if available

column object

Example

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

Properties

property	Type	Notes
array	boolean	Is the column an array?
comment	string	The database comment added for this comment, if available
columnDefault	string	The value used to default the value of this column
isNullable	string	Indicates if null values are allowed in this column (YES) or not (NO)
dataType	string	The PostgreSQL data type assigned to this column
characterMaximumLength	integer	The maximum length of a string stored in this column
numericScale	integer	For numeric columns, this refers to the number of digits permitted after the decimal point

index object

Example

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

Properties

property	Type	Notes
columns	[string]	An array that contains the column names of the table that are covered by this index
unique	boolean	Indicates whether this is a unique index or not
method	string	The index method used, one of btree, hash, gist or gin)

trigger object

Example

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

Properties

property	Type	Notes
eventManipulation	string	Event that fires the trigger (INSERT, UPDATE, or DELETE)
actionCondition	string	WHEN condition of the trigger, null if none (also null if the table is not owned by a currently enabled role)
actionStatement	string	Statement that is executed by the trigger (currently always EXECUTE PROCEDURE function(...))
actionOrientation	string	Identifies whether the trigger fires once for each processed row or once for each statement (ROW or STATEMENT)
actionTiming	string	Time at which the trigger fires (BEFORE, AFTER, or INSTEAD OF)

fkConstraint object

Example

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

Properties

property	Type	Notes
targetTable	string	The 'child' table that is related to this table - of the form [schemaName].[tableName]
sourceColumns	[string]	An array of foreign-key column names on this table
targetColumns	[string]	And this is an array of column names found the target table (often relating to its primary key)
updateAction	string	Identifies the update action, either NO ACTION, RESTRICT, CASCADE, SET NULL or SET DEFAULT
deleteAction	string	Identifies the delete action, either NO ACTION, RESTRICT, CASCADE, SET NULL or SET DEFAULT
matchType	string	Identifies the match type, either FULL, PARTIAL, or SIMPLE

• 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