How to uuse Slonik in Fastify/Express?

[aust1nz]

I'm really interested in Slonik as a database querying library, but I'm having a hard time understanding exactly how it would fit into a long-running back-end process like Express or Fastify. I'm hoping others who have figured out the right usage could share their insight.

From what I can figure out, the best approach is to establish a connection pool when the Express/Fastify server is created, and then to ensure that the pool is accessible subsequently when the server is handling requests. Then, when individual endpoints are called by users, they should establish a specific connection from the already instantiated pool, make the query, and then do whatever they want with the results.

Here's an example of what I think this looks like in Fastify:

slonik-plugin.ts:

import { FastifyPluginAsync } from 'fastify';
import fp from 'fastify-plugin';
import { createPool, DatabasePool } from 'slonik';

declare module 'fastify' {
  interface FastifyInstance {
    pool: DatabasePool;
  }
}

export const slonikPlugin: FastifyPluginAsync = fp(async (server) => {
  const pool = await createPool(process.env.DATABASE_URL || '');
  server.decorate('pool', pool);
});

app.ts:

... [Build your server config]
fastify.register(slonikPlugin);

route.ts:

  /** Route:   GET /users
   *  Summary: Get list of users
   *  Details:
   */
  fastify.route<{
    Querystring: inputs.UserListOptions;
  }>({
    method: 'GET',
    url: '/',
    schema: {
      querystring: inputs.userListOptions,
      response: {
        200: outputs.userList,
      },
    },
    handler: async (request) => {
      const users = await fastify.pool.connect(async (connection) => {
        return connection.many(sql`SELECT * from "User" LIMIT 10`);
      });
      return { users }
  }
});

Is this more or less the recommended approach, or am I missing a more obvious way to get the initial configuration up and running? Are there simple abstractions I could take advantage of so that each route handler doesn't have to repeat the connection functionality?

[aust1nz]

After noodling on this a bit more, I think I prefer a slightly different setup. This uses a class to instantiate a Slonik Connection singleton, which is a thin wrapper on existing Slonik methods. Here's an example:

connect.ts

import {
  createPool,
  DatabasePool,
  TaggedTemplateLiteralInvocation,
} from 'slonik';

class Connection {
  pool?: DatabasePool;

  async getPool() {
    if (!this.pool) {
      this.pool = await createPool(process.env.DATABASE_URL || '');
      if (!this.pool) {
        throw new Error('Could not establish database connection pool');
      }
    }
    return this.pool;
  }

  async maybeOne(sql: TaggedTemplateLiteralInvocation) {
    const pool = await this.getPool();
    return pool.connect(async (connection) => {
      return connection.maybeOne(sql);
    });
  }

  async many(sql: TaggedTemplateLiteralInvocation) {
    const pool = await this.getPool();
    return pool.connect(async (connection) => {
      return connection.many(sql);
    });
  }

  // And so forth for other slonik connection routes used in the app
}

export const connect = new Connection();

This allows for a much more readable set of code over in my route handlers:
user-routes.ts

import { connect } from '../db/query.ts';

fastify.route<{
    Querystring: inputs.UserListOptions;
  }>({
    method: 'GET',
    url: '/',
    schema: {
      querystring: inputs.userListOptions,
      response: {
        200: outputs.userList,
      },
    },
    handler: async (request) => {
      const { query } = request;
      const testUsers = await connect.many(sql`SELECT * from "User" LIMIT 10`);
      return testUsers;
    }
});

Or I could abstract related queries into a queries page:
user-queries.ts

import { connect } from '../db/query.ts';

export const getUsers = () => {
  return connect.many(sql`SELECT * from "User" LIMIT 10`);
};

user-routes.ts

import { getUsers } from './user-queries';

fastify.route<{
    Querystring: inputs.UserListOptions;
  }>({
    method: 'GET',
    url: '/',
    schema: {
      querystring: inputs.userListOptions,
      response: {
        200: outputs.userList,
      },
    },
    handler: async (request) => {
      const { query } = request;
      const testUsers = await getUsers();
      return testUsers;
  }
});

I think this approach has two advantages over my first post:

1. I can reuse the user-queries outside of the API server context more easily, if I want to perform a similar query in a standalone job, worker, etc.
2. The route handler syntax is a lot easier to parse as a developer who may not know the specifics of Slonik connection handling.

Just sharing my approach here both for the benefit of anyone with a similar question, and because I'd appreciate feedback from any current Slonik users who may be using this or another approach to use Slonik within an API server.

[AndrewJo]

I don't know about Fastify but I've created a middleware for Express.js for wrapping request handlers in a transaction.

With express-slonik you can ensure your entire request is wrapped in a single database transaction which is really useful when you want to ensure all of the handlers and middleware are properly isolated for a given request context:

import express, { json } from "express";
import createMiddleware, { IsolationLevels } from "express-slonik";

// middleware/currentUser.ts
// Middleware that gets the current user from database from request session data.
const currentUser = function () {
  return async function (req, res, next) {
    try {
      req.currentUser = await req.transaction.one(
        sql`SELECT * FROM users WHERE users.id = ${req.session.userId}`
      );
      next()
    } catch (error) {
      next(error);
    }
  }
};

// middleware/checkPermission.ts
// Middleware to check whether current user has permission to perform this action
const checkPermission = function (permissionName: string) {
  return async function (req, res, next) {
    try {
      const permissions = await req.transaction.manyFirst(sql`
        SELECT permissions.name
        FROM roles
        INNER JOIN permissions ON permissions.role_id = roles.id
        INNER JOIN user_roles ON user_roles.role_id = roles.id
        WHERE user_roles.user_id = ${req.session.userId}
      `);
      )
    } catch (error) {
      next(error);
    }
  };
};

// app.ts
(async function () {
  const pool = await createPool(process.env.DATABASE_URL);
  const app = express();
  const transaction = createMiddleware(pool);

  app
    .use(json())
    .patch(
      "/post/:id",

      // Start a transaction context (can also specify isolation levels if needed)
      transaction.begin(),

      // Since currentUser and checkPermissions are wrapped in the transaction, we know that
      // nothing can modify the user's permissions outside of current request context.
      currentUser(),
      checkPermission("update:post"),

      // By the time we get to the request handler, we have sufficient guarantees that the user in
      // the current request context has the permission to update the post and that no one else
      // is updating this post at the same time.
      async (req, res, next) => {
        try {
          await req.transaction.query(sql`
            UPDATE posts SET 
              posts.updated_by = ${req.session.userId},
              posts.updated_at = now(),
              -- Don't do this at production kids, we want the content sanitized!
              posts.content = ${req.body.content}
            WHERE posts.id = ${req.params.id}`
          );

          const updatedPost = await req.transaction.one(
            sql`SELECT * FROM posts WHERE posts.id = ${req.params.id}`
          );

          // Respond with updated post JSON
          res.json(updatedPost);
        } catch (error) {
          next(error);
        }
      },

      // By default, express-slonik autocommits the transaction when a response is sent to the client
      // by Express.js or autorollbacks whenever an error is detected in the request handler chain. 
      // You can also be explicit or have a finer control on when this happens by uncommenting the 
      // middleware below:
      // transaction.end()

      // Middleware inserted after transaction.end will not have access to req.transaction context and
      // will need to begin a new transaction context!
})();

Checkout the README for more documentation and examples. As always, I would like to know what you guys think about this pattern for Express.js apps.

[flevi29]

@gajus So for instance if I have an endpoint that invokes and returns function x which takes a connection, and this endpoint is frequently called upon, the best practice is still to pass a new connection to every one of these endpoint calls from a connection pool? By extension, is it a bad practice to keep a connection open for the lifetime of the application itself? And even if I would keep a connection open throughout the applications life, would the performance gain be negligible, or non-existent?

[gajus]

@AndrewJo If you have a public facing API and you are doing request-level transactions, then you better hope that no one with bad intentions knows about it. Fair regarding "blanket statement", but given that the absolute vast majority of users should probably never do this, I'd rather use tone that directs reader to be cautious.

If you are doing this, at least make sure that you have very strict timeouts.

[gajus]

@flevi29 Andrew's comment was about transaction. How you handle connection pooling is a different topic and will depend on the payload type. Personally, I would steer away from a pattern that issues a connection per request and instead assign a connection when the application is being invoked. These connections are already locally pooled (with default settings), so you are not compromising on performance.

[flevi29]

@gajus I am not sure I entirely follow. Is the following code the right approach?

// (pool is only ever created once)
const pool = await createPool(...);

// I use Nestjs so I never write things like this, in case I make a mistake
app.get("some-endpoint", async (req, res, ...) => {
  // ...
  // x takes a `DatabaseConnection`, queries any of something and returns it
  res.json(await x(pool));
  // or maybe like so if `x` would use connection multiple times
  res.json(await pool.connect((connection) => x(connection)));
  // ...
});

EDIT: I mean it must be, re-reading what you wrote in the first reply, it exactly means this, or so I interpret it. I guess I was just confused about the whole pool.connect thing, thought I should somehow wrap the whole thing in it, or maybe extract a connection from pool.connect, but thinking more about it the types of connections coming from pool.connect aren't meant to be long lived like that I suppose, even if it's more efficient to use them multiple times when possible.

[gajus]

res.json(await x(pool)) is correct.

[gajus]

Two articles on the subject:

• https://dev.to/gajus/integrating-slonik-with-expressjs-33kn
• https://dev.to/gajus/integrating-fastify-with-slonik-5bke