Getting started

Connecting to a database

Use connectPg() by providing a URL:

import { connectPg } from 'https://deno.land/x/pgc4d/src/mod.ts'

const db = await connectPg('postgres://username:password@hostname/database')

... or individual configuration options:

const db = await connectPg({
    hostname: 'hostname',
    username: 'username',
    password: 'password',
    database: 'database',
})

For a full list of options, see ConnectPgOptions.

The recommended pattern to ensure the connection is closed is to use try..finally:

const db = await connectPg(...)
try {
    ...
} finally {
    db.close()
}

Querying

Simple queries

const result = await db.query('SELECT generate_series(1,100)')
for await (const row of result.rows) {
    console.log(row)
}

result is an instance of BufferedQueryResult.

Streaming queries

const result = await db.queryStreaming('SELECT generate_series(1,100)')
for await (const row of result.rowsIterator) {
    console.log(row)
}

result is an instance of StreamingQueryResult.

Prepared statements

const stmt = await db.prepare('SELECT $1 + 100')
const result1 = (await stmt.execute([1])).value
const result2 = (await stmt.execute([2])).value
const result3 = (await stmt.execute([3])).value

There is also stmt.executeStreaming() to mirror db.queryStreaming().

Query results

BufferedQueryResult provides a set of accessors that help you get the result in the shape you need:

	1 column	Any number of columns (keyed rows)	Any number of columns (indexed rows)
0-1 rows	maybeValue: ColumnValue?	maybeRow: KeyedRow?	maybeIndexedRow: IndexedRow?
Exactly 1 row	value: ColumnValue	row: KeyedRow	indexedRow: IndexedRow
Any number of rows	column: ColumnValue[]	rows: KeyedRow[]	indexedRows: IndexedRow[]

KeyedRow are rows in the shape of JavaScript objects with column names as keys:

{ first_name: 'John', last_name: 'Doe', age: 33 }

IndexedRow are rows in the shape of JavaScript arrays, without column names:

[ 'John', 'Doe', 33 ]

If the result set is not of the expected shape (number of rows and columns), an exception will be thrown.

Additionally Both StreamingQueryResult and BufferedQueryResult expose iterators:

• indexedRowsIterator: AsyncIterableIterator<IndexedRow>
• rowsIterator: AsyncIterableIterator<KeyedRow>
• columnIterator: AsyncIterableIterator<ColumnValue>

In the case of the streaming response, the rows may only be iterated over once.

Data types

PostgreSQL type	Output	Accepted inputs	Comment
boolean	boolean	boolean
int2	number	number
int4	number	number
int8	bigint	bigint, number
float4	number	number
float8	number	number
bytea	UInt8Array	UInt8Array
timestamp	Date	Date
timestamptz	Date	Date
date	string (yyyy-MM-dd)	string (yyyy-MM-dd)
json	object, array, string, number, boolean	object, array, string, number, boolean	'null'::json is not representable
jsonb	object, array, string, number, boolean	object, array, string, number, boolean	'null'::json is not representable
oid	number	number
text	string	string
name	string	string
void	undefined	any

Arrays of the above types are supported where applicable.

Unsupported types will throw an exception. You can work around this by casting them to a known type, e.g. $1::text or $1::int. This works for both input parameters as well as output column values.