Kickstart Your Next Project with Deno and PostgreSQL

Web Applications
PostgreSQL
May 20th, 2022

Responses (0)

Clap
1|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.

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_USER environment variable, which corresponds to the superuser username for the PostgreSQL, to admin and the POSTGRES_DB environment variable, which corresponds to the default database's name, to bank_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 name pg_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:

(create-db.sql)

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:

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.

(seed-customers.sql)

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 file.

Within server.ts, write a simple HTTP server that responds to every request with a "Hello World!" message.

(deno-postgres/server.ts)

To run the server, execute the following command:

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)

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.

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.

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.

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 client.connect and 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:

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:

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.

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:

helpers provides APIs for managing router contexts.

Then, using the client.queryArray method, write a route handler for the GET /customers endpoint that...

  1. Grabs the value of the firstName query parameter from the URL with getQuery.

  2. Executes the query with the client.queryArray method. Returns the results of the query as an array (rows).

  3. 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 the firstName variable, $LASTNAME in the prepared statement corresponds to the value of the lastName variable, etc.

Note: client.queryArray also accepts named arguments.

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.

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)

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.

Sources#


Clap
1|0