Forem: Gajus Kuizinas

Integrating Fastify with Slonik

Gajus Kuizinas — Sat, 30 Sep 2023 00:09:02 +0000

For those unfamiliar, Slonik is a robust SQL query building and execution library designed for Node.js. Its primary purpose is to provide a safe and convenient way to compose and execute SQL queries. Now, let's delve into how it works in conjunction with Fastify, a high-performance web framework for Node.js.

Install the Required Packages

To get started, you'll need to install the necessary packages:

npm install slonik fastify

Basic App Setup

Let's create a basic Fastify app:

import fastify from 'fastify';

const app = fastify({ logger: true });
const port = 3000;

app.get('/', async (request, reply) => {
  reply.send({ message: 'Hello, World!' });
});

app.listen(port, (err, address) => {
  if (err) {
    app.log.error(err);
    process.exit(1);
  }
  app.log.info(`Server listening on ${address}`);
});

Running this app will start a server on port 3000. If you visit http://localhost:3000, you should see the message "Hello, World!".

Connecting to a Database

Slonik handles database connections using connection pools. A connection pool efficiently manages a set of database connections, minimizing the overhead of creating new connections.

Here is an example of how to create a Slonik connection pool:

import { createPool } from 'slonik';

const main = async () => {
  await createPool('postgres://...');
};

main();

Note: Replace 'postgres://...' with your actual connection URI.

You may have noticed that we are awaiting the result of createPool. Slonik queries the database during the connection pool setup, requiring us to modify our app slightly.

Using Slonik with Fastify

Let's see how to use Slonik with Fastify:

import fastify from 'fastify';
import { createPool } from 'slonik';

const main = async () => {
  const pool = await createPool('postgres://...');

  const app = fastify({ logger: true });
  const port = 3000;

  app.get('/', async (request, reply) => {
    reply.send({ message: 'Hello, World!' });
  });

  app.listen(port, (err, address) => {
    if (err) {
      app.log.error(err);
      process.exit(1);
    }
    app.log.info(`Server listening on ${address}`);
  });
};

void main();

We've made a few adjustments to our app. First, we created a connection pool using the createPool function. Then, we added a main function that creates the connection pool and starts the Fastify server. This wrapping of the code in a function is necessary because we need to await the createPool function, and you can't use await at the top level.

Integrating Slonik with a Fastify Route

With the connection pool in place, it's straightforward to execute queries within your Fastify routes:

app.get('/users', async (request, reply) => {
  const users = await pool.any(sql.unsafe`SELECT * FROM users`);

  reply.send(users);
});

Here, we're fetching all users from the database and sending them as a JSON response.

Thanks to the connection pool, you don't need to worry about opening and closing connections manually. Slonik handles this for you. For most use cases, this is the recommended way to execute queries. However, if you need more control over the connection, you can explore the transaction or connect methods.

Transactions

Slonik provides a transaction method that allows you to execute queries within the same connection and a single transaction:

await pool.connect(async (connection) => {
  await connection.query(sql.unsafe`INSERT INTO logs (message) VALUES ('foo')`);
  await connection.query(sql.unsafe`INSERT INTO logs (message) VALUES ('bar')`);
});

With this, both insertions either succeed together or fail together.

Using Transactions with Fastify

Let's see how we can use transactions with Fastify. First, we'll create a route that inserts a user into the database:

app.post('/users', async (request, reply) => {
  const { name } = request.body;

  await pool.query(sql.unsafe`INSERT INTO users (name) VALUES (${name})`);

  reply.send({ message: 'User created!' });
});

If it's just a single query, you don't need to use a transaction. However, let's say we want to log the creation of the user in the database:

app.post('/users', async (request, reply) => {
  const { name } = request.body;

  await pool.transaction(async (transaction) => {
    await transaction.query(sql.unsafe`INSERT INTO users (name) VALUES (${name})`);
    await transaction.query(sql.unsafe`INSERT INTO logs (message) VALUES (${name} was created!)`);
  });

  reply.send({ message: 'User created!' });
});

Here, we're using the transaction method to execute two queries within the same connection and a single transaction. This ensures that both queries either succeed together or fail together. If either query fails, the transaction is rolled back, and an error is thrown.

Optimizing Transactions

This section is tangential to the main topic of this article, but it's important to mention.

One key principle when working with transactions in relational databases is to keep them as concise as possible. Transactions that extend for long durations can lead to various issues.

Extended transaction durations can block other operations, increasing contention for resources. This can escalate to more severe problems such as deadlocks. In a deadlock scenario, two or more transactions wait indefinitely for each other to release locks.

Such problems can significantly degrade the system's performance. In situations with high concurrency, bottlenecks can emerge, hampering the smooth operation of the database.

Moreover, there's a risk of long-running transactions exhausting connection pool resources. When this happens, it can result in application slowdowns as different parts of the application queue up, waiting for available connections.

In light of these potential pitfalls, it's crucial to ensure that transactions are designed to encompass only the necessary operations. They should be optimized for both speed and efficiency. By adhering to these principles, you maintain the transaction's integrity while ensuring the responsiveness and scalability of the broader application and database system.

In short, keep your transactions short and focused.

Reserving a

Connection

Warning: Reserving a connection from the pool is an advanced feature. It's recommended to use transactions instead.

The connect method allows you to reserve a connection from the pool and execute queries on it.

It's important to note that this is a rare use case, especially in the context of services that produce responses to user requests. Most of the time, you should use connections from the connection pool or transactions instead. However, there are cases where you may need to reserve a connection from the pool. For example, you may want to set the time zone for the connection:

app.get('/events', async (request, reply) => {
  const events = await pool.connect(async (connection) => {
    await connection.query(sql.unsafe`SET LOCAL timezone = 'America/New_York'`);

    return await connection.any(sql.unsafe`SELECT event_name, event_time FROM events`);
  });

  reply.send(events);
});

Here, we are reserving a connection from the pool and executing two queries on it. The first query sets the time zone for the connection, and the second query fetches all events from the database. Once the connection is released, it returns to the pool.

Passing a Connection to a Function

A common question I am asked is how to pass a connection to a function. For example, let's say you have a function that fetches all users from the database:

const getUsers = async () => {
  return await connection.any(sql.unsafe`SELECT * FROM users`);
};

app.get('/users', async (request, reply) => {
  const users = await getUsers();

  reply.send(users);
});

The simple solution is to pass the connection as an argument:

const getUsers = async (pool) => {
  return await pool.any(sql.unsafe`SELECT * FROM users`);
};

app.get('/users', async (request, reply) => {
  const users = await getUsers(pool);

  reply.send(users);
});

I realize that this is a very simple example, but this principle holds even if your function requiring access to the database handle is nested deep within a call stack. You must pass an instance of the connection pool down to every function in the chain.

Anti-Patterns: AsyncLocalStorage

AsyncLocalStorage is a core module introduced in Node.js that provides a mechanism to store and retrieve data based on the current execution context. It might be tempting to use this to pass a connection to a function:

import fastify from 'fastify';
import { createPool } from 'slonik';
import { AsyncLocalStorage } from 'async_hooks';

const asyncLocalStorage = new AsyncLocalStorage();

const main = async () => {
  const pool = createPool('postgres://...');

  const app = fastify({ logger: true });
  const port = 3000;

  app.decorate('pool', pool);

  app.addHook('onRequest', (request, reply, done) => {
    pool.connect((err, connection) => {
      if (err) {
        return done(err);
      }
      asyncLocalStorage.run({ connection }, done);
    });
  });

  app.get('/users', async (request, reply) => {
    const connection = asyncLocalStorage.getStore().connection;
    const users = await connection.any(sql.unsafe`SELECT * FROM users`);
    reply.send(users);
  });

  app.listen(port, (err, address) => {
    if (err) {
      app.log.error(err);
      process.exit(1);
    }
    app.log.info(`Server listening on ${address}`);
  });
};

void main();

I strongly recommend against this approach. It's not a good idea in terms of performance and design. If you need to pass a connection to a function, you should pass it as an argument.

Refer to the Passing a Connection to a Function section for more information.

Anti-Patterns: Wrapping the Entire Request in a Transaction

Another anti-pattern I see is wrapping the entire request in a transaction:

app.post('/users', async (request, reply) => {
  await pool.transaction(async (transaction) => {
    // Do something that is part of the request but not part of the transaction
    await foo();

    await transaction.query(sql.unsafe`INSERT INTO users (name) VALUES ('foo')`);

    reply.send(
      await transaction.one(sql.unsafe`SELECT * FROM users WHERE name = 'foo'`)
    );
  });
});

This is not a good idea. Transactions should be as short as possible. If you need to perform something that is not part of the transaction, you should do it outside of the transaction.

app.post('/users', async (request, reply) => {
  await foo();

  await pool.transaction(async (transaction) => {
    await transaction.query(sql.unsafe`INSERT INTO users (name) VALUES ('foo')`);

    reply.send(
      await transaction.one(sql.unsafe`SELECT * FROM users WHERE name = 'foo'`)
    );
  });
});

Refer to the Optimizing Transactions section for more information.

Anti-Patterns: Wrapping the Entire Request in a Connection

Another anti-pattern I see is wrapping the entire request in a connection:

app.post('/users', async (request, reply) => {
  await pool.connect(async (connection) => {
    reply.send(await foo());
  });
});

This is not a good idea. Implementing this pattern means that your application will be able to handle only as many concurrent requests as there are connections in the connection pool. If you have a connection pool with 10 connections, your application will be able to handle only 10 concurrent requests.

Instead, you should pass an instance of the connection pool to the function that needs access to the database handle and use the connection pool to execute queries and transactions.

Refer to the Passing a Connection to a Function section for more information.

Conclusion

By now, you should have a solid understanding of how to integrate Slonik with Fastify in TypeScript. If you have any questions, feel free to reach out to me on Twitter or leave an issue on GitHub.

Integrating Slonik with Express.js

Gajus Kuizinas — Fri, 29 Sep 2023 23:54:46 +0000

What is Slonik?

For those uninitiated, Slonik is a battle-tested SQL query building and execution library for Node.js. Its primary goal is to allow you to write and compose SQL queries in a safe and convenient way. Now, let's see how it pairs with Express.js.

Install the Necessary Packages

First things first, you'll need to install the necessary packages:

npm install slonik express

Basic App Setup

Let's create a basic Express app:

import express from 'express';

const app = express()
const port = 3000

app.get('/', (req, res) => {
  res.send('Hello, World!')
})

app.listen(port, () => {
  console.log(`Example app listening on port ${port}`)
})

Running this app will start a server on port 3000. If you visit http://localhost:3000, you should see the message "Hello, World!".

Connecting to a Database

Slonik manages database connections using connection pools. A connection pool manages a set of database connections, which can be used and reused, minimizing the overhead of creating new connections.

Here is an example of how to create a Slonik connection pool:

import { createPool } from 'slonik';

const main = async () => {
  await createPool('postgres://...');
};

main();

Note: Your connection URI goes in place of postgres://....

You may have noticed that we are awaiting for the result of createPool. This is because Slonik needs to query the database in order to setup the connection pool. This requires us to slightly modify our app.

Using Slonik with Express.js

Let's see how we can use Slonik with Express.js:

import express from 'express';
import { createPool } from 'slonik';

const main = async () => {
  const pool = await createPool('postgres://...');

  const app = express();
  const port = 3000;

  app.get('/', (req, res) => {
    res.send('Hello, World!')
  });

  app.listen(port, () => {
    console.log(`Example app listening on port ${port}`)
  });
};

void main();

We've added a few lines to our app. First, we created a connection pool using the createPool function. Then, we added a main function that creates the connection pool and starts the Express.js server. The only reason we need to do this is because we need to await the createPool function. Since we can't use await at the top level, we need to wrap it in a function.

Integrating Slonik with an Express.js Route

With the pool ready, it's straightforward to execute queries within your Express routes:

app.get('/users', async (req, res) => {
  const users = await pool.any(sql.unsafe`SELECT * FROM users`);

  res.json(users);
});

Here, we're fetching all users from the database and sending them as a JSON response.

Because we are using a connection pool to manage connections, we don't need to worry about opening and closing connections. Slonik handles this for us. For most use cases, this is the recommended way to execute queries. However, if you need more control over the connection, you can use the transaction or connect methods.

Transactions

Slonik provides a transaction method that allows you to execute queries within the same connection and a single transaction:

await pool.connect(async (connection) => {
  await connection.query(sql.unsafe`INSERT INTO logs (message) VALUES ('foo')`);
  await connection.query(sql.unsafe`INSERT INTO logs (message) VALUES ('bar')`);
});

With this, both insertions either succeed together or fail together.

Using Transactions with Express.js

Let's see how we can use transactions with Express.js. First, we'll create a route that inserts a user into the database:

app.post('/users', async (req, res) => {
  const { name } = req.body;

  await pool.query(sql.unsafe`INSERT INTO users (name) VALUES (${name})`);

  res.json({ message: 'User created!' });
});

If it is just a single query, we don't need to use a transaction. However, let's say we want to log the creation of the user in the database:

app.post('/users', async (req, res) => {
  const { name } = req.body;

  await pool.transaction(async (transaction) => {
    await transaction.query(sql.unsafe`INSERT INTO users (name) VALUES (${name})`);
    await transaction.query(sql.unsafe`INSERT INTO logs (message) VALUES (${name} was created!)`);
  });

  res.json({ message: 'User created!' });
});

Here, we are using the transaction method to execute two queries within the same connection and a single transaction. This ensures that both queries either succeed together or fail together. If either query fails, the transaction is rolled back and the error is thrown.

Optimizing Transactions

This section is tangential to the main topic of this article, but I think it's important to mention.

One key principle when working with transactions in relational databases is to keep them as concise as possible. Transactions that extend for long durations can lead to a host of issues.

Extended transaction durations can block other operations, increasing contention for resources. This can, in turn, escalate to more severe problems such as deadlocks. In a deadlock scenario, two or more transactions wait indefinitely for each other to release locks.

Such problems can significantly degrade the performance of the system. Especially in situations with high concurrency, bottlenecks can emerge, stifling the smooth operation of the database.

Moreover, there's the risk of long-running transactions exhausting connection pool resources. When this happens, it can result in application slowdowns, as various parts of the application are queued, waiting for available connections.

In light of these potential pitfalls, it becomes paramount to ensure that transactions are designed to encapsulate only the absolutely necessary operations. They should be optimized for both speed and efficiency. By adhering to these principles, you not only uphold the integrity of the transaction but also ensure the responsiveness and scalability of the broader application and database system.

In short, keep your transactions short and sweet.

Reserving a Connection

Warning: Reserving a connection from the pool is an advanced feature. It is recommended to use transactions instead.

connect method allows you to reserve a connection from the pool and execute queries on it.

It is important to note that this is a rare use case, esp. in the context of services that produce responses to user requests. Most of the time, you should use connection from the connection pool or transactions instead. However, there are some cases where you may need to reserve a connection from the pool. For example, you may want to set time zone for the connection:

app.get('/events', async (req, res) => {
  const events = await pool.connect(async (connection) => {
    await connection.query(sql.unsafe`SET LOCAL timezone = 'America/New_York'`);

    return await connection.any(sql.unsafe`SELECT event_name, event_time FROM events`);
  });

  res.json(events);
});

Passing a Connection to a Function

A common a question I am asked is how to pass a connection to a function. For example, let's say you have a function that fetches all users from the database:

const getUsers = async () => {
  return await connection.any(sql.unsafe`SELECT * FROM users`);
};

app.get('/users', async (req, res) => {
  const users = await getUsers();

  res.json(users);
});

The simple solution is to pass the connection as an argument:

const getUsers = async (pool: DatabasePool) => {
  return await pool.any(sql.unsafe`SELECT * FROM users`);
};

app.get('/users', async (req, res) => {
  const users = await getUsers(pool);

  res.json(users);
});

I realize that this is a very simple example, but this is true also even if your function that requires access to the database handle is nested deep within a call stack. You must an instance of the connection pool down to every function in the chain.

Anti-Patterns: AsyncLocalStorage

import express from 'express';
import { createPool } from 'slonik';
import { AsyncLocalStorage } from 'async_hooks';

const asyncLocalStorage = new AsyncLocalStorage();

const main = async () => {
  const pool = createPool('postgres://...');

  const app = express();
  const port = 3000;

  app.use((req, res, next) => {
    pool.connect((err, connection) => {
      if (err) {
        return next(err);
      }
      asyncLocalStorage.run(connection, next);
    });
  });

  app.get('/users', async (req, res, next) => {
    try {
      const users = await fetchUsersFromDb();
      res.json(users);
    } catch (error) {
      next(error);
    }
  });

  async function fetchUsersFromDb() {
    const connection = asyncLocalStorage.getStore();
    const users = await connection.any('SELECT * FROM users');
    return users;
  }

  app.listen(port, () => {
    console.log(`Example app listening on port ${port}`);
  });
};

I strongly recommend against this. It is not a good idea both in terms of performance and design. If you need to pass a connection to a function, you should pass it as an argument.

Refer to the Passing a Connection to a Function section for more information.

Anti-Patterns: wrapping the entire request in a transaction

Another anti-pattern I see is wrapping the entire request in a transaction:

app.post('/users', async (req, res) => {
  await pool.transaction(async (transaction) => {
    // do something that is part of the request, but not part of the transaction
    await foo();

    await transaction.query(sql.unsafe`INSERT INTO users (name) VALUES ('foo')`);

    res.json(
      await transaction.one(sql.unsafe`SELECT * FROM users WHERE name = 'foo'`)
    );
  });
});

This is not a good idea. Transactions should be as short as possible. If you need to do something that is not part of the transaction, you should do it outside of the transaction.

app.post('/users', async (req, res) => {
  await foo();

  await pool.transaction(async (transaction) => {
    await transaction.query(sql.unsafe`INSERT INTO users (name) VALUES ('foo')`);

    res.json(
      await transaction.one(sql.unsafe`SELECT * FROM users WHERE name = 'foo'`)
    );
  });
});

Refer to the Optimizing Transactions section for more information.

Anti-Patterns: wrapping the entire request in a connection

Another anti-pattern I see is wrapping the entire request in a connection:

app.post('/users', async (req, res) => {
  await pool.connect(async (connection) => {
    res.json(await foo());
  });
});

Instead, you should pass an instance of the connection pool to the function that needs access to the database handle and use the connection pool to execute queries and transactions.

Refer to the Passing a Connection to a Function section for more information.

Async Middleware

Express.js does not support asynchronous routes out of the box. For the purpose of keeping the examples simple, we used await in our routes. You can use express-promise-router or express-async-handler to support asynchronous routes in your application.

Conclusion

By now, you should have a good grasp on integrating Slonik with Express.js in TypeScript. If you have any questions, feel free to reach out to me on Twitter or leave an issue on GitHub.

Routing queries to read-only PostgreSQL replica in Node.js

Gajus Kuizinas — Thu, 06 Oct 2022 00:21:19 +0000

Scaling databases is hard. However, perhaps the lowest hanging fruit is introducing read-only replicas.

A typical load balancing requirement is to route all "logical" read-only queries to a read-only instance. This requirement can be implemented in 2 ways:

Create two database clients (read-write and read-only) and pass them around the application as needed.
Use a middleware to assign query to a connection pool based on the query itself.

Option 1: Two distinct clients

The first option is preferable as it is the most explicit. However, it also has the most overhead to implement – adding this to an existing codebase could mean modifying thousands lines of code. The other downside of this approach is that it that it is easy to overlook when a read-only query is using a read-write database client, and while that isn't a major issue, it would be nice if the inverse it true – we would be warned if we are using the wrong connection for the query. That might be possible with a middleware that routes queries.

Option 2: Middleware

The second option routes queries based on the query itself and the context in which it was initiated. The following has to be true for us to safely route a query to a read-only instance:

It must be a SELECT query.
It mustn't be part of a transaction.
It mustn't execute a volatile function.

The first two requirements are relatively straightforward to implement. The third one requires that we introduce a convention.

Handling volatile functions

A volatile function in PostgreSQL is any function that has side-effects (e.g. writes data) or that can change output even within a single table scan (e.g. random()). In our context, only the first part of volatile function is relevant. However, there is no way of telling just by looking at the query if it is volatile or not.

SELECT foo() # Is foo() volatile?

Therefore, we need to have a convention for marking such queries as not safe for read-only routing. There is more than one way of doing it, but we chose a very simple approach: a magic comment that marks query as volatile. Just add @volatile anywhere in the query (comment) to indicate that it has side-effects and have middleware check for that keyword.

We could also do the inverse here and use a convention to mark which queries are safe for routing to the read-only instances using @readOnly keyword. This way we could avoid checking the query for other keywords altogether and entirely rely on what the engineer instruments. However, this approach suffers from the same short-comings as the #1 option: Easy to miss opportunities and requires editing every query one-by-one.

Implementing query routing using Slonik

We are using Slonik PostgreSQL client in our project, and lucky for us, Slonik has a beforePoolConnection middleware that can be used to implement all 3 requirements. In short, beforePoolConnection is called just before the query is assigned a connection. All we have to do is create a second read-only pool and route queries to that read-only pool when all 3 conditions are satisfied. The code is mighty straightforward:

const readOnlyPool = await createPool('postgres://read-only');
const pool = await createPool('postgres://main', {
  interceptors: [
    {
      beforePoolConnection: (connectionContext) => {
        if (!connectionContext.query?.sql.trim().toUpperCase().startsWith('SELECT ')) {
          // Returning null falls back to using the DatabasePool from which the query originates.
          return null;
        }

        // This is a convention for the edge-cases where a SELECT query includes a volatile function.
        // Adding a @volatile comment anywhere into the query bypasses the read-only route, e.g.
        // sql`
        //   # @volatile
        //   SELECT write_log()
        // `
        if (connectionContext.query?.sql.includes('@volatile')) {
          return null;
        }

        // Returning an instance of DatabasePool will attempt to run the query using the other connection pool.
        // Note that all other interceptors of the pool that the query originated from are short-circuited.
        return readOnlyPool;
      }
    }
  ]
});

// This query will use `postgres://read-only` connection.
pool.query(sql`SELECT 1`);

// This query will use `postgres://main` connection.
pool.query(sql`UPDATE 1`);

A few things that are worth clarifying about the above code:

We are not handling SELECT INTO in this middleware. We know that our use case does not require it, so I left it out. If you are using SELECT INTO, a simple fix could be to check if the query contains INTO keyword.
It is worth emphasizing that if a client initiates connection using pool#connect() or pool#transaction(), thenconnectionContext.queryisnull`, i.e., we are going to fall back to using the main pool.

And that's it! Short and sweet. We now have an efficient query routing mechanism that reduced the load on our main instance and allows us to better scale our service.

Building advanced SQL search from a user text input

Gajus Kuizinas — Fri, 30 Sep 2022 17:29:50 +0000

We are going to build a program that parses user search query and serializes it into a SQL statement. Here is an example of a search query:

firstName:"Thomas" -jefferson birthdate:[1960 TO 1970] (profession:"inventor" OR profession:"engineer")

What this says is:

Find records with first name "Thomas"
Exclude mentions of "jefferson"
Narrow down results to where the birthdate is between 1960 and 1970
Narrow down results to where the profession is either "inventor" or "engineer"

In SQL, this could look something like:

"firstName" = 'Thomas' AND
"searchName" ILIKE '%jefferson%' AND
"birthdate" BETWEEN 1960 AND 1970 AND
(
  "profession" = 'inventor' OR
  "profession" = 'engineer'
)

It might not be the most succinct form of SQL to do the job, but it is good enough.

https://contra.com/p/WobOBob7-building-advanced-sql-search-from-a-user-text-input

Fully qualified names vs a jungle of imports

Gajus Kuizinas — Sun, 11 Sep 2022 16:49:28 +0000

Originally published on Contra

Open any Node.js project and any deeply nested file within that project and the first thing you will see is a variation of:

import { configureScope } from '@sentry/node';
import { serializeError } from 'serialize-error';
import { type CommonQueryMethods, sql } from 'slonik';
import { z } from 'zod';
import { Logger } from '../../Logger';
import { type CustomerIOService } from '../services';
import { type SendPlainEmailOptions } from '../types';
import { fetchUserSettings } from './fetchUserSettings';

What you see is a a ton of imports from various paths across the application.
Honestly, I don't know why we are we doing it that way. Being explicit is nice, but it comes at the cost of constant guess game when trying to figure out how many ../ to add to your import path, and in general, the need to remember where everything lives.
In TypeScript, we can diminish the guess game by using path mapping. A common configuration is going to be "paths":{"@/*":["*"]} which now allows to import everything using absolute paths. This helps to rewrite the above code to something along the lines of:

import { configureScope } from '@sentry/node';
import { serializeError } from 'serialize-error';
import { type CommonQueryMethods, sql } from 'slonik';
import { z } from 'zod';
import { Logger } from '@/Logger';
import { type CustomerIOService } from '@/customer/services';
import { type SendPlainEmailOptions } from '@/customer/types';
import { fetchUserSettings } from '@/customer/routines/fetchUserSettings';

I would argue that the above is already easier to understand because we have the entire context to know what is being imported just by looking at the code. Whereas, with the relative paths, we need to do mental gymnastics to compute what the relative paths resolve to. However, this pattern still suffers from the need to remember where everything lives. Why? What good does it do? Perhaps there is a better way…

Using fully qualified names

Wouldn't it be easier if we could just import all project dependencies from a single file?

import {
  configureScope,
  serializeError,
  type CommonQueryMethods,
  sql,
  z,
  Logger,
  type CustomerIOService,
  type SendPlainEmailOptions,
  fetchUserSettings
} from '@/index';

The benefit of this approach is that we never need to remember the path of something in order to import it – we just need to know what we want to import. This also makes it helluva easier to stub dependencies and monkey patch dependencies when dealing with bugs.

The downside is that it requires that every method and variable in the codebase that has a public interface has to have a unique name (thus the fully qualified). Despite this not being the norm, I would definitely argue that this is a benefit because it forces to pick descriptive function names, and when you cannot, it probably signals a code smell.

I cannot see a good reason why we wouldn't be doing this.

Migrating from Cypress to Playwright using GPT3

Gajus Kuizinas — Wed, 07 Sep 2022 19:12:11 +0000

Cypress and @playwright/test are two competing integration testing frameworks. We have been using Cypress for a long time but recently made a decision to migrate our Cypress tests to Playwright, all 400 of them. It is absolutely worth it for the speed and reliability gains (a future post)... However, it meant a lot of manual work! Or so I thought...

https://contra.com/p/PWBcPYZc-rewriting-tests-from-cypress-to-playwright-using-gpt-3

Read how we automated the process almost entirely using GPT3 on Contra.

Slonik v30 added sql.type, sql.date, sql.timestamp and sql.interval

Gajus Kuizinas — Fri, 19 Aug 2022 17:42:25 +0000

sql.type – runtime validation
sql.timestamp – create PostgreSQL timestamp from Date
sql.date – create PostgreSQL date from Date
sql.interval – create PostgreSQL interval

Validating PostgreSQL results & inferring query static types

Gajus Kuizinas — Fri, 05 Aug 2022 16:49:00 +0000

Originally published on my Contra profile.

Most of the bugs come from misaligned expectations. When working with databases, the first line of defense is to type the expected query results statically.

type SubscriptionPayment = {
  id: number,
  subscriber: string,
  amount: number
};

await connection.many(
  sql<Person>`
    SELECT id, subscriber, amount
    FROM subscription_payment
  `
);

Doing this already improves the developer experience greatly because now we can make assumptions about the query results without even looking at the query.

We might even have integration tests that further validate that the query is returning the expected results (at least at the build time).

However, the problem is that once you deploy the application, the database schema might change independently of the codebase. This drift may result in your application behaving in unpredictable and potentially dangerous ways, e.g., imagine if the amount column type changed from numeric to text.

Without the runtime validation, this would cause a cascade of problems and potential database corruption. Even worse, without runtime checks, this could go unnoticed for a long time.

In contrast, by using runtime checks, you can ensure that the contract between your codebase and the database is always respected. If there is a breaking change, the application fails with a loud error that is easy to debug and fix.
Up to now, there was not a simple way to add runtime validation to database queries.

However, thanks to zod, a schema validation and static type interference library, and Slonik, we can now have the best of both worlds by writing just a single schema per query and getting static types and runtime result validation.

If you are familiar with JavaScript ecosystem, what Relay is to React.js/GraphQL, Slonik is to Node.js/PostgreSQL.
Here is how it works.

Let's assume that you have a PostgreSQL table person:

CREATE TABLE "public"."person"(
  "id" integer GENERATED ALWAYS AS IDENTITY,
  "name" text NOT NULL,
  PRIMARY KEY ("id")
);

and you want to retrieve all persons in the database, along with their id and name:

connection.any(sql`
  SELECT id, name
  FROM person
`);

With your knowledge of the database schema, define a zod object:

const personObject = z.object({
  id: z.number(),
  name: z.string(),
});

Update your query to use sql.type tag and pass personObject:

const personQuery = sql.type(personObject)`
  SELECT id, name
  FROM person
`;

Finally, query the database using typed sql tagged template:

const persons = await connection.any(personQuery);

With this information, Slonik guarantees that every member of persons is an object that has properties id and name, which are a non-null number and a non-null string respectively.

Handling schema validation errors

If query produces a row that does not satisfy zod object, then SchemaValidationError error is thrown.
SchemaValidationError includes properties that describe the query and validation errors:

sql – SQL of the query that produced unexpected row.
row – row data that did not satisfy the schema.
issues – array of unmet expectations.

Whenever this error occurs, the same information is also included in the logs.

In most cases, you shouldn't attempt to handle these errors at individual query level – allow to propagate to the top of the application and fix the issue when you become aware of it.

However, in cases such as dealing with unstructured data, it might be useful to handle these errors at a query level, e.g.

import { SchemaValidationError } from 'slonik';

try {} catch(error) {
  if (errorextendsSchemaValidationError) {
    // Handle scheme validation error
  }
}

Performance penalty

In the context of the network overhead, validation accounts for a tiny amount of the total execution time.Just to give an idea, in our sample of data, it takes sub 0.1ms to validate 1 row, ~3ms to validate 1,000 and ~25ms to validate 100,000 rows.

Unknown keys

Slonik disallows unknown keys, i.e. query that returns {foo: 'bar', baz: 'qux'} with z.object({foo: z.string()}) schema will produce SchemaValidationError error.

Inferring types

You can infer TypeScript type of the query result. There are couple of ways of doing it:

// Infer using z.infer<typeof yourSchema>
// https://github.com/colinhacks/zod#type-inference
type Person = z.infer<typeof personObject>;

// from sql tagged template `zodObject` property 
type Person = z.infer<personQuery.zodObject>;

Transforming results

Using zod transform you can refine the result shape and its type, e.g.

const coordinatesType = z.string().transform((subject) => {  
  const [x,y] = subject.split(',');
  return{x:Number(x),y:Number(y)};
});

const zodObject = z.object({foo:coordinatesType});
const query = sql.type(zodObject)`SELECT '1,2' as foo`;
const result = await pool.one(query);
expectTypeOf(result).toMatchTypeOf<{foo:{x:number,y:number}}>();

t.deepEqual(result,{foo:{x:1,y:2}});

What's next?

Now that we have a framework for instrumenting queries, the next step is automate schema and type generation as much as possible. For that, check out the work that mmkal has been doing in https://www.npmjs.com/package/@slonik/typegen

The most important ESLint rule: max-params

Gajus Kuizinas — Fri, 15 Jul 2022 08:14:18 +0000

Let me ask you: What do you think this code does?

resizeImage(imagePath, 300, 200, true, true, 1)

It resizes image... but what exactly does it do? For the most part, we cannot tell without looking up the function definition.

Let's say you are reviewing a PR and it includes this change:

-resizeImage(imagePath, 300, 200, true, true, 1)
+resizeImage(imagePath, 300, 200, false, true, 1)

Can you confidently say what is the impact of this change? For the most part... no. You need to know what each positional argument does.

Let's say you know that the interface is:

function resizeImage(
  imagePath: string,
  width: number,
  height: number,
  upscale: boolean,
  crop: boolean,
  quality: number,
): Promise<Buffer>

But now a PR introduces a change to the parameter order (e.g. to make it consistent with other functions):

function resizeImage(
  imagePath: string,
  width: number,
  height: number,
+  crop: boolean,
  upscale: boolean,
-  crop: boolean,
  quality: number,
): Promise<Buffer>

How do you review this change? Sure, reviewing the interface diff is easy, but what about the dozens or hundreds of diffs that update function invocation?

-resizeImage(imagePath, 300, 200, true, false, 1)
+resizeImage(imagePath, 300, 200, false, true, 1)
resizeImage(imagePath, 300, 200, false, false, 1)
-resizeImage(imagePath, 300, 200, false, true, 1)
+resizeImage(imagePath, 300, 200, false, false, 1)
-resizeImage(imagePath, 300, 200, true, false, 1)
+resizeImage(imagePath, 300, 200, false, true, 1)

Hopefully the problem is self-explanatory: Positional arguments create a breading ground for hard and even impossible bugs to catch/debug/fix, esp. when code needs to be refactored. Fear not though as there is a better way.

Let's start from the start, but this time use a single-object parameter:

resizeImage({
  imagePath,
  width: 300,
  height: 200,
  upscale: true,
  crop: false,
  quality: 1,
})

Can you tell what is the intention behind this code? Yes, you can get a good sense, even if you are not familiar with the implementation.

Can you easily refactor the interface? Yes, linter will warn you if contract is not satisfied.

We end up with positional arguments because they feel the most natural to start with. However, as functions grow in scope, what started as a simple function with 1-2 arguments becomes an unreadable mess.

This is where max-params comes to the rescue. Simply adding an ESLint rule that restricts having functions with more than X parameters ensures that your code remains legible and easy to refactor as your codebase scales.

Enable HTTPS keepAlive in Node.js

Gajus Kuizinas — Tue, 12 Jul 2022 18:16:37 +0000

No clue how I am learning this only now, but better later than never: HTTPS keepAlive is not enabled by default in Node.js, and it has severe performance implications to network-intense applications.

Just to illustrate the impact, assuming that your server is hosted in us-central1 and it is talking to a service in us-east1, just the network latency is ~20ms. Since a TCP handshake is a 3-packet event, this means that ~60ms are going to be allocated just to establish TLS handshake.

You can test that with a simple script:

const got = require('got');

const main = async () => {
  const response0 = await got('https://posthog.com/');

  console.log(response0.timings.phases);

  const response1 = await got('https://posthog.com/');

  console.log(response1.timings.phases);
};

main();

In this scenario, the above will produce:

{
  wait: 1,
  dns: 20,
  tcp: 72,
  tls: 74,
  request: 0,
  firstByte: 79,
  download: 222,
  total: 468
}
{
  wait: 0,
  dns: 1,
  tcp: 67,
  tls: 69,
  request: 1,
  firstByte: 73,
  download: 234,
  total: 445
}

However, watch the total time if we enable keepAlive:

const got = require('got');
const https = require('https');

https.globalAgent = new https.Agent({ keepAlive:true });

const main = async () => {
  const response0 = await got('https://posthog.com/');

  console.log(response0.timings.phases);

  const response1 = await got('https://posthog.com/');

  console.log(response1.timings.phases);
};

main();

{
  wait: 1,
  dns: 27,
  tcp: 77,
  tls: 75,
  request: 0,
  firstByte: 75,
  download: 220,
  total: 475
}
{
  wait: 0,
  dns: 0,
  tcp: 0,
  tls: 0,
  request: 0,
  firstByte: 77,
  download: 83,
  total: 160
}

The second request is 70% faster than the first request!

If your application relies on making many HTTPS calls, then simply enabling keepAlive is going to result in a significant performance improvement.

What makes a good commit

Gajus Kuizinas — Mon, 27 Jun 2022 22:14:51 +0000

First, the main goal of a commit is to introduce an atomic change.

Atomic changes contain only one feature, refactor, fix, etc.

A good commit allows to evaluate one change at a time and it contains only/all the changes necessary.

Bad commits happen because of a bad workflow.

Your workflow is rarely a reflection of what should be contained in a good commit, i.e.

9 times out of 10, if you commit using git commit -a you are making it a 💩 commit.

The workflow should be:

finish developing the feature and test it
think back through all the changes you had to make and how you would want to present them
stage selected lines/rebase to create commits that introduce them in logical chunks/order

If you have not already, please learn how to perform interactive staging.

http://git-scm.com/book/en/v2/Git-Tools-Interactive-Staging

Besides git, there are a ton of tools that provide GUI and make it is super simple. My favorite is Tower.

If you do the above, then your PR commits should read something along the lines of:

* feat: add claimUsername test helper
* test: ensure that username is at least 5 characters long
* test: ensure that in-use username cannot be claimed
* refactor: have registerNewTestUser use claimUsername
* refactor: have completeUserOnboarding use claimUsername

These commits are easy to review because each one of them introduces a change that can be reviewed and discussed in isolation.

Also, just by reading the commits, you gain a good mental map of all the changes.

It also helps to spot potentially breaking changes quicker.

could adding a function break the code? no
could adding a test break the code? no
could refactoring existing code to use new feature break the code? maybe

It is also super important that your code works after every commit.

If it does not, then you cannot safely revert your codebase to an earlier point in time.

If you do not enforce the above, then you should enforce squashing commits when merging PRs.

Pro-tip: If you are interviewing for an engineering job, ask interviewer to share the recent commit log of the codebase that you will be contributing to.

git log --pretty=oneline --abbrev-commit

It will tell you a lot about the engineering culture they have.

In contrast, a bad commit log is a result of "commiting as you go" and looks more like:

* test: add helper
* refactor: use helper
* fix: add missing configuration
* fix: inline editing
* fix: failing tests
* fix: revert earlier fix
* refactor: use helper

These commits are bad because the changes they contain are non-atomic and non-linear. The only way for the reviewer to evaluate these changes is by looking at all files modified at once. That's a big mental burden to put on someone. ☹️

Note that I didn't talk about contents/formatting of the commit messages themselves. What convention you adopt is a lot less important than just having atomic commits. In fact, great commit messages are a by-product/indication of atomic commits and vice-versa.

And in case you are thinking "this is a lot of work", "no one is reading these" or "it slows me down", then you should re-evaluate the sports that you are playing. Engineering is a team sport. Great engineering input compounds.

You don't need every commit/PR to be perfect, but it shows who is putting effort, and if others see you put in effort, they will do to. 🕊

For more great content of mine, follow me on Twitter https://twitter.com/kuizinas/status/1541496585275727875

Testing in production vs staging

Gajus Kuizinas — Fri, 24 Jun 2022 15:51:59 +0000

Context:

I am adamant advocate of designing services in such a way that promotes testing them in production as part of the software development cycle. Developers have their local test environments, and we have ephemeral environments for running integration tests, but we do not have staging environments. This comes as a surprise and almost a crazy idea to people who have not worked in a such setup. What follows is an internal memo that expresses my views in a conversation with the team about having a dedicated test / staging environment.

Internal memo:

There is no denying that testing pre-production is valuable – you can do risk-free deployments, revert them, etc.

In the ideal world, every branch that is being worked on would have its own web app, database, API, temporal, etc. – the entire copy of production. This environment would last no longer than the branch (but would also not provide any guarantees about persisting state within that period). This is the ideal world ephemeral testing environment. It is ideal because:

we know that it is reasonably similar to production because initialization is versioned and replicable
we can always ensure that it matches the current production environment by recreating it
there is no maintenance cost assigned to it because it has no expectations of persistence

We already have components of it (ephemeral API and database instances), and one day I would like us to have all of the above.

In contrast, a persistent staging environment does not provide any of these guarantees:

You have no clue what is deployed to this environment because multiple people can be contributing to it, i.e. You are not testing your changes in isolation.
You have no clue in what state is the environment because multiple users / automated tests can be making changes to it.
Staging environment is more than likely to lack robust monitoring and alerts coming from staging are not going to get prioritized.

Some testing is not even possible in staging environment, namely: performance and stress testing. This is because staging is deployed under a different infrastructure.

As far as integrity tests go, a shared test environment that is in an unpredictable state is as useful to integration tests as self-attesting PRs that they do not have bugs. What works there is not guaranteed to work in production.

On the other hand, testing in production is hard:

You need to be careful when deploying changes because no matter how many safe-guards you add to CI/CD, there is a chance of breaking production.
You have to think ahead not only about whether your changes will work in the test environment, but also if they are going to work in production (a perfect example of that is forgetting to add indexes concurrently, which will work in staging, but may completely lock production).
You have to make expensive transactions conditional, e.g. You should be able to test using sandbox / production Stripe credentials in production and in your local development environments. Otherwise, you cannot easily debug issues that arise in just one of the environments.
You have to plan for tests to be atomic, i.e. ensuring that other user actions do not affect your tests.
You have to have a process for canary, blue/green and rollbacks.
You have to have a process for isolating and deleting test data.
You have to have sufficient redundancy to avoid outages.
You have to communicate changes being deployed.
You have to monitor your deployments to isolate or revert deployments with unexpected behavior.

This is a lot of overhead, but all of these are also great practices for production maintenance regardless of where testing takes place.

In addition, testing in production allows a quicker path to (iteratively) test and collect feedback from real-users, which is what we all should be aiming to be doing more.

The way I see it, testing in production has a higher upfront cost and ongoing cost, but it brings along a heap of benefits that otherwise would get deprioritized in a normal software development cycle. It is how we learn to make safe deployments and quickly restore services when the unexpected happens.