PostgreSQL and Postgres.js

PostgreSQL is a relational database management system that is widely used in web applications. In this course, we mainly run PostgreSQL in a container, as outlined in the part on setting up a walking skeleton.

Relational databases are queried using the Structured Query Language (SQL). If you are not familiar with SQL, you may e.g. use the chatbot to ask for help when forming SQL queries.

When using database functionality in our web applications, we primarily interact with PostgreSQL using the Postgres.js library. The library is taken into use by adding it to the deno.json file of the project, as shown below.

{
  "imports": {
    // other imports
    "postgres": "https://deno.land/x/postgresjs@v3.4.4/mod.js"
  }
}

When importing the Postgres.js library, we import 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 "postgres";

const sql = postgres();

When the database driver is created as shown above, the driver reads database information from environment variables, and uses them for the concrete database connection. It is also possible to specify connection-specific variables explicitly when creating the driver, as shown below.

const sql = postgres('postgres://username:password@host:port/database', {
  host: '', // Postgres ip address[s] or domain name[s]
  port: 5432, // Postgres server port[s]
  database: '', // Name of database to connect to
  username: '', // Username of database user
  password: '', // Password of database user
});

Calling the function postgres() does not open a database connection immediately. Instead, the function call returns a function that can be used to query the database — the concrete connection is formed when the database is queried.

The postgres() function call returns a special kind of function that can be used to query the database. The function — sql — is a “tag function” created using tagged templates. Tagged templates is a feature in JavaScript that allows parsing template literals with a function, which e.g. allows performing any operations to arguments given to the template literal before the template literal is evaluated.

The tag function implementation provided by Postgres.js avoids many of the common SQL injection vulnerabilities that are caused by concatenating strings to form SQL queries.

When using Postgres.js, the tag function “sql” is used for constructing the SQL queries. As an example, if we would wish to create an SQL statement “SELECT 1 + 2 AS sum”, but where the value for the summed variables could vary, we would use the sql object as follows, where the variables are inserted to the query using ${ variable }.

const first = 1;
const second = 2;

const result = await sql`SELECT ${first} + ${second} AS sum`;
console.log(result);

The output of the above program would be an array with an object that has a key sum and a value 3.