Kickstart Your Next Project with Deno and PostgreSQL
Responses (0)
Most modern web applications rely on a relational database management system (RDBMS) to store and retrieve troves of related data. When you bring together two modern, open-source technologies, Deno and PostgreSQL, you can build production-grade web applications that...
Run in a secure sandbox environment. You must pass certain security flags to Deno to explicitly grant permissions like network access (
--allow-net
).Treat TypeScript as a first-class language.
Natively support web standard APIs (e.g.,
fetch
).Can process large volumes of data with ACID compliant, concurrent transactions and extensions like PostGIS.
Since Deno was built as an improved, alternative V8 runtime to Node.js, Deno projects can use both the third-party modules developed specifically to work with Deno and the many third-party packages that exist in the npm ecosystem. A popular PostgreSQL client for Node.js projects is node-postgres
. If you visit the deno.land/x website and search postgres
, then you will come across deno-postgres
, a lightweight PostgreSQL driver that was developed specifically for Deno.
The API and underlying implementation of deno-postgres
is based on two popular PostgreSQL drivers: node-postgres
(coincidentally) and pq
, a Go PostgreSQL driver. deno-postgres
provides abstractions for features and operations that allow you to unleash the full potential of PostgreSQL: connection pools, prepared statements, transactions and typed queries.
Below, I'm going to show you how to...
Connect a PostgreSQL database to Deno.
Execute queries securely with prepared statements.
Installation and Setup#
Setting Up the PostgreSQL Database#
To get started, let's use Docker to spin up a new PostgreSQL database on the local machine.
$ docker run --rm -p 5433:5432 -e POSTGRES_PASSWORD=password123 -e POSTGRES_USER=admin -e POSTGRES_DB=bank_db --name pg_db_container postgres

Running this command creates a new Docker container based on the official postgres
Docker image. The docker run
command accepts several options and flags that override image and Docker runtime defaults.
-e
- Short for--env
. Tells Docker to set environment variables in the container. The only required environment variable that must be provided to use the PostgreSQL image isPOSTGRES_PASSWORD
, which corresponds to the superuser password for the PostgreSQL database. We also set thePOSTGRES_USER
environment variable, which corresponds to the superuser username for the PostgreSQL, toadmin
and thePOSTGRES_DB
environment variable, which corresponds to the default database's name, tobank_db
.-p
- Short for--publish
. Tells Docker to expose port 5432 inside the container to port 5433 outside the container. It maps the local machine's port 5433 to the Docker container's port 5432.--rm
- Tells Docker to automatically clean up the container and remove the container's file system upon killing the process (when the container exits).--name
- Tells Docker to assign the namepg_db_container
to the container.
For this tutorial, the database's schema design is based on a bank. The schema consists of three tables:
customers
- Bank customers.accounts
- Bank accounts. Each customer can have a checking account and/or a savings account.transactions
- Bank transactions. Each time a customer deposits, withdraws or transfers money, the balances of the affected accounts get updated based on the transaction amount.
To create tables in the database, let's run a SQL file directly against the pg_db_container
container. With this approach, we never have to copy the SQL file from the host machine to the container.
First, create a SQL file named create-db.sql
that creates the customers
, accounts
and transactions
tables:
$ touch create-db.sql
(create-db.sql
)
BEGIN;
CREATE TYPE account_type AS ENUM ('checking', 'savings');
CREATE TYPE transaction_status AS ENUM ('pending', 'completed', 'failed');
CREATE TYPE transaction_type AS ENUM ('withdrawal', 'deposit', 'transfer');
CREATE TABLE customers (
id UUID DEFAULT gen_random_uuid(),
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
CONSTRAINT customer_key PRIMARY KEY (id)
);
CREATE TABLE accounts (
id UUID DEFAULT gen_random_uuid(),
"number" VARCHAR(10) DEFAULT to_char(floor(random() * 10000000000)::bigint, 'fm0000000000'),
customer_id UUID REFERENCES customers (id) NOT NULL,
"type" account_type NOT NULL,
balance INT DEFAULT 0,
CONSTRAINT account_key PRIMARY KEY (id, "number"),
UNIQUE ("number")
);
CREATE TABLE transactions (
id UUID DEFAULT gen_random_uuid(),
to_acct_number VARCHAR(10) REFERENCES accounts ("number"),
from_acct_number VARCHAR(10) REFERENCES accounts ("number"),
amount INT NOT NULL,
posted_on DATE NOT NULL,
"status" transaction_status NOT NULL,
"type" transaction_type NOT NULL,
CONSTRAINT transaction_key PRIMARY KEY (id)
);
COMMIT;
Then, run the following command, which pipes the contents of the create-db.sql
SQL file (as a command string) to the psql
command that's ran within the pg_db_container
container:
$ cat ./create-db.sql | docker exec -i pg_db_container psql -U admin -d bank_db

Alternatively, you can create a Dockerfile
that extends the postgres
image and copies the create-db.sql
file from the host machine to the container's /docker-entrypoint-initdb.d
directory. All scripts within this directory are ran at the time the container starts up (and the container's data directory is also empty).
Note: Do not adopt this schema for production. The database does not fully reflect what actually happens in the real world. For example, it does not account for the differences in the number of digits a checking account number has (commonly ) and a savings account number has (commonly ). Also, for simplicity's sake, balance calculations only involve integer values. Remember, the main focus of this tutorial is to demonstrate the capabilities of deno-postgres
.
Now, create a SQL file named seed-customers.sql
that inserts three customer records into the customers
table.
$ touch seed-customers.sql
(seed-customers.sql
)
BEGIN;
INSERT INTO customers (first_name, last_name) VALUES ('Alice', 'King');
INSERT INTO customers (first_name, last_name) VALUES ('Bob', 'Wong');
INSERT INTO customers (first_name, last_name) VALUES ('Chris', 'Lopez');
COMMIT;
Then, run the following command to populate the database with these three customers:
$ cat ./seed-customers.sql | docker exec -i pg_db_container psql -U admin -d bank_db

To verify that the database has been successfully seeded with this data, let's query the database with the psql
interactive shell:
$ psql -h localhost -p 5433 -U admin -d bank_db
You will be prompted to enter the superuser's password. Once entered, the terminal launches the psql
interactive shell, which runs inside the PostgreSQL database server.

Enter the following SQL SELECT
statement to query the customers
table for the seeded data:
SELECT * FROM customers;

Creating a Simple Web Server in Deno with Oak#
Create a new project folder named deno-postgres
. Within this folder, create a server.ts
file.
$ mkdir deno-postgres && cd deno-postgres
$ touch server.ts
Within server.ts
, write a simple HTTP server that responds to every request with a "Hello World!" message.
(deno-postgres/server.ts
)
import { Application } from 'https://deno.land/x/[email protected]/mod.ts';
const app = new Application();
app.use((ctx) => {
ctx.response.body = "Hello World!";
});
await app.listen({ port: 8000 });
To run the server, execute the following command:
$ deno run --allow-net server.ts
The --allow-net
option grants network access to the Deno script.

When you visit http://localhost:8000/
(or any URL beginning with http://localhost:8000/
) in your browser, you will find the "Hello World!" message printed on the page.
Oak is an HTTP middleware framework for Deno that includes a router middleware. Both the framework and router are inspired by Koa, so if you have any prior experience working with Koa, then many aspects of the code should look quite familiar.
Let's modify the server to only respond to requests sent to a GET /
endpoint by enabling routing.
(deno-postgres/server.ts
)
import { Application, Router } from 'https://deno.land/x/[email protected]/mod.ts';
const router = new Router();
router
.get("/", (context) => {
context.response.body = "Hello World!";
});
const app = new Application();
app.use(router.routes());
app.use(router.allowedMethods());
await app.listen({ port: 8000 });
Re-run the server. The browser only prints the "Hello World!" message for http://localhost:8000/
.
As we progress in this tutorial, each new route (and route handler) we add to this router will demonstrate the features and operations that deno-postgres
comes with.
Connecting to the PostgreSQL Database#
To connect to the PostgreSQL database, let's import the PostgreSQL client from deno-postgres
.
import { Client } from "https://deno.land/x/[email protected]/mod.ts";
Then, create and initialize a new instance of the PostgreSQL client, like so:
const client = new Client({
user: "admin",
password: "password123",
database: "bank_db",
hostname: "localhost",
port: 5433,
});
Each PostgreSQL client instance manages a connection to a single PostgreSQL database. The configuration options we pass to Client
tell the driver everything it needs to know to connect to the PostgreSQL database, such as the database's hostname and the credentials of the superuser.
You can find more options here. For encrypted connections to the database via SSL/TLS, click here.
Call the client's connect
method to connect to the database.
await client.connect();
Call the client's end
method to terminate the connection to the database.
await client.end();
To ensure the script starts the server only when the client has successfully connected to the PostgreSQL database, perform both client.connect
and app.listen
in a try
block. Any errors that prevent the server from running will terminate the connection to the database.
try {
await client.connect();
await app.listen({ port: 8000 });
} catch (err) {
await client.end();
console.error(err);
}
When you run the server, Deno prompts you to grant access to several PostgreSQL environment variables:
Deno requests env access to "<PG_ENV_VAR>." Run again with --allow-env to bypass this prompt.
Allow? [y/n (y = yes allow, n = no deny)]






Press y
to grant access for each requested environment variable. To skip this series of prompts, run the Deno script with the --allow-env
flag, like so:
deno run --allow-net --allow-env server.ts
If the terminal shows no error messages, and the server returns a "Hello World!" message for a GET /
request, then the server has successfully connected to the dockerized PostgreSQL database.
Executing Queries with Prepared Statements#
The recommended way to execute queries is with prepared statements, especially if your application accepts user input. Prepared statements protect your PostgreSQL database from SQL injection attacks. Since the SQL code (statement template) is pre-compiled, prepared statements are also efficient.
The statement template consists of a base SQL statement with placeholders for bind variables.
Let's write our first prepared statement for querying the customers table
based on the customer's first name.
Within the deno-postgres/server.ts
file, define a new route on the router for the GET /customers
endpoint.
router
.get("/", (context) => {
context.response.body = "Hello World!";
})
.get("/customers", async (context) => {});
This endpoint will return customers from the customers
table based on a query parameter, firstName
. Using this query parameter, we can tell the query to only find customers whose first name matches the value of firstName
. For example, firstName=alice
would filter the table for any customer who has a first name of "Alice."
To extract query parameters from the URL, import the getQuery
method from Oak's helpers
module, like so:
import { helpers } from "https://deno.land/x/[email protected]/mod.ts";
const { getQuery } = helpers;
helpers
provides APIs for managing router contexts.
Then, using the client.queryArray
method, write a route handler for the GET /customers
endpoint that...
Grabs the value of the
firstName
query parameter from the URL withgetQuery
.Executes the query with the
client.queryArray
method. Returns the results of the query as an array (rows
).Sends back a response with the results.
The client.queryArray
method executes the query and returns the results of the query as an array. It can accept, at most, two arguments:
If only one argument is passed, then the argument must be a SQL statement.
If two arguments are passed, then the first argument must be a prepared statement and the second argument must be bind variables.
These variables can be provided in an array if referenced in the prepared statement as
$1
for the first value,$2
for the second value, etc.These variables can be provided in an object if referenced in the prepared statement by the variables name. So
$FIRSTNAME
in the prepared statement corresponds to the value of thefirstName
variable,$LASTNAME
in the prepared statement corresponds to the value of thelastName
variable, etc.
Note: client.queryArray
also accepts named arguments.
router
.get("/", (context) => {
context.response.body = "Hello World!";
})
.get("/customers", async (context) => {
const { firstName = "%" } = getQuery(context);
const result = await client.queryArray(
"SELECT id, first_name, last_name FROM customers WHERE first_name ILIKE $1",
[firstName]
);
context.response.body = result;
});
In the WHERE
clause, ILIKE
is used for case-insensitive string matching. A %
sign in the pattern matches for any sequence of characters. So if the URL does not contain a firstName
query parameter (or it's set explicitly to an empty string), then the ILIKE
will match for any first_name
.
Note: Don't use %
as a default value for ILIKE
conditions in production. This is just a quick and easy implementation that ensures all customers are returned if the user decides not to provide any query parameters.
Re-run the server. When you visit http://localhost:8000/customers?firstName=alice
in a browser, you will see the results of the query.

Note: You can use cURL
or an application like Postman to send the request. The above is shown in a Chrome browser using the JSONView extension.
In this case, because the customers
table had a customer whose first name is "Alice," that record (id
, firstName
and lastName
) was returned as an array of values. The first value corresponds to the first column id
, the second value corresponds to the second column firstName
, and the third value corresponds to the third column lastName
.
Now, let's check that the prepared statement guards against SQL injection attacks.
Within the browser, visit http://localhost:8000/customers?firstName=alice%20OR%201%3D1
.
If the value of the firstName
query parameter was directly placed within the SQL statement, then the SQL statement would be SELECT id, first_name, last_name FROM customers WHERE first_name ILIKE 'alice' OR 1=1
. With the OR
operator and 1=1
condition, the WHERE
clause always evaluates to true
. You would expect all the rows in the customers
table to be leaked.
However, that's not what happens:

Yet, if use psql
to execute this same SQL statement, then all the rows in the customers
table are leaked.

The client correctly executes the query as SELECT id, first_name, last_name FROM customers WHERE first_name ILIKE 'alice OR 1=1'
, not SELECT id, first_name, last_name FROM customers WHERE first_name ILIKE 'alice' OR 1=1
.
Let's add another query parameter, lastName
, to find customers whose last name matches the value of lastName
.
router
.get("/", (context) => {
context.response.body = "Hello World!";
})
.get("/customers", async (context) => {
const { firstName = "%", lastName = "%" } = getQuery(context);
const result = await client.queryArray(
"SELECT id, first_name, last_name FROM customers WHERE first_name ILIKE $1 AND last_name ILIKE $2",
[firstName, lastName]
);
context.response.body = result;
});
Re-run the server. When you visit http://localhost:8000/customers?firstName=alice&lastName=king
in a browser, you will see the results of the query.

Note: There's also a queryObject
method, which accepts the same arguments as the queryArray
method, but returns the response as an object with the column names as keys.
Altogether, the deno-postgres/server.ts
file should look like this:
(deno-postgres/server.ts
)
import {
Application,
Router,
helpers,
} from "https://deno.land/x/[email protected]/mod.ts";
import { Client } from "https://deno.land/x/[email protected]/mod.ts";
const { getQuery } = helpers;
const client = new Client({
user: "admin",
password: "password123",
database: "bank_db",
hostname: "localhost",
port: 5433,
});
const router = new Router();
router
.get("/", (context) => {
context.response.body = "Hello World!";
})
.get("/customers", async (context) => {
const { firstName = "%", lastName = "%" } = getQuery(context);
const result = await client.queryArray(
"SELECT id, first_name, last_name FROM customers WHERE first_name ILIKE $1 AND last_name ILIKE $2",
[firstName, lastName]
);
context.response.body = result;
});
const app = new Application();
app.use(router.routes());
app.use(router.allowedMethods());
try {
await client.connect();
await app.listen({ port: 8000 });
} catch (err) {
await client.end();
console.error(err);
}
Next Steps#
Try using PostgreSQL and Deno for your next project!
To learn how to create and deploy a production-ready, REST API with Deno from scratch, check out Halvard Mørstad's course here. Halvard is a software engineer and is the author of Nessie, a modular database migration tool for Deno.
