Utilizing Database Hooks Like a Pro in Node.js

In this article, I’ll explain how to use database hooks in your Node.js applications to solve specific problems that might arise in your development journey.

Many applications require little more than establishing a connection pool between a server, database, and executing queries. However, depending on your application and database deployments, additional configurations might be necessary.

For example, multi-region distributed SQL databases can be deployed with different topologies depending on the application use case. Some topologies require setting properties on the database on a per-session basis.

Let’s explore some of the hooks made available by some of the most popular database clients and ORMs in the Node.js ecosystem.

Laying the Foundation

The Node.js community has many drivers to choose from when working with the most popular relational databases. Here, I’m going to focus on PostgreSQL-compatible database clients, which can be used to connect to YugabyteDB or another PostgreSQL database.

Sequelize, Prisma, Knex and node-postgres are popular clients with varying feature sets depending on your needs. I encourage you to read through their documentation to determine which best suits your needs.

These clients come with hooks for different use cases. For instance:

• Connection hooks: Execute a function immediately before or after connecting and disconnecting from your database.
• Logging hooks: Log messages to stdout at various log levels.
• Lifecycle hooks: Execute a function immediately before or after making calls to the database.

In this article, I’ll cover some of the hooks made available by these clients and how you can benefit from using them in your distributed SQL applications.

I’ll also demonstrate how to use hooks to hash a user's password before creation and how to set runtime configuration parameters after connecting to a multi-region database with read replicas.

Sequelize 

The Sequelize ORM has a number of hooks for managing the entire lifecycle of your database transactions. 

The beforeCreate lifecycle hook can be used to hash a password before creating a new user:

User.beforeCreate(async (user, options) => {
 const hashedPassword = await hashPassword(user.password);
 user.password = hashedPassword;
});

Next, I’m using the afterConnect connection hook to set session parameters. 

With this YugabyteDB deployment, you can execute reads from followers to reduce latencies, and eliminate the need to read from the primary cluster nodes:

const config = {
  host: process.env.DB_HOST,
  port: 5433,
  dialect: "postgres",
  dialectOptions: {
    ssl: {
      require: true,
      rejectUnauthorized: true,
      ca: [CERTIFICATE],
    },
  },
  pool: {
    max: 5,
    min: 1,
    acquire: 30000,
    idle: 10000,
  },
  hooks: {
     async afterConnect(connection) {
       if (process.env.DB_DEPLOYMENT_TYPE === "multi_region_with_read_replicas") {
         await connection.query("set yb_read_from_followers = true; set session characteristics as transaction read only;");
       }
     },
   },
};

const connection = new Sequelize(
   process.env.DATABASE_NAME,
   process.env.DATABASE_USER,
   process.env.DATABASE_PASSWORD,
   config
);

By using this hook, each database session in the connection pool will set these parameters upon establishing a new connection:

• set yb_read_from_followers = true;: This parameter controls whether or not reading from followers is enabled.
• set session characteristics as transaction read only;: This parameter applies the read-only setting to all statements and transaction blocks that follow.

Prisma

Despite being the ORM of choice for many in the Node.js community, at the time of writing, Prisma doesn’t contain many of the built-in hooks found in Sequelize. Currently, the library contains hooks to handle the query lifecycle, logging, and disconnecting, but offers no help before or after establishing connections.

Here’s how you can use Prisma’s lifecycle middleware to hash a password before creating a user:

prisma.$use(async (params, next) => {
 if (params.model == 'User' && params.action == 'create') {
   params.args.data.password = await hashPassword(params.args.data.password);
 }

 return next(params)
})

const create = await prisma.user.create({
 data: {
   username: 'bhoyer',
   password: 'abc123'
 },
})

To set session parameters to make use of our read replicas, we’ll have to execute a statement before querying our database:

await prisma.$executeRaw(`set yb_read_from_followers = true; set session characteristics as transaction read only;`);
const users = await prisma.user.findMany();

If you need to immediately establish a connection in your connection pool to set a parameter, you can connect explicitly with Prisma to forgo the lazy connection typical of connection pooling.

Prisma has the log levels of query , error, info, and warn. Queries can be handled as events using event-based logging:

const prisma = new PrismaClient({
 log: [
   {
     emit: 'event',
     level: 'query',
   },
   {
     emit: 'stdout',
     level: 'error',
   },
   {
     emit: 'stdout',
     level: 'info',
   },
   {
     emit: 'stdout',
     level: 'warn',
   },
 ],
});

prisma.$on('query', (e) => {
 console.log('Query: ' + e.query);
 console.log('Params: ' + e.params);
 console.log('Duration: ' + e.duration + 'ms');
});

This can be helpful in development when working on query tuning in a distributed system. 

Here’s how you can make use of the beforeExit hook to access the database before exiting:

const prisma = new PrismaClient();

prisma.$on('beforeExit', async () => {
 // PrismaClient still available
 await prisma.issue.create({
   data: {
     message: 'Connection exiting.' 
   },
 })
});

Knex

Knex is a lightweight query builder, but it does not have the query middleware found in more full-featured ORMs. 

To hash a password, you can process this manually using a custom function:

async function handlePassword(password) {
	const hashedPassword = await hashPassword(password);
	return hashedPassword;
}
const password = await handlePassword(params.password);
knex('users').insert({...params, password});

The syntax required to achieve a connection hook in the Knex.js query builder is similar to that of Sequelize. Here’s how we can set our session parameters to read from YugabyteDB’s replica nodes:

const knex = require('knex')({
  client: 'pg',
  connection: {/*...*/},
  pool: {
    afterCreate: function (connection, done) {
      connection.query('set yb_read_from_followers = true; set session characteristics as transaction read only;', function (err) {
        if (err) {
          //Query failed
          done(err, conn);
        } else {
          console.log("Reading from replicas.");
	     done();
        }
      });
    }
  }
});

node-postgres

The node-postgres library is the most low-level of all of the libraries discussed. Under the hood, the Node.js EventEmitter is used to emit connection events.

A connect event is triggered when a new connection is established in the connection pool. Let’s use it to set our session parameters. I’ve also added an error hook to catch and log all error messages:

const config = {
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  password: process.env.DB_PASSWORD,
  port: 5433,
  database: process.env.DB_NAME,
  min: 1,
  max: 10,
  idleTimeoutMillis: 5000,
  connectionTimeoutMillis: 5000,
  ssl: {
    rejectUnauthorized: true,
    ca: [CERTIFICATE],
    servername: process.env.DB_HOST,
  }
};
const pool = new Pool(config);

pool.on("connect", (c) => {
  c.query("set yb_read_from_followers = true; set session characteristics as transaction read only;");
});

pool.on("error", (e) => {
  console.log("Connection error: ", e);
});

There aren’t any lifecycle hooks at our disposal with node-postgres, so hashing our password will have to be done manually, like with Prisma:

async function handlePassword(password) {
	const hashedPassword = await hashPassword(password);
	return hashedPassword;
}
const password = await handlePassword(params.password);
const user = await pool.query('INSERT INTO user(username, password) VALUES ($1, $2) RETURNING *', [params.username, password]);

Wrapping Up

As you can see, hooks can solve a lot of the problems previously addressed by complicated and error-prone application code. Each application has a different set of requirements and brings new challenges. You might go years before you need to utilize a particular hook in your development process, but now, you’ll be ready when that day comes.

Look out for more from me on Node.js and distributed application development. Until then, keep on coding!