Connection Pool

Learning objectives

Understands what happens when we open up a database connection.
Knows what connection pools are and -- on a high level -- how they work.

By default, the Postgres.js uses a connection pool into which connections are lazily added.

If we would not use a connection pool, whenever we make a query, we would open a database connection, make the query, and we close the connection. This can be rather slow.

If the web server and the database server are geologically far from each others, sending a single message may already take tens of milliseconds. When we study the steps outlined above, we may observe that the amount of time that is spent making the actual query is actually only a small part of the overall process.

Could we somehow skip opening and closing the database connection for each request? Connection pools provide one solution to this problem.

Connection pools

As discussed above, quite a bit of time is spent on opening and closing the connections, whereas the most important part -- that is, executing the actual query -- is only a small part of the whole process. A common way to reduce the overhead of creating connections is the use of connection pools.

A connection pool is effectively a set of connections to the database server from the web server, which are opened when the application is started. These connections can be retrieved or "borrowed" from the pool for a moment to execute a query, after which they are returned to the pool.

Question not found or loading of the question is still in progress.

Summarizing application structure

To summarize, at this point, our application structure is as follows.

tree --dirsfirst
.
├── database
│   └── database.js
├── services
│   └── addressService.js
├── views
│   └── index.eta
└── app.js

3 directories, 4 files

The contents of the files are as follows. The file database.js in the folder database:

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

const sql = postgres({});

export { sql };

The file addressService.js in the folder services:

import { sql } from "../database/database.js";

const create = async (name, address) => {
  await sql`INSERT INTO addresses (name, address)
    VALUES (${ name }, ${ address })`;
};

const deleteById = async (id) => {
  try {
    await sql`DELETE FROM addresses WHERE id = ${ id }`;
  } catch (e) {
    console.log(e);
  }
};

const findAll = async () => {
  return await sql`SELECT * FROM addresses`;
};

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

  return await sql`SELECT * FROM addresses
    WHERE name ILIKE ${ namePart } OR address ILIKE ${ namePart }`;
};

export { create, deleteById, findAll, findByNameOrAddressLike };

The file index.eta in the folder views:

<h1>Addresses</h1>

<h2>Add an address</h2>

<form method="POST">
  Name: <input type="text" name="name" /><br/>
  Address: <input type="text" name="address" /><br/>
  <input type="submit" value="Submit!" />
</form>

<h2>Current addresses</h2>

<ul>
<% it.addresses.forEach((address) => { %>
  <li>
    <%= address.name %>, <%= address.address %>
    <form method="POST" action="/delete/<%= address.id %>">
      <input type="submit" value="Delete" />
    </form>
  </li>
<% }); %>
</ul>

And the file app.js in the root folder:

import { serve } from "https://deno.land/std@0.222.1/http/server.ts";
import { configure, renderFile } from "https://deno.land/x/eta@v2.2.0/mod.ts";
import * as addressService from "./services/addressService.js";

configure({
  views: `${Deno.cwd()}/views/`,
});

const responseDetails = {
  headers: { "Content-Type": "text/html;charset=UTF-8" },
};

const redirectTo = (path) => {
  return new Response(`Redirecting to ${path}.`, {
    status: 303,
    headers: {
      "Location": path,
    },
  });
};

const deleteAddress = async (request) => {
  const url = new URL(request.url);
  const parts = url.pathname.split("/");
  const id = parts[2];
  await addressService.deleteById(id);

  return redirectTo("/");
};

const addAddress = async (request) => {
  const formData = await request.formData();

  const name = formData.get("name");
  const address = formData.get("address");

  await addressService.create(name, address);

  return redirectTo("/");
};

const listAddresses = async (request) => {
  const data = {
    addresses: await addressService.findAll(),
  };

  return new Response(await renderFile("index.eta", data), responseDetails);
};

const handleRequest = async (request) => {
  const url = new URL(request.url);
  if (request.method === "POST" && url.pathname.startsWith("/delete/")) {
    return await deleteAddress(request);
  } else if (request.method === "POST") {
    return await addAddress(request);
  } else {
    return await listAddresses(request);
  }
};

serve(handleRequest, { port: 7777 });

Such structure helps in building applications. We'll look into this structure in more detail when discussing Web Application Architecture.

Note that in the following assignment, you are expected to upload a zip file that contains the solution to the assignment. The root of the zip file must contain a file called app.js, which can be used to start the application. The application must be started on the port 7777.