Deno Postgres

Querying the database

The following example shows a command line application that connects to a database, makes a query to the database, prints the result rows, and then terminates the connection. Database connection is formed using the asynchronous method connect of the client. Once the connection has been formed, we can execute the query -- method queryObject takes the query as a parameter and returns a result object. Once the query has been executed, we close the connection -- this is done with the asynchronous method end.

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client();

await client.connect();
const result = await client.queryObject("SELECT * FROM names;");
await client.end();

console.log(result.rows);

Running the above code produces the following output.

[ { id: 1, name: "Donald" }, { id: 2, name: "Mickey" } ]

We can also iterate over the result data. In the following example, we print only the name property of each object in the rows of the result.

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client();

await client.connect();
const result = await client.queryObject("SELECT * FROM names;");
await client.end();

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

Donald
Mickey

Inserting data

Inserting data to the database is done using the SQL INSERT statement. Let's try out adding a new name to the database.

In the following example, we first add a new name to the database, after which we query for all the names in the database.

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client();

await client.connect();
await client.queryObject("INSERT INTO names (name) VALUES ('Goofy');");
await client.end();

await client.connect();
const result = await client.queryObject("SELECT * FROM names;");
await client.end();

result.rows.forEach((row) => {
  console.log(row.name);
});

After running the above application, as we can see, the database now contains three names.

Donald
Mickey
Goofy

Prepared statements

In the previous examples, when we inserted a name to the database, the name was directly written into the SQL statement.

When we want to add arbitrary strings (or content) to a database query, we use prepared statements (also known as parameterized queries). Prepared statements are pre-defined SQL statements with slots for parameters that need to be inserted when running the query.

When working with the deno postgres client, prepared statements are defined so that the parameters are marked with a dollar sign and a name with which the parameter will be identified with, e.g. $id. When making a query using prepared statements, the query function is given two parameters; (1) the query as a string and (2) an object that contains the data that will be inserted into the query.

The following example shows how prepared statements are used. The example would insert the name 'Daisy' to the names table in the database.

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client();

let name = "Daisy";

await client.connect();
await client.queryArray(
  "INSERT INTO names (name) VALUES ($name);",
  { name: name },
);
await client.end();

Note that the name of the parameter defined in the query must be present in the object. In the following example, the query function expects a parameter called n, which is present in the object. Thus, the query works.

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client();

let name = "Daisy";

await client.connect();
await client.queryArray(
  "INSERT INTO names (name) VALUES ($n);",
  { n: name },
);
await client.end();

On the other hand, in the following example, the query expects a parameter called name, but the object has a property called n. Thus, the query does not work.

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client();

let name = "Daisy";

await client.connect();
await client.queryArray(
  "INSERT INTO names (name) VALUES ($name);",
  { n: name },
);
await client.end();

Prepared statements are used whenever adding data to the query. The example below shows an application that searches for a given name.

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client();

const name = "Goofy";

await client.connect();
const result = await client.queryObject(
  "SELECT * FROM names WHERE name = $name;",
  { name: name },
);
await client.end();

result.rows.forEach((row) => {
  console.log(row.name);
});

Goofy

And the following example shows an application where a name 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 names that include the character o.

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client();

const likePart = "%o%";

await client.connect();
const result = await client.queryObject(
  "SELECT * FROM names WHERE name LIKE $part;",
  { part: likePart },
);
await client.end();

result.rows.forEach((row) => {
  console.log(row.name);
});

Donald
Goofy

Multiple parameters in prepared statements

When using multiple parameters, each parameter is given a specific name that distinguishes the parameter. Typically, the names are descriptive -- that is, when for example adding a name to a database, the parameter is called $name. Similarly, when looking for something based on an id, the parameter is called $id. This practice helps interpreting the queries.

In the following example, we access a database table called addresses, which is created as follows.

CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL
);

Let's create functionality for adding an address and for searching addresses based on names and addresses. It is meaningful to store such functionality into a separate file -- let's call the file addressService.js, i.e. a service that allows us to work with addresses, and place it into a folder called services (relative to the app.js file). The file contains two functions, create and findByNameOrAddressLike. These are implemented as follows.

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client();

const create = async (name, address) => {
  await client.connect();
  await client.queryObject(
    "INSERT INTO addresses (name, address) VALUES ($name, $address);",
    { name: name, address: address },
  );
  await client.end();
};

const findByNameOrAddressLike = async (nameOrAddress) => {
  const likePart = `%${nameOrAddress}%`;

  await client.connect();
  const result = await client.queryObject(
    "SELECT * FROM addresses WHERE name ILIKE $namePart OR address ILIKE $addressPart;",
    { namePart: likePart, addressPart: likePart },
  );
  await client.end();

  return result.rows;
};

export { create, findByNameOrAddressLike };

Note that in the above example, the database client is also defined in the addressService.js file. The ILIKE syntax above is PostgreSQL specific and allows case-insensitive queries. In addition, the function findByNameOrAddressLike returns the rows object from the result, which means that the callee of that function does not have access to the result itself.

Now, the address table could be accessed as follows. In the following example, we first create two addresses, after which we first search for entries that have daisy in either the name or the address, and then search for entries that have duckb in either the name or the address.

import * as addressService from "./services/addressService.js";

await addressService.create("Donald Duck", "Duckburg");
await addressService.create("Daisy Duck", "Duckburg");

let result = await addressService.findByNameOrAddressLike("daisy");
console.log(result);

result = await addressService.findByNameOrAddressLike("duckb");
console.log(result);

[
  { id: 2, name: "Daisy Duck", address: "Duckburg" }
]
[
  { id: 1, name: "Donald Duck", address: "Duckburg" },
  { id: 2, name: "Daisy Duck", address: "Duckburg" }
]