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 (
Treat TypeScript as a first-class language.
Natively support web standard APIs (e.g.,
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.
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 is
POSTGRES_PASSWORD, which corresponds to the superuser password for the PostgreSQL database. We also set the
POSTGRES_USERenvironment variable, which corresponds to the superuser username for the PostgreSQL, to
POSTGRES_DBenvironment variable, which corresponds to the default database's name, to
-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 name
pg_db_containerto 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
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
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
Now, create a SQL file named
seed-customers.sql that inserts three customer records into the
Then, run the following command to populate the database with these three customers:
To verify that the database has been successfully seeded with this data, let's query the database with the
psql interactive shell:
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:
Creating a Simple Web Server in Deno with Oak#
Create a new project folder named
deno-postgres. Within this folder, create a
server.ts, write a simple HTTP server that responds to every request with a "Hello World!" message.
To run the server, execute the following command:
--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.
Re-run the server. The browser only prints the "Hello World!" message for
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
Then, create and initialize a new instance of the PostgreSQL client, like so:
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.
Call the client's
connect method to connect to the database.
Call the client's
end method to terminate the connection to the database.
To ensure the script starts the server only when the client has successfully connected to the PostgreSQL database, perform both
app.listen in a
try block. Any errors that prevent the server from running will terminate the connection to the database.
When you run the server, Deno prompts you to grant access to several PostgreSQL environment variables:
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:
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.
deno-postgres/server.ts file, define a new route on the router for the
GET /customers endpoint.
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."
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
firstNamequery parameter from the URL with
Executes the query with the
client.queryArraymethod. Returns the results of the query as an array (
Sends back a response with the results.
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
$1for the first value,
$2for the second value, etc.
These variables can be provided in an object if referenced in the prepared statement by the variables name. So
$FIRSTNAMEin the prepared statement corresponds to the value of the
$LASTNAMEin the prepared statement corresponds to the value of the
client.queryArray also accepts named arguments.
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
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 (
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
Now, let's check that the prepared statement guards against SQL injection attacks.
Within the browser, visit
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
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.
deno-postgres/server.ts file should look like this:
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.