How to work with Postgres database in Deno

In this article, we’ll go over the usual aspects of working with Postgres database in Deno. We’ll cover things like:

• Connecting to database
• Creating table
• Querying data
• Updating data
• Removing data
• Catching errors

Let’s get started.

What imports are required?

At minimum, we need to import Client from the postgres module from Deno’s third party modules:

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

To handle errors cleanly, we need to import three custom error types from the same postgres module:

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

What permissions are required?

The database access is subject to network permissions to the database host and port:

> deno run --no-prompt --allow-net=localhost:5432 app.ts

How to initialize database access?

To initialize the database, the access credentials can be provided as an object or a connection string.

Here is the initialization code for the both ways:

Using ClientOptions:

const client = new Client({
  database: "deno-db",
  hostname: "localhost",
  port: 5432,
  user: "denouser",
  password: "denoPwd",
});

Using connection string:

const client = new Client(
  "postgres://denouser:denoPwd@localhost:5432/deno-db",
);

How to make database connection?

The database connection is made using connect API provided by the Client. The connect API can throw Deno.errors.ConnectionRefused. This needs to be caught.

try {
  await client.connect();
} catch (e) {
  if (e instanceof Deno.errors.ConnectionRefused) {
    console.error(e.message);
  }
}

If connect API doesn’t throw any error, the database connection is successful.

How to create tables?

The queryObject API can be used for all the operations, including DDL commands.

await client.queryObject(`create table product (
  id integer primary key,
  name varchar(100) not null,
  price real not null
)`);

This creates a table named product in the deno-db database:

deno-db=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | product | table | denouser
(1 row)
deno-db=# \d product
                      Table "public.product"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id     | integer                |           | not null | 
 name   | character varying(100) |           | not null | 
 price  | real                   |           | not null | 
Indexes:
    "product_pkey" PRIMARY KEY, btree (id)

If DDL command isn’t successful, a PostgresError would be thrown.

For example, creating another table with the same name, product:

PostgresError: relation "product" already exists

How to insert data into a table?

The queryObject API can also be used to insert data into any table. The variables can be supplied either as an array or object. The variables can be referred by:

• index ($1, $2, etc.) for array input
• name ($id, $name, etc.) for object input

If error is not thrown, the operation is considered as successful. The rowCount attribute in the output indicates affected rows.

Passing variables as array

const id = 1, name = "milk", price = "5.99";
const res = await client.queryObject(
  `insert into product 
  (id, name, price)
  values ($1, $2, $3)
`,
  [id, name, price],
);
// res.rowCount = 1

Passing variables as object

const id = 1, name = "milk", price = "5.99";
await client.queryObject(
  `insert into product 
  (id, name, price)
  values ($id, $name, $price)
`,
  { id, name, price },
);
// res.rowCount = 1

Either of the above inserts data into the product table:

deno-db=# select * from product;
 id | name | price 
----+------+-------
  1 | milk |  5.99
(1 row)

If any insertion error occurs, it’d be raised as PostgresError. For example, an error when duplicate key (id=1) is inserted again:

PostgresError: duplicate key value violates unique constraint "product_pkey"

How to query data from the table?

The queryObject API can also be used to query data from the table. If the query requires, the variables can be passed as array index or object key names. The output is available in the rows array.

Here is the data present in the product table:

deno-db=# select * from product;
 id |    name    | price 
----+------------+-------
  1 | milk       |  5.99
  2 | oats       |  3.99
  3 | strawberry |  7.99
(3 rows)

Querying without condition

const res = await client.queryObject("select * from product");
// res.rows
[
  { id: 1, name: "milk", price: "5.99" },
  { id: 2, name: "oats", price: "3.99" },
  { id: 3, name: "strawberry", price: "7.99" }
]
// res.rowCount  = 3

Querying with condition

const lowestPrice = 4.99;
const res = await client.queryObject(
  `select * from product where price > $1`,
  [lowestPrice],
);
// res.rows
[
  { id: 1, name: "milk", price: "5.99" },
  { id: 3, name: "strawberry", price: "7.99" }
]
// res.rowCount = 2

The queryArray API can also be used. The output of queryArray is an array of arrays. Here is the difference in the output:

const res = await client.queryArray(
  `select * from product where price > $1`,
  [lowestPrice],
);
// res.rows
[ [ 1, "milk", "5.99" ], [ 3, "strawberry", "7.99" ] ]
// res.rowCount = 2

How to remove data from the table?

The same queryObject API can be used to remove data from the table. As always, the variables can be passed by array index or object key names.

const name = "strawberry";
const res = await client.queryArray(
  `delete from product where name = $name`,
  { name },
);
// res.rowCount = 1

The rowCount attribute in the output would indicate affected rows (in other words deleted rows). If no rows match the condition, the rowCount would be 0. If the table doesn’t exist, PostgresError would be thrown.

Let’s delete the same data again:

// res.rowCount = 0

Let’s delete multiple rows:

const lowestPrice = 0.99;
const res = await client.queryArray(
  `delete from product where price > $1`,
  [ lowestPrice ],
);
// res.rowCount = 2

The table is now empty in the database:

deno-db=# select * from product;
 id | name | price 
----+------+-------
(0 rows)