- đ Fastest full featured PostgreSQL client
- đŻ 1250 LOC - 0 dependencies
- đˇ ES6 Tagged Template Strings at the core
- đââď¸ Simple surface API
- đŹ Chat on Gitter
Prelude
This code is a modified version of the Node.js package postgres
for the Deno runtime.
It is intended to remain as close as possible to the original code in order to simplify futur pull merge.
Currently, almost all tests pass except thoses about SSL. Some fail from time to time due to a race condition that occurs because there is currently no real alternative to the Node.js âsetImmediateâ function in Deno.
Getting started
Install
import postgres from 'https://deno.land/x/postgresql/mod.js'
Use
// db.js
import postgres from 'https://deno.land/x/postgresql/mod.js'
const sql = postgres({ ...options }) // will default to the same as psql
export default sql
// other.js
import sql from './db.js'
const users = await sql`
select name, age from users
`
// users: [{ name: 'Murray', age: 68 }, { name: 'Walter', age: 78 }]
postgres([url], [options])
Connection 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.
const sql = postgres('postgres://username:password@host:port/database', {
host : '', // Postgres ip address[s] 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 [NOT WORKING YET]
max : 10, // Max number of connections
idle_timeout : 0, // Idle connection timeout in seconds
connect_timeout : 30, // Connect timeout in seconds
no_prepare : false, // No automatic creation of prepared statements
types : [], // Array of custom types, see more below
onnotice : fn // Defaults to console.log
onparameter : fn // (key, value) when server param change
debug : fn // Is called with (connection, query, params)
transform : {
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
},
target_session_attrs : null // Use 'read-write' with multiple hosts to
// ensure only connecting to primary
})
Environment Variables for Options
It is also possible to connect to the database without a connection string or options, which will read the options from the environment variables in the table below (require --allow-env
):
SSL
Currently, the ssl
option is NOT WORKING for the Deno version and setting it will result in a SSL_NOT_SUPPORTED_YET
error. PR are welcome.
Multi host connections - High Availability (HA)
Connection uri strings with multiple hosts works like in psql multiple host uris
Connecting to the specified hosts/ports will be tried in order, and on a successfull connection retries will be reset. This ensures that hosts can come up and down seamless to your application.
If you specify target_session_attrs: 'read-write'
or PGTARGETSESSIONATTRS=read-write
Postgres.js will only connect to a writeable host allowing for zero down time failovers.
Environment Variables for Options
It is also possible to connect to the database without a connection string or any options. Postgres.js will fall back to the common environment variables used by psql
as in the table below:
const sql = postgres()
Option | Environment Variables |
---|---|
host |
PGHOST |
port |
PGPORT |
database |
PGDATABASE |
username |
PGUSERNAME or PGUSER |
password |
PGPASSWORD |
idle_timeout |
PGIDLE_TIMEOUT |
â connect_timeout |
PGCONNECT_TIMEOUT |
NOTE: With Deno, you have to explicitly grant permission to access environment variables with the command line flag --allow-env
.
sql` ` -> Promise
Query A query will always return a Promise
which resolves to a results array [...]{ rows, command }
. Destructuring is great to immediately access the first element.
const [new_user] = await sql`
insert into users (
name, age
) values (
'Murray', 68
)
returning *
`
// new_user = { user_id: 1, name: 'Murray', age: 68 }
TypeScript support
postgres
has TypeScript support. You can pass a row list type for your queries in this way:
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`
However, be sure to check the array length to avoid accessing properties of undefined
rows:
const users = await sql<User[]>`SELECT * FROM users WHERE id = ${id}`
if (!users.length)
throw new Error('Not found')
return users[0]
You can also prefer destructuring when you only care about a fixed number of rows.
In this case, we recommand you to prefer using tuples to handle undefined
properly:
const [user]: [User?] = await sql`SELECT * FROM users WHERE id = ${id}`
if (!user) // => User | undefined
throw new Error('Not found')
return user // => User
// NOTE:
const [first, second]: [User?] = await sql`SELECT * FROM users WHERE id = ${id}` // fails: `second` does not exist on `[User?]`
// vs
const [first, second] = await sql<[User?]>`SELECT * FROM users WHERE id = ${id}` // ok but should fail
All the public API is typed. Also, TypeScript support is still in beta. Feel free to open an issue if you have trouble with types.
Query parameters
Parameters are automatically inferred and handled by Postgres so that SQL injection isnât possible. No special handling is necessary, simply use JS tagged template literals as usual.
let search = 'Mur'
const users = await sql`
select
name,
age
from users
where
name like ${ search + '%' }
`
// users = [{ name: 'Murray', age: 68 }]
Arrays will be handled by replacement parameters too, so where in
queries are also simple.
const users = await sql`
select
*
from users
where age in (${ [68, 75, 23] })
`
sql` `.stream(fn) -> Promise
Stream If you want to handle rows returned by a query one by one, you can use .stream
which returns a promise that resolves once there are no more rows.
await sql`
select created_at, name from events
`.stream(row => {
// row = { created_at: '2019-11-22T14:22:00Z', name: 'connected' }
})
// No more rows
sql` `.cursor([rows = 1], fn) -> Promise
Cursor Use cursors if you need to throttle the amount of rows being returned from a query. New results wonât be requested until the promise / async callback function has resolved.
await sql`
select * from generate_series(1,4) as x
`.cursor(async row => {
// row = { x: 1 }
await http.request('https://example.com/wat', { row })
})
// No more rows
A single row will be returned by default, but you can also request batches by setting the number of rows desired in each batch as the first argument. That is usefull if you can do work with the rows in parallel like in this example:
await sql`
select * from generate_series(1,1000) as x
`.cursor(10, async rows => {
// rows = [{ x: 1 }, { x: 2 }, ... ]
await Promise.all(rows.map(row =>
http.request('https://example.com/wat', { row })
))
})
If an error is thrown inside the callback function no more rows will be requested and the promise will reject with the thrown error.
You can also stop receiving any more rows early by returning an end token sql.END
from the callback function.
await sql`
select * from generate_series(1,1000) as x
`.cursor(row => {
return Math.random() > 0.9 && sql.END
})
Listen and notify
When you call listen, a dedicated connection will automatically be made to ensure that you receive notifications in real time. This connection will be used for any further calls to listen. Listen returns a promise which resolves once the LISTEN
query to Postgres completes, or if there is already a listener active.
await sql.listen('news', payload => {
const json = JSON.parse(payload)
console.log(json.this) // logs 'is'
})
Notify can be done as usual in sql, or by using the sql.notify
method.
sql.notify('news', JSON.stringify({ no: 'this', is: 'news' }))
sql``
Tagged template function Tagged template functions are not just ordinary template literal strings. They allow the function to handle any parameters within before interpolation. This means that they can be used to enforce a safe way of writing queries, which is what Postgres.js does. Any generic value will be serialized according to an inferred type, and replaced by a PostgreSQL protocol placeholders $1, $2, ...
and then sent to the database as a parameter to let it handle any need for escaping / casting.
This also means you cannot write dynamic queries or concat queries together by simple string manipulation. To enable dynamic queries in a safe way, the sql
function doubles as a regular function which escapes any value properly. It also includes overloads for common cases of inserting, selecting, updating and querying.
sql()
inside tagged template
Dynamic query helpers - Postgres.js has a safe, ergonomic way to aid you in writing queries. This makes it easier to write dynamic insert
, select
and update
queries, and pass where
parameters.
Insert
const user = {
name: 'Murray',
age: 68
}
sql`
insert into users ${
sql(user, 'name', 'age')
}
`
// Is translated into this query:
insert into users (name, age) values ($1, $2)
You can leave out the column names and simply do sql(user)
if you want to get all fields from the object as columns, but be careful not to allow users to supply columns you donât want.
Multiple inserts in one query
If you need to insert multiple rows at the same time itâs also much faster to do it with a single insert
. Simply pass an array of objects to sql()
.
const users = [{
name: 'Murray',
age: 68,
garbage: 'ignore'
}, {
name: 'Walter',
age: 78
}]
sql`
insert into users ${
sql(users, 'name', 'age')
}
`
Update
This is also useful for update queries
const user = {
id: 1,
name: 'Muray'
}
sql`
update users set ${
sql(user, 'name')
} where
id = ${ user.id }
`
// Is translated into this query:
update users set name = $1 where id = $2
Select
const columns = ['name', 'age']
sql`
select ${
sql(columns)
} from users
`
// Is translated into this query:
select name, age from users
Dynamic table name
const table = 'users'
sql`
select id from ${sql(table)}
`
// Is translated into this query:
select id from users
sql.array(Array)
Arrays PostgreSQL has a native array type which is similar to js arrays, but only allows the same type and shape for nested items. This method automatically infers the item type and serializes js arrays into PostgreSQL arrays.
const types = sql`
insert into types (
integers,
strings,
dates,
buffers,
multi
) values (
${ sql.array([1,2,3,4,5]) },
${ sql.array(['Hello', 'Postgres']) },
${ sql.array([new Date(), new Date(), new Date()]) },
${ sql.array([Buffer.from('Hello'), Buffer.from('Postgres')]) },
${ sql.array([[[1,2],[3,4]][[5,6],[7,8]]]) },
)
`
sql.json(object)
JSON
const body = { hello: 'postgres' }
const [{ json }] = await sql`
insert into json (
body
) values (
${ sql.json(body) }
)
returning body
`
// json = { hello: 'postgres' }
sql.file(path, [args], [options]) -> Promise
File query Using an .sql
file for a query. The contents will be cached in memory so that the file is only read once. Requires the --allow-read
flag.
sql.file(path.join(__dirname, 'query.sql'), [], {
cache: true // Default true - disable for single shot queries or memory reasons
})
Transactions
sql.begin(fn) -> Promise
BEGIN / COMMIT Calling begin with a function will return a Promise which resolves with the returned value from the function. The function provides a single argument which is sql
with a context of the newly created transaction. BEGIN
is automatically called, and if the Promise fails ROLLBACK
will be called. If it succeeds COMMIT
will be called.
const [user, account] = await sql.begin(async sql => {
const [user] = await sql`
insert into users (
name
) values (
'Alice'
)
`
const [account] = await sql`
insert into accounts (
user_id
) values (
${ user.user_id }
)
`
return [user, account]
})
sql.savepoint([name], fn) -> Promise
SAVEPOINT
sql.begin(async sql => {
const [user] = await sql`
insert into users (
name
) values (
'Alice'
)
`
const [account] = (await sql.savepoint(sql =>
sql`
insert into accounts (
user_id
) values (
${ user.user_id }
)
`
).catch(err => {
// Account could not be created. ROLLBACK SAVEPOINT is called because we caught the rejection.
})) || []
return [user, account]
})
.then(([user, account]) => {
// great success - COMMIT succeeded
})
.catch(() => {
// not so good - ROLLBACK was called
})
Do note that you can often achieve the same result using WITH
queries (Common Table Expressions) instead of using transactions.
Types
You can add ergonomic support for custom types, or simply pass an object with a { type, value }
signature that contains the Postgres oid
for the type and the correctly serialized value.
Adding Query helpers is the recommended approach which can be done like this:
const sql = sql({
types: {
rect: {
to : 1337,
from : [1337],
serialize : ({ x, y, width, height }) => [x, y, width, height],
parse : ([x, y, width, height]) => { x, y, width, height }
}
}
})
const [custom] = sql`
insert into rectangles (
name,
rect
) values (
'wat',
${ sql.types.rect({ x: 13, y: 37, width: 42, height: 80 }) }
)
returning *
`
// custom = { name: 'wat', rect: { x: 13, y: 37, width: 42, height: 80 } }
Teardown / Cleanup
To ensure proper teardown and cleanup on server restarts use sql.end({ timeout: 0 })
before Deno.exit()
.
Calling sql.end()
will reject new queries and return a Promise which resolves when all queries are finished and the underlying connections are closed. If a timeout is provided any pending queries will be rejected once the timeout is reached and the connections will be destroyed.
Prexit
Sample shutdown using
import prexit from 'prexit'
prexit(async () => {
await sql.end({ timeout: 5 })
await new Promise(r => server.close(r))
})
Numbers, bigint, numeric
Number
in javascript is only able to represent 253-1 safely which means that types in PostgreSQLs like bigint
and numeric
wonât fit into Number
.
Deno supports BigInt
, so we can use it to match the PostgreSQL type bigint
which is returned for eg. count(*)
. Unfortunately it doesnât work with JSON.stringify
out of the box, so Postgres.js will return it as a string.
If you want to use BigInt
you can add this custom type:
const sql = postgres({
types: {
bigint: postgres.BigInt
}
})
There is currently no way to handle numeric / decimal
in a native way in Javascript, so these and similar will be returned as string
. You can also handle types like these using custom types if you want to.
The Connection Pool
Connections are created lazily once a query is created. This means that simply doing const sql = postgres(...)
wonât have any effect other than instantiating a new sql
instance.
No connection will be made until a query is made.
This means that we get a much simpler story for error handling and reconnections. Queries will be sent over the wire immediately on the next available connection in the pool. Connections are automatically taken out of the pool if you start a transaction using sql.begin()
, and automatically returned to the pool once your transaction is done.
Any query which was already sent over the wire will be rejected if the connection is lost. Itâll automatically defer to the error handling you have for that query, and since connections are lazy itâll automatically try to reconnect the next time a query is made. The benefit of this is no weird generic âonerrorâ handler that tries to get things back to normal, and also simpler application code since you donât have to handle errors out of context.
There are no guarantees about queries executing in order unless using a transaction with sql.begin()
or setting max: 1
. Of course doing a series of queries, one awaiting the other will work as expected, but thatâs just due to the nature of js async/promise handling, so itâs not necessary for this library to be concerned with ordering.
Idle timeout
Connections will by default not close until .end()
is called, but often it is useful to have them close when there is no activity or if using Postgres.js in eg. Lamdas / Serverless environments. This can be done using the idle_timeout
option to specify the amount of seconds to wait before automatically closing an idle connection.
For example, to close idle connections after 2 seconds:
const sql = postgres({
idle_timeout: 2
})
Prepared statements
Prepared statements will automatically be created for any queries where it can be inferred that the query is static. This can be disabled by using the no_prepare
option. For instance â this is useful when using PGBouncer in transaction mode
.
sql.unsafe
- Advanced unsafe use cases
sql.unsafe(query, [args], [options]) -> promise
Unsafe queries If you know what youâre doing, you can use unsafe
to pass any string youâd like to postgres. Please note that this can lead to sql injection if youâre not careful.
sql.unsafe('select ' + danger + ' from users where id = ' + dragons)
Errors
Errors are all thrown to related queries and never globally. Errors coming from PostgreSQL itself are always in the native Postgres format, and the same goes for any Deno errors eg. coming from the underlying connection.
Query errors will contain a stored error with the origin of the query to aid in tracing errors.
Query errors will also contain the query
string and the parameters
which are not enumerable to avoid accidentally leaking confidential information in logs. To log these it is required to specifically access error.query
and error.parameters
.
There are also the following errors specifically for this library.
UNDEFINED_VALUE
Undefined values are not allowed
Postgres.js wonât accept undefined
as values in tagged template queries since it becomes ambiguous what to do with the value. If you want to set something to null, use null
explicitly.
SSL_NOT_SUPPORTED_YET
SSL is not implemented yet.
Current version of postgres-deno
does not provide SSL support yet. Will be remove soon.
MESSAGE_NOT_SUPPORTED
X (X) is not supported
Whenever a message is received from Postgres which is not supported by this library. Feel free to file an issue if you think something is missing.
MAX_PARAMETERS_EXCEEDED
Max number of parameters (65534) exceeded
The postgres protocol doesnât allow more than 65534 (16bit) parameters. If you run into this issue there are various workarounds such as using sql([...])
to escape values instead of passing them as parameters.
SASL_SIGNATURE_MISMATCH
Message type X not supported
When using SASL authentication the server responds with a signature at the end of the authentication flow which needs to match the one on the client. This is to avoid man in the middle attacks. If you receive this error the connection was cancelled because the server did not reply with the expected signature.
NOT_TAGGED_CALL
Query not called as a tagged template literal
Making queries has to be done using the sql function as a tagged template. This is to ensure parameters are serialized and passed to Postgres as query parameters with correct types and to avoid SQL injection.
AUTH_TYPE_NOT_IMPLEMENTED
Auth type X not implemented
Postgres supports many different authentication types. This one is not supported.
CONNECTION_CLOSED
write CONNECTION_CLOSED host:port
This error is thrown if the connection was closed without an error. This should not happen during normal operation, so please create an issue if this was unexpected.
CONNECTION_ENDED
write CONNECTION_ENDED host:port
This error is thrown if the user has called sql.end()
and performed a query afterwards.
CONNECTION_DESTROYED
write CONNECTION_DESTROYED host:port
This error is thrown for any queries that were pending when the timeout to sql.end({ timeout: X })
was reached.
CONNECTION_CONNECT_TIMEOUT
write CONNECTION_CONNECT_TIMEOUT host:port
This error is thrown if the startup phase of the connection (tcp, protocol negotiation and auth) took more than the default 30 seconds or what was specified using connect_timeout
or PGCONNECT_TIMEOUT
.
Migration tools
Postgres.js doesnât come with any migration solution since itâs way out of scope, but here are some modules that supports Postgres.js for migrations:
Thank you
A really big thank you to @JAForbes who introduced me to Postgres and still holds my hand navigating all the great opportunities we have.
Thanks to @ACXgit for initial tests and dogfooding.
Also thanks to Ryan Dahl for letting me have the postgres
npm package name.
Finally thanks to Rasmus Porsager for his amazing Node.js library!