Skip to content

Latest commit

 

History

History
281 lines (219 loc) · 9.31 KB

README.md

File metadata and controls

281 lines (219 loc) · 9.31 KB
Raw

pg-info

Tymly Package npm (scoped) CircleCI codecov CodeFactor Dependabot badge Commitizen friendly JavaScript Style Guide license

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