Database Queries with Postgres.js

Here, we'll look in a bit more detail into working with PostgreSQL and Postgres.js -- the former is a relational database and the latter is a database driver.

Here, we'll first go over the basics, after which we'll look into these in the context of web applications. When working on these in the context of web applications, you need to have the docker compose setup that was built in the chapter Application Containerization for this chapter and many of the subsequent chapters.

Creating a driver

The Postgres.js library is imported from https://deno.land/x/postgresjs@v3.4.4/mod.js in a similar way as any other Deno dependency. The import statement imports a function that can be used to create a database driver that can then be used to query a database. Importing the library and creating a driver looks as follows.

import postgres from "https://deno.land/x/postgresjs@v3.4.4/mod.js";

const sql = postgres();

When the database driver is created as shown above, the driver reads database information from environmental variables, and uses them for the concrete connection. The function call postgres(); does not open a database connection immediately -- a connection is formed when the database is queried.

Database queries

Postgres.js uses tagged template literals for constructing the SQL query. In essence, Postgres.js provides a sql function (or, more precisely, we call the function sql), which is used to parse template literals. The function enforces safe query generation using inferred types.

In essence, when Postgres.js is used as intended, SQL injection vulnerabilities are a lot harder to add to the code. We'll discuss SQL injection vulnerabilities in the chapter on Web Security Basics.

The following example shows a command line application that makes a query to the database. The query asks for all the data in the table todos, and logs the resulting rows to the console.

import postgres from "https://deno.land/x/postgresjs@v3.4.4/mod.js";

const sql = postgres();

const rows = await sql`SELECT * FROM todos`;

console.log(rows);

The output of the above program depends on the database contents. The following provides one possible output.

[ { "id":1, "todo":"Learn SQL" }, { "id":2, "todo":"Learn Docker" } ]

Inserting data to a database

Data is inserted to the database with the SQL INSERT statement. The SQL INSERT statement includes information on which table the data is inserted to, which columns the data is inserted to, and what the data is. As an example, the following query would add a row to a table called todos, inserting a value to the column todo, and using Learn more SQL as the value.

INSERT INTO todos (todo) VALUES ('Learn more SQL');

Even though the table todos has a column id, we do not explicitly specify a value for the column. This is because the column has been created as a SERIAL column, which means that the PostgreSQL database is responsible for specifying the value for the id column.

Don't be a SERIAL killer by trying to specify values for an id column. 🥁🤦

A program that would add the above row to the database would look as follows.

import postgres from "https://deno.land/x/postgresjs@v3.4.4/mod.js";

const sql = postgres();

await sql`INSERT INTO todos (todo) VALUES ('Learn more SQL')`;

Whenever the above program is run, a new row is added to the todos table.

Query parameters

Query parameters allow adding values from variables to the queries. Query parameters are added to the queries using template literal format ${ variable }. As an example, if we would have a variable called todo, we could change the above insert statements as follows.

const todo = "Template Literals?";

await sql`INSERT INTO todos (todo) VALUES (${ todo })`;

Consequently, a full program that would insert a todo to the database table based on a variable would look as follows.

import postgres from "https://deno.land/x/postgresjs@v3.4.4/mod.js";

const sql = postgres();

const todo = "Template Literals?";

await sql`INSERT INTO todos (todo) VALUES (${ todo })`;

Query parameters are used similarly when querying for information. The example below shows an application that searches for a given todo.

import postgres from "https://deno.land/x/postgresjs@v3.4.4/mod.js";

const sql = postgres();

const todo = "Learn SQL";

const rows = await sql`SELECT * FROM todos WHERE todo = ${ todo }`;

console.log(rows);

[ { "id":1, "todo":"Learn SQL" } ]

It is also possible to search with wildcards when working with PostgreSQL. The following example shows an application where a todo is searched for using the LIKE keyword. In the example below, the % sign allows any number of any characters -- effectively, in the example below, we search for all the todos that include the string SQL.

import postgres from "https://deno.land/x/postgresjs@v3.4.4/mod.js";

const sql = postgres();

const part = "SQL";

const rows = await sql`SELECT * FROM todos WHERE todo LIKE ${ '%' + part + '%' }`;

console.log(rows);

[ { "id":1, "todo":"Learn SQL" }, { "id":2, "todo":"Learn Docker" }, { "id":3, "todo":"Learn more SQL" } ]

Iterating query results

The query result is an array consisting of objects that represent the rows retrieved from the database. Naturally, this array can also be iterated over. In the following example, we print only the todo property of each object in the rows of the result.

import postgres from "https://deno.land/x/postgresjs@v3.4.4/mod.js";

const sql = postgres();

const part = "SQL";

const rows = await sql`SELECT * FROM todos WHERE todo LIKE ${ '%' + part + '%' }`;

rows.forEach((obj) => {
  console.log(obj.todo);
});

Learn SQL
Learn Docker
Learn more SQL