### Example Usage

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

Demonstrates how to create a database instance and use it to fetch and insert data.

```APIDOC
### Usage

Create your `sql` database instance
```js
// db.js
import postgres from 'https://deno.land/x/postgresjs/mod.js'

const sql = postgres({ /* options */ }) // will use psql environment variables

export default sql
```

Simply import for use elsewhere
```js
// users.js
import sql from './db.js'

async function getUsersOver(age) {
  const users = await sql`
    select
      name,
      age
    from users
    where age > ${ age }
  `
  // users = Result [{ name: "Walter", age: 80 }, { name: 'Murray', age: 68 }, ...]
  return users
}


async function insertUser({ name, age }) {
  const users = await sql`
    insert into users
      (name, age)
    values
      (${ name }, ${ age })
    returning name, age
  `
  // users = Result [{ name: "Murray", age: 68 }]
  return users
}
```

#### ESM dynamic imports

The library can be used with ESM dynamic imports as well as shown here.

```js
const { default: postgres } = await import('postgres')
```
```

--------------------------------

### Start Transaction with Callback

Source: https://github.com/porsager/postgres/blob/master/README.md

Use `sql.begin` to start a transaction. It reserves a connection and provides a scoped `sql` instance. `ROLLBACK` is automatically called on failure.

```javascript
const [user, account] = await sql.begin(async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
    returning *
  `

  const [account] = await sql`
    insert into accounts (
      user_id
    ) values (
      ${ user.user_id }
    )
    returning *
  `

  return [user, account]
})
```

--------------------------------

### Install Postgres.js

Source: https://github.com/porsager/postgres/blob/master/README.md

Install the postgres npm package using npm. This is the first step to using the library in your Node.js project.

```bash
$ npm install postgres
```

--------------------------------

### Transaction Management with sql.begin

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

Demonstrates how to start a new transaction using `sql.begin`. It handles automatic COMMIT on success and ROLLBACK on failure, providing a scoped `sql` instance for transaction-specific operations.

```APIDOC
## BEGIN / COMMIT `await sql.begin([options = ''], fn) -> fn()`

### Description
Use `sql.begin` to start a new transaction. Postgres.js will reserve a connection for the transaction and supply a scoped `sql` instance for all transaction uses in the callback function. `sql.begin` will resolve with the returned value from the callback function.

`BEGIN` is automatically sent with the optional options, and if anything fails `ROLLBACK` will be called so the connection can be released and execution can continue.

### Request Example
```js
const [user, account] = await sql.begin(async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
    returning *
  `

  const [account] = await sql`
    insert into accounts (
      user_id
    ) values (
      ${ user.user_id }
    )
    returning *
  `

  return [user, account]
})
```

### Pipelining Requests
It's also possible to pipeline the requests in a transaction if needed by returning an array with queries from the callback function like this:

### Request Example
```js
const result = await sql.begin(sql => [
  sql`update ...`,
  sql`update ...`,
  sql`insert ...`
])
```
```

--------------------------------

### Insert Data and Return All Columns

Source: https://github.com/porsager/postgres/blob/master/README.md

Insert a new record into the 'users' table and use 'returning *' to get all columns of the inserted row. This demonstrates basic insertion and returning all fields.

```javascript
const xs = await sql`
  insert into users (
    name, age
  ) values (
    'Murray', 68
  )

  returning *
`

// xs = [{ user_id: 1, name: 'Murray', age: 68 }]
```

--------------------------------

### Query Descriptions

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

Use `.describe` to get information about the query process, including the final generated query string, without executing the query.

```APIDOC
## Query Descriptions

Rather than executing a given query, `.describe` will return information utilized in the query process. This information can include the query identifier, column types, etc.

This is useful for debugging and analyzing your Postgres queries. Furthermore, **`.describe` will give you access to the final generated query string that would be executed.**
```

--------------------------------

### Execute INSERT with RETURNING Clause

Source: https://context7.com/porsager/postgres/llms.txt

Inserts a new row into the 'users' table and returns the newly inserted row using the RETURNING clause. Requires prior connection setup.

```javascript
import postgres from 'postgres'
const sql = postgres()

// Insert with returning
const [newUser] = await sql`
  INSERT INTO users (name, age)
  VALUES (${'Charlie'}, ${35})
  RETURNING *
`
// newUser = { id: 3, name: 'Charlie', age: 35 }
```

--------------------------------

### Query descriptions

Source: https://github.com/porsager/postgres/blob/master/README.md

Use `.describe()` to get query information like identifiers and column types without executing the query. This is useful for debugging and analysis.

```javascript
await sql``.describe()
```

--------------------------------

### Execute UPDATE Query with RETURNING

Source: https://context7.com/porsager/postgres/llms.txt

Updates rows in the 'users' table and returns the affected rows using the RETURNING clause. Logs the count of affected rows. Requires prior connection setup.

```javascript
import postgres from 'postgres'
const sql = postgres()

// Update
const updated = await sql`
  UPDATE users SET age = ${31} WHERE name = ${'Alice'}
  RETURNING *
`
console.log(updated.count) // Number of affected rows
```

--------------------------------

### Realtime Subscriptions API

Source: https://context7.com/porsager/postgres/llms.txt

Subscribe to database changes using PostgreSQL logical replication. Requires `wal_level = logical` in PostgreSQL configuration and appropriate publication setup.

```APIDOC
## Realtime Subscriptions

Subscribe to database changes using PostgreSQL logical replication. Requires `wal_level = logical` in PostgreSQL configuration and appropriate publication setup.

### Method

`sql.subscribe(operations, callback, onReady?, onError?)
`

### Parameters

#### Path Parameters

- **operations** (string or array) - Required - Specifies which changes to subscribe to. Can be a single operation ('insert', 'update', 'delete'), a comma-separated string of operations, or a wildcard '*'. Can also be combined with table names (e.g., 'insert:orders', '*:users').
- **callback** (function) - Required - A function to be called when a change occurs. It receives the row data and an info object.
- **onReady** (function) - Optional - A callback function called when the subscription is established and ready.
- **onError** (function) - Optional - A callback function called when a subscription error occurs.

### Request Example

```javascript
// Subscribe to all changes
const { unsubscribe } = await sql.subscribe('*', (row, info) => {
  console.log(info.command, info.relation.table, row)
})

// Unsubscribe when done
unsubscribe()
```

### Request Example

```javascript
// Subscribe to specific operations
await sql.subscribe('insert', (row, info) => {
  console.log('Inserted into', info.relation.table, ':', row)
})

await sql.subscribe('update', (row, info) => {
  console.log('Updated:', row, 'Old values:', info.old)
})

await sql.subscribe('delete', (row, info) => {
  console.log('Deleted from', info.relation.table, ':', row)
})
```

### Request Example

```javascript
// Subscribe to specific table
await sql.subscribe('*:users', (row, info) => {
  console.log('Users table changed:', info.command, row)
})
```

### Request Example

```javascript
// Subscribe to specific table and operation
await sql.subscribe('insert:orders', (row, info) => {
  console.log('New order:', row)
})
```

### Request Example

```javascript
// Subscribe to specific row by primary key
await sql.subscribe('update:users=123', (row, info) => {
  console.log('User 123 updated:', row)
})
```

### Request Example

```javascript
// With reconnection callback
await sql.subscribe(
  'insert:events',
  (row, info) => handleEvent(row),
  () => console.log('Subscribed and ready'),
  (err) => console.error('Subscription error:', err)
)
```
```

--------------------------------

### Execute Simple SELECT Query

Source: https://context7.com/porsager/postgres/llms.txt

Performs a basic SELECT query using a tagged template literal. Results are returned as an array of row objects. Requires prior connection setup.

```javascript
import postgres from 'postgres'
const sql = postgres()

// Simple select
const users = await sql`SELECT * FROM users`
// users = [{ id: 1, name: 'Alice', age: 30 }, { id: 2, name: 'Bob', age: 25 }]
```

--------------------------------

### Initialize Postgres.js Instance

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

Create a database instance using default environment variables or custom options. This is the first step to interacting with your PostgreSQL database.

```javascript
// db.js
import postgres from 'https://deno.land/x/postgresjs/mod.js'

const sql = postgres({ /* options */ }) // will use psql environment variables

export default sql
```

--------------------------------

### Cloudflare Workers Integration

Source: https://github.com/porsager/postgres/blob/master/README.md

Example of using Postgres.js with Cloudflare Hyperdrive for connection pooling.

```ts
// Requires Postgres.js 3.4.0 or later
import postgres from 'postgres'

interface Env {
    HYPERDRIVE: Hyperdrive;
}

export default async fetch(req: Request, env: Env, ctx: ExecutionContext) {
    // The Postgres.js library accepts a connection string directly
    const sql = postgres(env.HYPERDRIVE.connectionString)
    const results = await sql`SELECT * FROM users LIMIT 10`
    return Response.json(results)
}
```

--------------------------------

### Execute DELETE Query

Source: https://context7.com/porsager/postgres/llms.txt

Deletes rows from the 'users' table based on a condition. Requires prior connection setup.

```javascript
import postgres from 'postgres'
const sql = postgres()

// Delete
await sql`DELETE FROM users WHERE age < ${18}`
```

--------------------------------

### POST /postgres (Client Initialization)

Source: https://github.com/porsager/postgres/blob/master/README.md

Initializes a new Postgres client connection with various configuration options for host, authentication, and connection pooling.

```APIDOC
## POST /postgres

### Description
Initializes the Postgres.js client with a connection string and an optional configuration object.

### Parameters
#### Request Body
- **url** (string) - Required - Postgres connection string (e.g., postgres://user:pass@host:port/db)
- **options** (object) - Optional - Configuration object containing:
  - **host** (string) - Postgres IP or domain
  - **port** (number) - Postgres port
  - **database** (string) - Database name
  - **username** (string) - Database user
  - **password** (string|function) - Password or async function returning a token
  - **ssl** (boolean|object) - SSL configuration
  - **max** (number) - Max number of connections
  - **max_lifetime** (number) - Max lifetime in seconds
  - **idle_timeout** (number) - Idle connection timeout in seconds
  - **connect_timeout** (number) - Connect timeout in seconds
  - **target_session_attrs** (string) - Connection target (e.g., 'primary')

### Request Example
const sql = postgres('postgres://username:password@host:port/database', {
  max: 10,
  ssl: false
})
```

--------------------------------

### All Postgres Connection Options

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

Provides a comprehensive list of all available configuration options for establishing a Postgres.js connection. Includes settings for host, port, authentication, connection pooling, and data transformation.

```javascript
const sql = postgres('postgres://username:password@host:port/database', {
  host                 : '',            // Postgres ip address[es] or domain name[s]
  port                 : 5432,          // Postgres server port[s]
  path                 : '',            // unix socket path (usually '/tmp')
  database             : '',            // Name of database to connect to
  username             : '',            // Username of database user
  password             : '',            // Password of database user
  ssl                  : false,         // true, prefer, require, tls.connect options
  sslnegotiation       : null,          // direct
  max                  : 10,            // Max number of connections
  max_lifetime         : null,          // Max lifetime in seconds (more info below)
  idle_timeout         : 0,             // Idle connection timeout in seconds
  connect_timeout      : 30,            // Connect timeout in seconds
  prepare              : true,          // Automatic creation of prepared statements
  types                : [],            // Array of custom types, see more below
  onnotice             : fn,            // Default console.log, set false to silence NOTICE
  onparameter          : fn,            // (key, value) when server param change
  debug                : fn,            // Is called with (connection, query, params, types)
  socket               : fn,            // fn returning custom socket to use
  transform            : {
    undefined          : undefined,     // Transforms undefined values (eg. to null)
    column             : fn,            // Transforms incoming column names
    value              : fn,            // Transforms incoming row values
    row                : fn             // Transforms entire rows
  },
  connection           : {
    application_name   : 'postgres.js', // Default application_name
    ...                                 // Other connection parameters, see https://www.postgresql.org/docs/current/runtime-config-client.html
  },
  target_session_attrs : null,          // Use 'read-write' with multiple hosts to
                                        // ensure only connecting to primary
  fetch_types          : true           // Automatically fetches types on connect
                                        // on initial connection.
})
```

--------------------------------

### Connect using environment variables

Source: https://github.com/porsager/postgres/blob/master/README.md

Initialize the client without options to automatically use standard PostgreSQL environment variables.

```js
const sql = postgres()
```

--------------------------------

### Connect to PostgreSQL using Connection String

Source: https://context7.com/porsager/postgres/llms.txt

Establishes a database connection using a connection string URL. Connections are created lazily.

```javascript
import postgres from 'postgres'

// Using connection string
const sql = postgres('postgres://username:password@localhost:5432/mydb')
```

--------------------------------

### Dynamic Single Column Selection

Source: https://context7.com/porsager/postgres/llms.txt

Safely selects a single dynamic column from a table using the `sql()` helper function. Requires prior connection setup.

```javascript
import postgres from 'postgres'
const sql = postgres()

// Dynamic column with single identifier
const column = 'email'
const emails = await sql`
  SELECT ${sql(column)} FROM users
`
// Executes: SELECT "email" FROM users
```

--------------------------------

### Dynamic Column Selection

Source: https://context7.com/porsager/postgres/llms.txt

Safely selects dynamic columns from a table using the `sql()` helper function to prevent SQL injection. Requires prior connection setup.

```javascript
import postgres from 'postgres'
const sql = postgres()

// Dynamic column selection
const columns = ['name', 'age', 'email']
const users = await sql`
  SELECT ${sql(columns)} FROM users
`
// Executes: SELECT "name", "age", "email" FROM users
```

--------------------------------

### Configure All Postgres Options

Source: https://github.com/porsager/postgres/blob/master/README.md

Use this configuration object to set all available options for connecting to a PostgreSQL database. This includes host, port, database credentials, connection pooling, and transformation settings.

```javascript
const sql = postgres('postgres://username:password@host:port/database', {
  host                 : '',            // Postgres ip address[es] or domain name[s]
  port                 : 5432,          // Postgres server port[s]
  path                 : '',            // unix socket path (usually '/tmp')
  database             : '',            // Name of database to connect to
  username             : '',            // Username of database user
  password             : '',            // Password of database user
  ssl                  : false,         // true, prefer, require, tls.connect options
  sslnegotiation       : null,          // direct
  max                  : 10,            // Max number of connections
  max_lifetime         : null,          // Max lifetime in seconds (more info below)
  idle_timeout         : 0,             // Idle connection timeout in seconds
  connect_timeout      : 30,            // Connect timeout in seconds
  prepare              : true,          // Automatic creation of prepared statements
  types                : [],            // Array of custom types, see more below
  onnotice             : fn,            // Default console.log, set false to silence NOTICE
  onparameter          : fn,            // (key, value) when server param change
  debug                : fn,            // Is called with (connection, query, params, types)
  socket               : fn,            // fn returning custom socket to use
  transform            : {
    undefined          : undefined,     // Transforms undefined values (eg. to null)
    column             : fn,            // Transforms incoming column names
    value              : fn,            // Transforms incoming row values
    row                : fn             // Transforms entire rows
  },
  connection           : {
    application_name   : 'postgres.js',
    ...                                 // Other connection parameters, see https://www.postgresql.org/docs/current/runtime-config-client.html
  },
  target_session_attrs : null,          // Use 'read-write' with multiple hosts to
                                        // ensure only connecting to primary
  fetch_types          : true,          // Automatically fetches types on connect
                                        // on initial connection.
})
```

--------------------------------

### Lazy Connection Initialization

Source: https://github.com/porsager/postgres/blob/master/README.md

Demonstrates how connections are opened only when a query is executed.

```js
const sql = postgres() // no connections are opened

await sql`...` // one connection is now opened
await sql`...` // previous opened connection is reused

// two connections are opened now
await Promise.all([
  sql`...`,
  sql`...`
])
```

--------------------------------

### Dynamic Table Name

Source: https://context7.com/porsager/postgres/llms.txt

Safely inserts a dynamic table name into a query using the `sql()` helper function to prevent SQL injection. Requires prior connection setup.

```javascript
import postgres from 'postgres'
const sql = postgres()

// Dynamic table name
const tableName = 'users'
const rows = await sql`
  SELECT * FROM ${sql(tableName)}
`
// Executes: SELECT * FROM "users"
```

--------------------------------

### Create Postgres.js Database Instance

Source: https://github.com/porsager/postgres/blob/master/README.md

Create a database instance using the postgres function. It can accept options or use environment variables for connection details. Import this instance for use in other modules.

```javascript
// db.js
import postgres from 'postgres'

const sql = postgres({ /* options */ }) // will use psql environment variables

export default sql
```

--------------------------------

### Database Connection

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

Instantiate the postgres client using connection URLs or options. Environment variables are used as fallbacks.

```APIDOC
## Connection

### `postgres([url], [options])`

You can use either a `postgres://` url connection string or the options to define your database connection properties. Options in the object will override any present in the url. Options will fall back to the same environment variables as psql.

```js
const sql = postgres('postgres://username:password@host:port/database', {
  host                 : '',            // Postgres ip address[s] or domain name[s]
  port                 : 5432,          // Postgres server port[s]
  database             : '',            // Name of database to connect to
  username             : '',            // Username of database user
  password             : '',            // Password of database user
  // ...and more
})
```

More options can be found in the [Connection details section](#connection-details).
```

--------------------------------

### Insert User and Return Data

Source: https://github.com/porsager/postgres/blob/master/README.md

Insert a new user into the 'users' table with provided name and age, then return the inserted name and age. This showcases dynamic inserts and returning specific columns.

```javascript
// users.js
import sql from './db.js'

async function insertUser({ name, age }) {
  const users = await sql`
    insert into users
      (name, age)
    values
      (${ name }, ${ age })
    returning name, age
  `
  // users = Result [{ name: "Murray", age: 68 }]
  return users
}
```

--------------------------------

### Reserve a Dedicated Connection

Source: https://context7.com/porsager/postgres/llms.txt

Use `sql.reserve()` to get a dedicated connection from the pool for operations requiring the same connection. Ensure the connection is always released back to the pool using a `finally` block.

```javascript
import postgres from 'postgres'
const sql = postgres()

// Reserve a connection
const reserved = await sql.reserve()

try {
  // All queries use the same connection
  await reserved`SET LOCAL timezone = 'UTC'`
  await reserved`SET LOCAL statement_timeout = '5s'`

  const result = await reserved`SELECT now() AT TIME ZONE 'UTC'`
  console.log(result)

  // Connection settings persist for reserved connection
  await reserved`SELECT * FROM events WHERE created_at > now() - interval '1 hour'`
} finally {
  // Always release back to pool
  reserved.release()
}
```

```javascript
// Use for session-specific settings
async function withTimezone(tz, fn) {
  const conn = await sql.reserve()
  try {
    await conn`SET LOCAL timezone = ${tz}`
    return await fn(conn)
  } finally {
    conn.release()
  }
}

const events = await withTimezone('America/New_York', sql =>
  sql`SELECT created_at::text FROM events LIMIT 5`
)
```

--------------------------------

### Connect to PostgreSQL using Environment Variables

Source: https://context7.com/porsager/postgres/llms.txt

Connects to PostgreSQL by automatically reading connection details from environment variables (PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD). Connections are created lazily.

```javascript
import postgres from 'postgres'
// Using environment variables (PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD)
const sql = postgres()
```

--------------------------------

### Connect to PostgreSQL using Options Object

Source: https://context7.com/porsager/postgres/llms.txt

Configures a database connection using an options object, allowing detailed settings for pooling, timeouts, and transformations. Connections are created lazily.

```javascript
import postgres from 'postgres'
const sql = postgres({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  username: 'user',
  password: 'secret',
  max: 10,                    // Max connections in pool
  idle_timeout: 20,           // Close idle connections after 20 seconds
  connect_timeout: 30,        // Connection timeout in seconds
  ssl: 'require',             // SSL mode: false, 'require', 'prefer', 'verify-full'
  prepare: true,              // Enable prepared statements
  transform: postgres.camel,  // Transform column names to camelCase
  debug: (conn, query, params) => console.log(query, params),
  onnotice: msg => console.log(msg),
  connection: {
    application_name: 'my-app',
    statement_timeout: 30000
  }
})
```

--------------------------------

### POST /connection

Source: https://github.com/porsager/postgres/blob/master/README.md

Establishes a connection to the PostgreSQL database using a connection string or configuration object.

```APIDOC
## postgres([url], [options])

### Description
Initializes a new database connection instance. You can provide a connection string or an options object. Options override URL parameters and fall back to psql environment variables.

### Method
Constructor / Initialization

### Parameters
#### Path Parameters
- **url** (string) - Optional - A postgres:// connection string.

#### Request Body
- **options** (object) - Optional - Configuration object including host, port, database, username, and password.

### Request Example
{
  "host": "localhost",
  "port": 5432,
  "database": "mydb",
  "username": "user",
  "password": "secret"
}
```

--------------------------------

### Execute Parameterized Query

Source: https://context7.com/porsager/postgres/llms.txt

Executes a SQL query with parameters safely escaped to prevent SQL injection. Values are extracted from template literals and sent separately. Requires prior connection setup.

```javascript
import postgres from 'postgres'
const sql = postgres()

// Parameterized query - values are safely escaped
const name = 'Alice'
const age = 25
const result = await sql`
  SELECT * FROM users
  WHERE name = ${name} AND age > ${age}
`
// Executes: SELECT * FROM users WHERE name = $1 AND age > $2
// Parameters: ['Alice', 25]
```

--------------------------------

### Define Row Type for SQL Queries in TypeScript

Source: https://github.com/porsager/postgres/blob/master/README.md

Define an interface for your row type and use it with the sql tag to get type-safe query results. This helps catch errors if you access non-existent properties.

```typescript
interface User {
  id: number
  name: string
}

const users = await sql<User[]>"SELECT * FROM users"
users[0].id // ok => number
users[1].name // ok => string
users[0].invalid // fails: `invalid` does not exists on `User`
```

--------------------------------

### Postgres.js Connection Options

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

This section outlines all the available configuration options for establishing a connection to a PostgreSQL database using Postgres.js.

```APIDOC
## POST /porsager/postgres

### Description
This section details the various configuration options available when initializing a Postgres.js client, covering connection parameters, authentication methods, security settings, and high availability setups.

### Method
POST

### Endpoint
/porsager/postgres

### Parameters
#### Query Parameters
- **host** (string) - Optional - Postgres ip address[es] or domain name[s]
- **port** (number) - Optional - Postgres server port[s]
- **path** (string) - Optional - unix socket path (usually '/tmp')
- **database** (string) - Optional - Name of database to connect to
- **username** (string) - Optional - Username of database user
- **password** (string) - Optional - Password of database user
- **ssl** (boolean | object) - Optional - true, prefer, require, tls.connect options
- **sslnegotiation** (null) - Optional - direct
- **max** (number) - Optional - Max number of connections
- **max_lifetime** (number | null) - Optional - Max lifetime in seconds (more info below)
- **idle_timeout** (number) - Optional - Idle connection timeout in seconds
- **connect_timeout** (number) - Optional - Connect timeout in seconds
- **prepare** (boolean) - Optional - Automatic creation of prepared statements
- **types** (array) - Optional - Array of custom types, see more below
- **onnotice** (function | boolean) - Optional - Default console.log, set false to silence NOTICE
- **onparameter** (function) - Optional - (key, value) when server param change
- **debug** (function) - Optional - Is called with (connection, query, params, types)
- **socket** (function) - Optional - function returning custom socket to use
- **transform** (object) - Optional - Transforms values
  - **undefined** (any) - Optional - Transforms undefined values (eg. to null)
  - **column** (function) - Optional - Transforms incoming column names
  - **value** (function) - Optional - Transforms incoming row values
  - **row** (function) - Optional - Transforms entire rows
- **connection** (object) - Optional - Other connection parameters
  - **application_name** (string) - Optional - Default application_name
- **target_session_attrs** (string | null) - Optional - Use 'read-write' with multiple hosts to ensure only connecting to primary
- **fetch_types** (boolean) - Optional - Automatically fetches types on connect on initial connection.

### Request Example
```json
{
  "host": "",
  "port": 5432,
  "database": "",
  "username": "",
  "password": "",
  "ssl": false,
  "max": 10,
  "max_lifetime": null,
  "idle_timeout": 0,
  "connect_timeout": 30,
  "prepare": true,
  "types": [],
  "onnotice": null,
  "onparameter": null,
  "debug": null,
  "socket": null,
  "transform": {
    "undefined": null,
    "column": null,
    "value": null,
    "row": null
  },
  "connection": {
    "application_name": "postgres.js"
  },
  "target_session_attrs": null,
  "fetch_types": true
}
```

### Response
#### Success Response (200)
- **sql** (object) - The initialized Postgres.js client instance.

#### Response Example
```json
{
  "message": "Postgres client initialized successfully."
}
```
```

--------------------------------

### Configure Connection with URL and Options

Source: https://github.com/porsager/postgres/blob/master/README.md

Configure the Postgres.js connection using a connection string URL and an options object. Options provided in the object will override any settings from the URL.

```javascript
const sql = postgres('postgres://username:password@host:port/database', {
  host                 : '',            // Postgres ip address[s] or domain name[s]
  port                 : 5432,          // Postgres server port[s]
  database             : '',            // Name of database to connect to
  username             : '',            // Username of database user
  password             : '',            // Password of database user
  ...and more
})
```

--------------------------------

### Combine Dynamic Parts in Query

Source: https://context7.com/porsager/postgres/llms.txt

Constructs a query with multiple dynamic parts, including table name, selected columns, and ordering column, using the `sql()` helper for safe identifier injection. Requires prior connection setup.

```javascript
import postgres from 'postgres'
const sql = postgres()

// Combining dynamic parts
const table = 'orders'
const selectCols = ['id', 'total', 'status']
const orderCol = 'created_at'
const data = await sql`
  SELECT ${sql(selectCols)}
  FROM ${sql(table)}
  ORDER BY ${sql(orderCol)} DESC
`
```

--------------------------------

### ESM Dynamic Import for Postgres.js

Source: https://github.com/porsager/postgres/blob/master/README.md

Demonstrates how to use Postgres.js with ESM dynamic imports. This is useful for scenarios where dynamic loading of modules is required.

```javascript
const { default: postgres } = await import('postgres')
```

--------------------------------

### Realtime Subscriptions via Logical Replication

Source: https://context7.com/porsager/postgres/llms.txt

Subscribe to database changes using logical replication. Requires wal_level set to logical and a publication created in the database.

```javascript
import postgres from 'postgres'
const sql = postgres({ publications: 'alltables' })

// First, create a publication in your database:
// CREATE PUBLICATION alltables FOR ALL TABLES;

// Subscribe to all changes
const { unsubscribe } = await sql.subscribe(
  '*',
  (row, info) => {
    console.log(info.command, info.relation.table, row)
  }
)

// Subscribe to specific operations
await sql.subscribe('insert', (row, info) => {
  console.log('Inserted into', info.relation.table, ':', row)
})

await sql.subscribe('update', (row, info) => {
  console.log('Updated:', row, 'Old values:', info.old)
})

await sql.subscribe('delete', (row, info) => {
  console.log('Deleted from', info.relation.table, ':', row)
})

// Subscribe to specific table
await sql.subscribe('*:users', (row, info) => {
  console.log('Users table changed:', info.command, row)
})

// Subscribe to specific table and operation
await sql.subscribe('insert:orders', (row, info) => {
  console.log('New order:', row)
})

// Subscribe to specific row by primary key
await sql.subscribe('update:users=123', (row, info) => {
  console.log('User 123 updated:', row)
})

// With reconnection callback
await sql.subscribe(
  'insert:events',
  (row, info) => handleEvent(row),
  () => console.log('Subscribed and ready'),
  (err) => console.error('Subscription error:', err)
)

// Unsubscribe when done
unsubscribe()
```

--------------------------------

### PREPARE TRANSACTION

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

Explains how to use `sql.prepare` to prepare a transaction for two-phase commit using the `PREPARE TRANSACTION` SQL statement.

```APIDOC
## PREPARE TRANSACTION `await sql.prepare([name]) -> fn()`

### Description
Indicates that the transactions should be prepared using the `PREPARE TRANSACTION [NAME]` statement instead of being committed. This is useful for implementing two-phase commit protocols.

### Request Example
```js
sql.begin('read write', async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
  `

  await sql.prepare('tx1')
})
```
```

--------------------------------

### Configure SSL with Reject Unauthorized

Source: https://github.com/porsager/postgres/blob/master/README.md

Configure SSL settings, specifically `rejectUnauthorized: false`, for certain cloud providers in production environments. This is a common workaround for environments where verifiable certificates are not supported.

```javascript
const sql =
  process.env.NODE_ENV === 'production'
    ? // "Unless you're using a Private or Shield Heroku Postgres database, Heroku Postgres does not currently support verifiable certificates"
      // https://help.heroku.com/3DELT3RK/why-can-t-my-third-party-utility-connect-to-heroku-postgres-with-ssl
      postgres({ ssl: { rejectUnauthorized: false } })
    : postgres()
```

--------------------------------

### Stream data into Postgres with COPY

Source: https://github.com/porsager/postgres/blob/master/README.md

Use writable streams to pipe data into a table via the COPY command.

```javascript
import { pipeline } from 'node:stream/promises'

// Stream of users with the default tab delimitated cells and new-line delimitated rows
const userStream = Readable.from([
  'Murray\t68\n',
  'Walter\t80\n'
])

const query = await sql`copy users (name, age) from stdin`.writable()
await pipeline(userStream, query);
```

--------------------------------

### Execute query from file

Source: https://github.com/porsager/postgres/blob/master/README.md

Execute SQL queries stored in external files using `sql.file()`. Supports passing arguments for parameterized queries.

```javascript
const result = await sql.file('query.sql', ['Murray', 68])
```

--------------------------------

### Perform Dynamic Inserts and Updates

Source: https://context7.com/porsager/postgres/llms.txt

Use the sql helper to map objects directly to SQL statements. Specify column names to restrict fields for security.

```javascript
import postgres from 'postgres'
const sql = postgres()

// Insert single object
const user = { name: 'Diana', age: 28, email: 'diana@example.com' }
const [inserted] = await sql`
  INSERT INTO users ${sql(user)}
  RETURNING *
`
// Executes: INSERT INTO users ("name", "age", "email") VALUES ($1, $2, $3)

// Insert with specific columns only (safer)
const userData = { name: 'Eve', age: 32, role: 'admin', password: 'secret' }
await sql`
  INSERT INTO users ${sql(userData, 'name', 'age')}
`
// Only inserts name and age, ignores role and password

// Insert multiple rows at once
const users = [
  { name: 'Frank', age: 40 },
  { name: 'Grace', age: 35 },
  { name: 'Henry', age: 29 }
]
await sql`INSERT INTO users ${sql(users, 'name', 'age')}`
// Executes: INSERT INTO users ("name", "age") VALUES ($1, $2), ($3, $4), ($5, $6)

// Update with object
const updates = { age: 33, email: 'new@example.com' }
await sql`
  UPDATE users SET ${sql(updates, 'age', 'email')}
  WHERE id = ${userId}
`
// Executes: UPDATE users SET "age" = $1, "email" = $2 WHERE id = $3

// Columns as array
const allowedColumns = ['name', 'age']
await sql`INSERT INTO users ${sql(user, allowedColumns)}`
```

--------------------------------

### Handle dynamic ordering

Source: https://github.com/porsager/postgres/blob/master/README.md

Construct dynamic order by clauses by mapping over object entries and creating sql fragments.

```javascript
const id = 1
const order = {
  username: 'asc'
  created_at: 'desc'
}
await sql`
  select 
    * 
  from ticket 
  where account = ${ id }  
  order by ${
    Object.entries(order).flatMap(([column, order], i) =>
      [i ? sql`,` : sql``, sql`${ sql(column) } ${ order === 'desc' ? sql`desc` : sql`asc` }`]
    )
  }
`
```

--------------------------------

### Execute simple queries

Source: https://github.com/porsager/postgres/blob/master/README.md

Use simple queries to support multiple statements without dynamic parameters.

```javascript
await sql`select 1; select 2;`.simple()
```

--------------------------------

### SQL Functions and Dynamic Identifiers

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

Demonstrates how to use SQL functions and dynamic identifiers (table and column names) within queries.

```APIDOC
## SQL Functions

Using keywords or calling functions dynamically is also possible by using ```sql``` fragments.

```js
const date = null

await sql`
  update users set updated_at = ${ date || sql`now()` }
`

// Which results in:
update users set updated_at = now()
```

## Table Names

Dynamic identifiers like table names and column names is also supported like so:

```js
const table = 'users'
    , column = 'id'

await sql`
  select ${ sql(column) } from ${ sql(table) }
`

// Which results in:
select "id" from "users"
```

### Quick primer on interpolation

Here's a quick oversight over all the ways to do interpolation in a query template string:

| Interpolation syntax       | Usage                         | Example                                                   |
| -------------              | -------------                 | ------------- |
| `${ sql`` }`               | for keywords or sql fragments | ``await sql`SELECT * FROM users ${sql`order by age desc` }` ``  |
| `${ sql(string) }`         | for identifiers               | ``await sql`SELECT * FROM ${sql('table_name')` ``               |
| `${ sql([] or {}, ...) }`  | for helpers                   | ``await sql`INSERT INTO users ${sql({ name: 'Peter'})}` ``      |
| `${ 'somevalue' }`         | for values                    | ``await sql`SELECT * FROM users WHERE age = ${42}` ``           |
```

--------------------------------

### Create publication for replication

Source: https://github.com/porsager/postgres/blob/master/README.md

SQL command to create a publication for all tables, required for logical replication.

```sql
CREATE PUBLICATION alltables FOR ALL TABLES
```

--------------------------------

### COPY to/from as Streams

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

Utilize Node.js streams for efficient data transfer with PostgreSQL's COPY command.

```APIDOC
## COPY ... FROM STDIN (Writable Stream)

### Description
Write data to PostgreSQL using the `COPY ... FROM STDIN` command, exposed as a Node.js writable stream.

### Method
`sql`copy ... from stdin`.writable()

### Endpoint
N/A (Client-side execution)

### Parameters
None

### Request Example
```js
import { pipeline } from 'node:stream/promises'
import { Readable } from 'node:stream'

const userStream = Readable.from([
  'Murray\t68\n',
  'Walter\t80\n'
])

const query = await sql`copy users (name, age) from stdin`.writable()
await pipeline(userStream, query);
```

### Response
Returns a Node.js `Writable` stream.
```

```APIDOC
## COPY ... TO STDOUT (Readable Stream)

### Description
Read data from PostgreSQL using the `COPY ... TO STDOUT` command, exposed as a Node.js readable stream.

### Method
`sql`copy ... to stdout`.readable()

### Endpoint
N/A (Client-side execution)

### Parameters
None

### Request Example (Stream Pipeline)
```js
import { pipeline } from 'node:stream/promises'
import { createWriteStream } from 'node:fs'

const readableStream = await sql`copy users (name, age) to stdout`.readable()
await pipeline(readableStream, createWriteStream('output.tsv'))
// output.tsv content: `Murray\t68\nWalter\t80\n`
```

### Request Example (for await...of)
```js
const readableStream = await sql`
  copy (
    select name, age
    from users
    where age = 68
  ) to stdout
`.readable()
for await (const chunk of readableStream) {
  // chunk.toString() === `Murray\t68\n`
}
```

### Response
Returns a Node.js `Readable` stream.
```

--------------------------------

### Execute Unsafe and File Queries with Postgres.js

Source: https://context7.com/porsager/postgres/llms.txt

Safely execute raw SQL strings or load queries from files when dynamic query building is necessary. Use caution with `unsafe` as parameters are not auto-escaped and require manual validation.

```javascript
import postgres from 'postgres'
const sql = postgres()

// Unsafe query - use with caution, parameters NOT auto-escaped
const tableName = 'users'  // Must be validated/sanitized!
const result = await sql.unsafe(`SELECT * FROM ${tableName} WHERE id = $1`, [123])
```

```javascript
// Unsafe with options
const data = await sql.unsafe(
  'SELECT * FROM users WHERE name = $1',
  ['Alice'],
  { prepare: true }  // Use prepared statement
)
```

```javascript
// Combining safe and unsafe in nested fragments
const orderBy = 'created_at DESC'  // Must be validated!
const users = await sql`
  SELECT * FROM users
  WHERE active = true
  ${sql.unsafe(`ORDER BY ${orderBy}`)}
`
```

```javascript
// Load query from file
const users = await sql.file('queries/get-users.sql')
```

```javascript
// File query with parameters
// queries/get-user-by-id.sql: SELECT * FROM users WHERE id = $1
const [user] = await sql.file('queries/get-user-by-id.sql', [userId])
```

```javascript
// File query with options
const results = await sql.file('queries/complex-report.sql', [], { cache: true })
```

```javascript
// Simple query mode (multiple statements, no parameters)
await sql`
  CREATE TABLE IF NOT EXISTS test (id serial);
  INSERT INTO test DEFAULT VALUES;
  INSERT INTO test DEFAULT VALUES;
`.simple()
```

--------------------------------

### Queries in Files

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

Execute SQL queries stored in external files using `sql.file(path, [args], [options])`.

```APIDOC
## Queries in Files

Using a file for a query is also supported with optional parameters to use if the file includes `$1, $2, etc`

```js
const result = await sql.file('query.sql', ['Murray', 68])
```
```

--------------------------------

### Stream data from Postgres with COPY

Source: https://github.com/porsager/postgres/blob/master/README.md

Use readable streams to export data from a table via the COPY command.

```javascript
import { pipeline } from 'node:stream/promises'
import { createWriteStream } from 'node:fs'

const readableStream = await sql`copy users (name, age) to stdout`.readable()
await pipeline(readableStream, createWriteStream('output.tsv'))
// output.tsv content: `Murray\t68\nWalter\t80\n`
```

```javascript
const readableStream = await sql`
  copy (
    select name, age
    from users
    where age = 68
  ) to stdout
`.readable()
for await (const chunk of readableStream) {
  // chunk.toString() === `Murray\t68\n`
}
```

--------------------------------

### POST /query

Source: https://github.com/porsager/postgres/blob/master/README.md

Executes a SQL query against the database using tagged template literals for safe parameter interpolation.

```APIDOC
## sql`query`

### Description
Executes a SQL query. The tagged template literal automatically handles parameter serialization and escaping to prevent SQL injection.

### Method
POST (via tagged template execution)

### Parameters
- **query** (string) - Required - The SQL statement to execute, with template literals for dynamic values.

### Request Example
sql`select * from users where age > ${age}`

### Response
#### Success Response (200)
- **Result** (Array) - An array of objects where each object represents a row, mapping column names to values.

#### Response Example
[
  { "name": "Walter", "age": 80 },
  { "name": "Murray", "age": 68 }
]
```

--------------------------------

### Instant Iteration with forEach

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

Handle rows returned by a query one by one using the `.forEach` method.

```APIDOC
## Instant iteration

If you want to handle rows returned by a query one by one, you can use `.forEach` which returns a promise that resolves once there are no more rows.

```js
await sql`
  select created_at, name from events
`.forEach(row => {
  // row = { created_at: '2019-11-22T14:22:00Z', name: 'connected' }
})

// No more rows
```
```

--------------------------------

### Subscribe to real-time updates

Source: https://github.com/porsager/postgres/blob/master/README.md

Subscribes to database changes using logical replication. Requires appropriate database configuration.

```javascript
const sql = postgres({ publications: 'alltables' })

const { unsubscribe } = await sql.subscribe(
  'insert:events',
  (row, { command, relation, key, old }) => {
    // Callback function for each row change
    // tell about new event row over eg. websockets or do something else
  },
  () => {
    // Callback on initial connect and potential reconnects
  }
)
```

--------------------------------

### Connect with Dynamic Password

Source: https://context7.com/porsager/postgres/llms.txt

Establishes a connection where the password is provided by an asynchronous function, useful for rotating credentials. Connections are created lazily.

```javascript
import postgres from 'postgres'
// Dynamic password for rotating credentials
const sql = postgres({
  host: 'db.example.com',
  password: async () => await getSecretFromVault()
})
```

--------------------------------

### Configure Connection Pool Lifecycle

Source: https://context7.com/porsager/postgres/llms.txt

Configure connection pool settings such as maximum connections, idle timeouts, and maximum connection lifetime. Implement `onclose` and `onnotice` callbacks for connection events.

```javascript
import postgres from 'postgres'

const sql = postgres({
  max: 10,
  idle_timeout: 20,      // Close idle connections after 20 seconds
  max_lifetime: 60 * 30, // Max connection age: 30 minutes
  connect_timeout: 10,   // Connection timeout: 10 seconds
  onclose: connId => console.log(`Connection ${connId} closed`),
  onnotice: notice => console.log('Notice:', notice.message)
})
```

--------------------------------

### Dynamic Ordering in Postgres.js

Source: https://github.com/porsager/postgres/blob/master/deno/README.md

Construct dynamic `ORDER BY` clauses by mapping over an object of sort criteria. Use `sql()` to safely interpolate column names and order directions.

```javascript
const id = 1
const order = {
  username: 'asc'
  created_at: 'desc'
}
await sql`
  select 
    * 
  from ticket 
  where account = ${ id }  
  order by ${ 
    Object.entries(order).flatMap(([column, order], i) =>
      [i ? sql`,` : sql``, sql`${ sql(column) } ${ order === 'desc' ? sql`desc` : sql`asc` }`]
    )
  }
`
```