Skip to main content

​
Overview

PostgreSQL dialect that uses the pg library (node-postgres). This is the recommended way to connect Kysely to PostgreSQL databases. Source: src/dialect/postgres/postgres-dialect.ts:43

​
Installation

Install the required peer dependencies: 
npm install pg
npm install --save-dev @types/pg

​
Basic Usage

import { Kysely, PostgresDialect } from 'kysely'
import { Pool } from 'pg'

const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({
      host: 'localhost',
      database: 'mydatabase',
      user: 'myuser',
      password: 'mypassword',
      port: 5432,
      max: 10,
    })
  })
})

​
Configuration

​
PostgresDialectConfig

Source: src/dialect/postgres/postgres-dialect-config.ts:6
​
pool
PostgresPool | (() => Promise<PostgresPool>)
required
A postgres Pool instance or a function that returns one.If a function is provided, it’s called once when the first query is executed. This allows for lazy initialization of the connection pool.See node-postgres Pool documentation for available pool options.
​
cursor
PostgresCursorConstructor
Optional cursor constructor for streaming large result sets.Requires the pg-cursor package.
import { PostgresDialect } from 'kysely'
import { Pool } from 'pg'
import Cursor from 'pg-cursor'

new PostgresDialect({
  cursor: Cursor,
  pool: new Pool({ /* ... */ })
})
​
onCreateConnection
(connection: DatabaseConnection) => Promise<void>
Called once for each created connection. Useful for setting up connection-level configuration.
new PostgresDialect({
  pool: new Pool({ /* ... */ }),
  onCreateConnection: async (connection) => {
    await connection.executeQuery(
      CompiledQuery.raw('SET timezone = "UTC"')
    )
  }
})
​
onReserveConnection
(connection: DatabaseConnection) => Promise<void>
Called every time a connection is acquired from the pool. Use this for per-query setup that needs to run every time.
new PostgresDialect({
  pool: new Pool({ /* ... */ }),
  onReserveConnection: async (connection) => {
    // Run on every connection acquisition
    await connection.executeQuery(
      CompiledQuery.raw('SET search_path = public')
    )
  }
})

​
Connection Pooling

The PostgreSQL dialect uses the pg library’s built-in connection pooling. Here are some recommended pool settings: 
import { Pool } from 'pg'

const pool = new Pool({
  // Connection settings
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'user',
  password: 'password',

  // Pool settings
  max: 10, // Maximum number of connections
  min: 2, // Minimum number of connections
  idleTimeoutMillis: 30000, // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Return error after 2s if connection cannot be established
})

​
Streaming with Cursors

For large result sets, you can use cursors to stream results:
1

Install pg-cursor

npm install pg-cursor
npm install --save-dev @types/pg-cursor
2

Configure the dialect

import Cursor from 'pg-cursor'

const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    cursor: Cursor,
    pool: new Pool({ /* ... */ })
  })
})
3

Use streaming queries

const stream = db
  .selectFrom('person')
  .selectAll()
  .stream()

for await (const row of stream) {
  console.log(row)
}

​
SSL/TLS Configuration

To connect using SSL: 
import { Pool } from 'pg'
import fs from 'fs'

const pool = new Pool({
  host: 'localhost',
  database: 'mydb',
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync('/path/to/ca-cert.pem').toString(),
    key: fs.readFileSync('/path/to/client-key.pem').toString(),
    cert: fs.readFileSync('/path/to/client-cert.pem').toString(),
  }
})

​
Environment Variables

The pg library automatically reads these environment variables:
  • PGHOST - Database host
  • PGPORT - Database port
  • PGDATABASE - Database name
  • PGUSER - Database user
  • PGPASSWORD - Database password
import { Pool } from 'pg'

// Uses environment variables
const pool = new Pool()

​
PostgresPool

Source: src/dialect/postgres/postgres-dialect-config.ts:52 The subset of the pg Pool interface that Kysely requires: 
interface PostgresPool {
  connect(): Promise<PostgresPoolClient>
  end(): Promise<void>
}

​
PostgresPoolClient

Source: src/dialect/postgres/postgres-dialect-config.ts:57 
interface PostgresPoolClient {
  query<R>(sql: string, parameters: ReadonlyArray<unknown>): Promise<PostgresQueryResult<R>>
  query<R>(cursor: PostgresCursor<R>): PostgresCursor<R>
  release(): void
}

Build docs developers (and LLMs) love

Get started for freeTalk to us