Postgres

This module provides an abstraction over postgres called PostgresService along with utilities to integrate it with other Gruber modules.

There are methods like getPostgresMigrations, executePostgresMigration and postgresBootstrapMigration to help platforms like Deno and Node.js implement a Migrator.

There is also PostgresStore that implements the Store interface.

getPostgresMigrations

Query the postgres database to find migrations that have already been performed. Returning an array of PostgresMigrationRecord.

const sql // PostgresService
const records = await getPostgresMigrations(sql)

executePostgresMigration

Perform either the up or down postgres migration and record what happened. This will first start a transaction, so if anything goes wrong the whole operation is aborted. Within the transaction, it attempts the run the action (either up or down) as specified.

After the action is ran, it will follow up with updating the migration records. For an up action, it will create a new PostgresMigrationRecord and insert it into the database. For a down action, it will remove the corresponding PostgresMigrationRecord.

There is an edge case where it will not remove the record if running the postgresBootstrapMigration action, because that migration deletes the migration table itself so would be pointless.

const sql // PostgresService
const definition = definePostgresMigration(...)

await executePostgresMigration(definition, "up", sql)

PostgresMigrationRecord `type`

A record in a postgres database containing information about a migration that has been run.

const record = {
  name: '001-add-users-table.js',
  created: new Date()
}

postgresBootstrapMigration

This is a MigrationDefinition to bootstrap postgres migrations. It sets up the initial "migrations" table that all other migrations will be recorded in.

definePostgresMigration

A typed version of defineMigration that specalizes for a PostgresService. This is mostly useful to get a strongly typed sql parameter.

import { definePostgresMigration } from "gruber"

export default definePostgresMigration({
  async up(sql) {
    await sql.execute`
      CREATE TABLE users ...
    `
  },
  async down(sql) {
    await sql.execute`
      DROP TABLE users
    `
  }
})

PostgresService `type`

PostgresService is an abstraction around a connection to a running postgres database. Originally it was a shallow interface for postgres.js, but it is moving towards an agnostic layer that any SQL-based library could implement.

The idea is that platforms within Gruber implement a method that returns an object that conforms to this interface using a specific postgres library.

It might be interesting to think of this as an abstraction over any SQL-based server, but for now it is focussed on postgres.

const sql // PostgresService

// Run an SQL transaction and return the response
const result = await sql.transaction(async (trx) => {
  await trx.execute` INSERT INTO ... `
  return trx.execute` SELECT * FROM ... `
})

// Execute a statement
const users = await sql.execute` SELECT * FROM users WHERE id = ${5}`

// Prepare a value to be used in a query
const pets = await sql.execute`
  SELECT * FROM pets WHERE id IN ${sql.prepare([1, 2, 3, 4])}
`

// Write JSON to the database
await sql.execute`
  INSERT INTO bicycles (name, model)
  VALUES ("Big red", ${sql.json({ id: "xxx-yyy-zzz" })})
`

// Close the connection
await sql.dispose()

// Use Explicit Resource Management to automatically dispose the connection
function main() {
  using sql = getPostgresService(...)

  await sql.execute` SELECT * FROM ... `
}

dispose

Close the connection

await sql.dispose()

execute

Perform an SQL query against the database and return the results. Values are automatically escaped.

const users = await sql.execute`
  SELECT * FROM users WHERE id = ${5}
`

json

Prepare JSON to be serialized into a query

// Write JSON to the database
await sql.execute`
  INSERT INTO bicycles (name, model)
  VALUES ("Big red", ${sql.json({ id: "xxx-yyy-zzz", manufacturer: 'Cube' })})
`

prepare

Prepare a value to be used in an SQL query, for instance if you want to turn an array into a SQL set,

const pets = await sql.execute`
  SELECT * FROM pets WHERE id IN ${sql.prepare([1, 2, 3, 4])}
`

would become:

SELECT * FROM pets WHERE id IN (1,2,3,4)

transaction

Start a transaction with a BEGIN, then COMMIT if all goes well or ROLLBACK if not.

// Run an SQL transaction and return the response
const result = await sql.transaction(async (trx) => {
  await trx.execute` INSERT INTO ... `
  return trx.execute` SELECT * FROM ... `
})

debug

{
  "getPostgresMigrations": {
    "entrypoint": "postgres/mod.ts",
    "id": "getPostgresMigrations",
    "name": "getPostgresMigrations",
    "content": "Query the postgres database to find migrations that have already been performed.\nReturning an array of `PostgresMigrationRecord`.\n\n```js\nconst sql // PostgresService\nconst records = await getPostgresMigrations(sql)\n```",
    "tags": {
      "group": "Miscellaneous"
    },
    "members": {}
  },
  "executePostgresMigration": {
    "entrypoint": "postgres/mod.ts",
    "id": "executePostgresMigration",
    "name": "executePostgresMigration",
    "content": "Perform either the **up** or **down** postgres migration and record what happened.\nThis will first start a transaction, so if anything goes wrong the whole operation is aborted.\nWithin the transaction, it attempts the run the action (either **up** or **down**) as specified.\n\nAfter the action is ran, it will follow up with updating the migration records.\nFor an **up** action, it will create a new `PostgresMigrationRecord`\nand insert it into the database.\nFor a **down** action, it will remove the corresponding `PostgresMigrationRecord`.\n\n> There is an edge case where it will not remove the record if running the `postgresBootstrapMigration` action,\n> because that migration deletes the migration table itself so would be pointless.\n\n```js\nconst sql // PostgresService\nconst definition = definePostgresMigration(...)\n\nawait executePostgresMigration(definition, \"up\", sql)\n```",
    "tags": {
      "group": "Miscellaneous"
    },
    "members": {}
  },
  "PostgresMigrationRecord": {
    "entrypoint": "postgres/mod.ts",
    "id": "PostgresMigrationRecord",
    "name": "PostgresMigrationRecord",
    "content": "A record in a postgres database containing information about a migration that has been run.\n\n```js\nconst record = {\n  name: '001-add-users-table.js',\n  created: new Date()\n}\n```",
    "tags": {
      "group": "Miscellaneous",
      "type": "true"
    },
    "members": {}
  },
  "postgresBootstrapMigration": {
    "entrypoint": "postgres/mod.ts",
    "id": "postgresBootstrapMigration",
    "name": "postgresBootstrapMigration",
    "content": "This is a `MigrationDefinition` to bootstrap postgres migrations.\nIt sets up the initial \"migrations\" table that all other\nmigrations will be recorded in.",
    "tags": {
      "group": "Miscellaneous"
    },
    "members": {}
  },
  "definePostgresMigration": {
    "entrypoint": "postgres/mod.ts",
    "id": "definePostgresMigration",
    "name": "definePostgresMigration",
    "content": "A typed version of `defineMigration` that specalizes for a ` PostgresService`.\nThis is mostly useful to get a strongly typed `sql` parameter.\n\n```js\nimport { definePostgresMigration } from \"gruber\"\n\nexport default definePostgresMigration({\n  async up(sql) {\n    await sql.execute`\n      CREATE TABLE users ...\n    `\n  },\n  async down(sql) {\n    await sql.execute`\n      DROP TABLE users\n    `\n  }\n})\n```",
    "tags": {
      "group": "Miscellaneous"
    },
    "members": {}
  },
  "PostgresService": {
    "entrypoint": "postgres/mod.ts",
    "id": "PostgresService",
    "name": "PostgresService",
    "content": "`PostgresService` is an abstraction around a connection to a running postgres database.\nOriginally it was a shallow interface for [postgres.js](https://github.com/porsager/postgres),\nbut it is moving towards an agnostic layer that any SQL-based library could implement.\n\nThe idea is that platforms within Gruber implement a method that returns an object that conforms to this interface\nusing a specific postgres library.\n\nIt might be interesting to think of this as an abstraction over any SQL-based server, but for now it is focussed on postgres.\n\n```js\nconst sql // PostgresService\n\n// Run an SQL transaction and return the response\nconst result = await sql.transaction(async (trx) => {\n  await trx.execute` INSERT INTO ... `\n  return trx.execute` SELECT * FROM ... `\n})\n\n// Execute a statement\nconst users = await sql.execute` SELECT * FROM users WHERE id = ${5}`\n\n// Prepare a value to be used in a query\nconst pets = await sql.execute`\n  SELECT * FROM pets WHERE id IN ${sql.prepare([1, 2, 3, 4])}\n`\n\n// Write JSON to the database\nawait sql.execute`\n  INSERT INTO bicycles (name, model)\n  VALUES (\"Big red\", ${sql.json({ id: \"xxx-yyy-zzz\" })})\n`\n\n// Close the connection\nawait sql.dispose()\n\n// Use Explicit Resource Management to automatically dispose the connection\nfunction main() {\n  using sql = getPostgresService(...)\n\n  await sql.execute` SELECT * FROM ... `\n}\n```",
    "tags": {
      "group": "Miscellaneous",
      "type": "true"
    },
    "members": {
      "transaction": {
        "id": "PostgresService_transaction",
        "name": "transaction",
        "content": "Start a transaction with a `BEGIN`, then `COMMIT` if all goes well or `ROLLBACK` if not.\n\n```js\n// Run an SQL transaction and return the response\nconst result = await sql.transaction(async (trx) => {\n  await trx.execute` INSERT INTO ... `\n  return trx.execute` SELECT * FROM ... `\n})\n```",
        "tags": {
          "group": "Miscellaneous"
        },
        "members": {}
      },
      "execute": {
        "id": "PostgresService_execute",
        "name": "execute",
        "content": "Perform an SQL query against the database and return the results.\nValues are automatically escaped.\n\n```js\nconst users = await sql.execute`\n  SELECT * FROM users WHERE id = ${5}\n`",
        "tags": {
          "group": "Miscellaneous"
        },
        "members": {}
      },
      "prepare": {
        "id": "PostgresService_prepare",
        "name": "prepare",
        "content": "Prepare a value to be used in an SQL query,\nfor instance if you want to turn an array into a SQL set,\n\n```js\nconst pets = await sql.execute`\n  SELECT * FROM pets WHERE id IN ${sql.prepare([1, 2, 3, 4])}\n`\n```\n\nwould become:\n\n```sql\nSELECT * FROM pets WHERE id IN (1,2,3,4)\n```",
        "tags": {
          "group": "Miscellaneous"
        },
        "members": {}
      },
      "json": {
        "id": "PostgresService_json",
        "name": "json",
        "content": "Prepare JSON to be serialized into a query\n\n```js\n// Write JSON to the database\nawait sql.execute`\n  INSERT INTO bicycles (name, model)\n  VALUES (\"Big red\", ${sql.json({ id: \"xxx-yyy-zzz\", manufacturer: 'Cube' })})\n`\n```",
        "tags": {
          "group": "Miscellaneous"
        },
        "members": {}
      },
      "dispose": {
        "id": "PostgresService_dispose",
        "name": "dispose",
        "content": "Close the connection\n\n```js\nawait sql.dispose()\n```",
        "tags": {
          "group": "Miscellaneous"
        },
        "members": {}
      }
    }
  }
}