deno-postgres

PostgreSQL driver for Deno.

deno-postgres is being developed based on excellent work of node-postgres and pq.

Example

import { Client } from "https://deno.land/x/postgres/mod.ts";

async function main() {
  const client = new Client({
    user: "user",
    database: "test",
    hostname: "localhost",
    port: 5432,
  });
  await client.connect();
  const result = await client.query("SELECT * FROM people;");
  console.log(result.rows);
  await client.end();
}

main();

Connection Management

You are free to create your ‘clients’ like so:

const client = new Client({
  ...
})
await client.connect()

But for stronger management and scalability, you can use pools:

import { Pool } from "https://deno.land/x/postgres@v0.4.0/mod.ts";
import { PoolClient } from "https://deno.land/x/postgres@v0.4.0/client.ts";

const POOL_CONNECTIONS = 20;
const dbPool = new Pool({
  user: "user",
  password: "password",
  database: "database",
  hostname: "hostname",
  port: 5432,
}, POOL_CONNECTIONS);

async function runQuery(query: string) {
  const client: PoolClient = await dbPool.connect();
  const dbResult = await client.query(query);
  client.release();
  return dbResult;
}

await runQuery("SELECT * FROM users;");
await runQuery("SELECT * FROM users WHERE id = '1';");

This improves performance, as creating a whole new connection for each query can be an expensive operation. With pools, you can keep the connections open to be re-used when requested (const client = dbPool.connect()). So one of the active connections will be used instead of creating a new one.

The number of pools is up to you, but I feel a pool of 20 is good for small applications. Though remember this can differ based on how active your application is. Increase or decrease where necessary.

API

deno-postgres follows node-postgres API to make transition for Node devs as easy as possible.

Connecting to DB

If any of parameters is missing it is read from environmental variable.

import { Client } from "https://deno.land/x/postgres/mod.ts";

let config;

config = {
  hostname: "localhost",
  port: 5432,
  user: "user",
  database: "test",
  applicationName: "my_custom_app",
};
// alternatively
config = "postgres://user@localhost:5432/test?application_name=my_custom_app";

const client = new Client(config);
await client.connect();
await client.end();

Queries

Simple query

const result = await client.query("SELECT * FROM people;");
console.log(result.rows);

Parametrized query

const result = await client.query(
  "SELECT * FROM people WHERE age > <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mn>1</mn><mi>A</mi><mi>N</mi><mi>D</mi><mi>a</mi><mi>g</mi><mi>e</mi><mo>&lt;</mo></mrow><annotation encoding="application/x-tex">1 AND age &lt; </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8778em;vertical-align:-0.1944em;"></span><span class="mord">1</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.10903em;">N</span><span class="mord mathnormal" style="margin-right:0.02778em;">D</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.03588em;">g</span><span class="mord mathnormal">e</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&lt;</span></span></span></span>2;",
  10,
  20,
);
console.log(result.rows);

// equivalent using QueryConfig interface
const result = await client.query({
  text: "SELECT * FROM people WHERE age > <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mn>1</mn><mi>A</mi><mi>N</mi><mi>D</mi><mi>a</mi><mi>g</mi><mi>e</mi><mo>&lt;</mo></mrow><annotation encoding="application/x-tex">1 AND age &lt; </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8778em;vertical-align:-0.1944em;"></span><span class="mord">1</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.10903em;">N</span><span class="mord mathnormal" style="margin-right:0.02778em;">D</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.03588em;">g</span><span class="mord mathnormal">e</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&lt;</span></span></span></span>2;",
  args: [10, 20],
});
console.log(result.rows);

Interface for query result

import { QueryResult } from "https://deno.land/x/postgres@v0.4.2/query.ts";

const result: QueryResult = await client.query(...)
if (result.rowCount > 0) {
  console.log("Success")
} else {
  console.log("A new row should have been added but wasnt")
}