@well-known-components/pg-component

pg-component

A port used to query pg.

API

Create

To create the component you have the option of:

• Supplying any PoolConfig options you want. This are related 1:1 with node-postgres implementation, which in turn are all variables Postgres supports
• Supplying any RunnerOption. This are related 1:1 with node-pg-migrate implementation and will be used for migrations
• Supplying a Metrics component (any IMetricsComponent). This component will enable additional functionality for collecting metrics on querys.

// src/components.ts

await createPgComponent({ config, logs, metrics: metrics | undefined } /* optional config here */)

// A possible optional'd look like:
const { config } = createConfigComponent()
const pgConfig = {
  pool: {
    user: "admin",
    password: "admin",
    database: "really_secure",
  },
  migration: {
    databaseUrl: await config.requireString("PG_COMPONENT_PSQL_CONNECTION_STRING"),
    dir: path.resolve(__dirname, "migrations"),
    migrationsTable: "pgmigrations",
    ignorePattern: ".*\\.map", // avoid sourcemaps
    direction: "up",
  },
}

Start

You'd normally won't have to call this function, as that is taken care by the Lifecycle.run method. But the method will:

• Try to run migrations ONLY IF you supplied migration options when creating the component
• Start an internal pool of connections with the database

Query

You have two options when querying the database, querying directly and streaming a query.

Direct query

the API looks like this

  query<T>(sql: string): Promise<IDatabase.IQueryResult<T>>
  // If it has been built with the metrics component this contract is also exposed:
  query<T>(sql: SQLStatement, durationQueryNameLabel?: string): Promise<IDatabase.IQueryResult<T>>

Using a template string is recommended, as it mitigates possible SQL injection problems, by using SQL placeholders in the final query when interpolating values

const id = getIdFromUnreliableSource()

pg.query<TableType>(SQL`SELECT * FROM table WHERE id = ${id}`) // this results in ['SELECT * FROM table WHERE id = $1', id]

if metrics component has been provided, suppliying a durationQueryNameLabel will trigger a metrics increment with that name.

Streaming a query

This is only useful if you need to query a large amount of columns and expect to have a hit in performance. The API is similar

  streamQuery<T = any>(sql: SQLStatement, config?: { batchSize?: number }): AsyncGenerator<T>

but it returns a generator. To use it:

for await (const row of database.streamQuery<TableType>(query, { batchSize: 10000 })) {
  yield row.some_value
}

Migrations

If you want to have migrations for your database, this component uses node-pg-migrate as a proxy. For this you'll need to:

• Supply the necessary runner options on your component creation. You'd normaly want to use 'up' as a migration direction
• For creating new migrations or any other type of interaction you can use the node-pg-migrate binary. To use we recommend:

package.json

"scripts": {
  "migrate": "node-pg-migrate --database-url-var PG_COMPONENT_PSQL_CONNECTION_STRING --envPath .env -j ts --tsconfig tsconfig.json -m ./src/migrations"
}

use

$ npm run migrate create create-your-table

Configuration

It supports the following ENV variables:

First everything related with connecting to Postgres:

• PG_COMPONENT_PSQL_CONNECTION_STRING
• PG_COMPONENT_PSQL_PORT
• PG_COMPONENT_PSQL_HOST
• PG_COMPONENT_PSQL_DATABASE
• PG_COMPONENT_PSQL_USER
• PG_COMPONENT_PSQL_PASSWORD

You'll probably use either the CONNECTION_STRING or the other params and not both.

Then the variables related to Postgres's query timeouts:

• PG_COMPONENT_IDLE_TIMEOUT
• PG_COMPONENT_QUERY_TIMEOUT
• PG_COMPONENT_STREAM_QUERY_TIMEOUT

Then the variables related to this component's implementation

• PG_COMPONENT_GRACE_PERIODS: how many retries the component we'll give for a gracefull stop of the database. It'll wait 200ms per each grace period. Defaults to 10