Mirror of https://github.com/roostorg/coop
github.com/roostorg/coop
1/**
2 * @fileoverview Connects to pg via sequelize and exposes some helper functions
3 * for running queries in a transaction.
4 *
5 * Critically, this code is not in models/index.ts in order to avoid a circular
6 * dependency with model definitions that use the transactionWithRetry helper.
7 */
8import clsHooked from 'cls-hooked';
9import pkg, { type Transaction, type TransactionOptions } from 'sequelize';
10
11import { safeGet } from '../utils/misc.js';
12
13const { Sequelize } = pkg;
14const {
15 DATABASE_HOST,
16 DATABASE_READ_ONLY_HOST,
17 DATABASE_PORT = 5432,
18 DATABASE_NAME = 'development',
19 DATABASE_USER = 'postgres',
20 DATABASE_PASSWORD,
21 SEQUELIZE_PRINT_LOGS,
22} = process.env;
23
24// Set up CLS so that we can ambiently link queries into the same transaction.
25// See https://sequelize.org/docs/v6/other-topics/transactions/
26Sequelize.useCLS(clsHooked.createNamespace('sequelize'));
27
28export const makeSequelize = () =>
29 new Sequelize(DATABASE_NAME, DATABASE_USER, DATABASE_PASSWORD, {
30 port: Number(DATABASE_PORT),
31 // eslint-disable-next-line no-console
32 logging: SEQUELIZE_PRINT_LOGS === 'true' ? console.log : false,
33 dialect: 'postgres',
34 replication: {
35 read: [{ host: DATABASE_READ_ONLY_HOST }],
36 write: { host: DATABASE_HOST },
37 },
38 pool: {
39 max: 150,
40 acquire: 15_000,
41 // This timeout was made crazy long so that queries which take a long time
42 // to respond don't cause Sequelize to release the connection back to the
43 // pool and/or close it. We needed this for loading location bank
44 // locations, which were previously stored as 40mb json blobs, which pg
45 // could take ~1 minute to respond with (or more when the db was under
46 // heavy load). Going forward, we don't want to have idle connections for
47 // this long (and should warn if a connection is idle in connection for
48 // more than, idk, ~5s), but having a long idle timeout is still probably
49 // better than canceling the query and releasing the connection. However,
50 // we also don't want this to be too long, so that a server instance that
51 // briefly needs to open a lot of connections (e.g., to warm its caches on
52 // startup) doesn't hold those connections for longer than necessary,
53 // which'll add db load and [in future] potentially lead to hitting the
54 // db's max connection limit as new server instances are autoscaled in.
55 idle: 300_000,
56 // TODO: set maxUses once we start auto scaling the number of read replicas.
57 // See https://github.com/sequelize/sequelize-pool#using-maxuses-option
58 // Think about how/if we'll do this w/ our kysely connection pools.
59 },
60 dialectOptions: {
61 //ssl: true,
62 query_timeout: 1_000_000,
63 idle_in_transaction_session_timeout: 300_000,
64 },
65 });
66
67export function maketransactionWithRetry(
68 sequelize: pkg.Sequelize,
69): TransactionWithRetry {
70 /**
71 * Run a Sequelize transaction, and auto-retry up to two times if it fails due
72 * to a serialization error. Sequelize's should really have this built-in See
73 * https://stackoverflow.com/questions/68427796/sequelize-transaction-retry-doenst-work-as-expected
74 *
75 * See https://www.postgresql.org/docs/current/transaction-iso.html
76 */
77 // eslint-disable-next-line @typescript-eslint/no-explicit-any
78 return async function transactionWithRetry(...args: any[]) {
79 let remainingTries = 3;
80 while (remainingTries > 0) {
81 try {
82 remainingTries -= 1;
83 return await sequelize.transaction(...args);
84 } catch (e: unknown) {
85 if (safeGet(e, ['original', 'code']) === '40001') {
86 await sequelize.query('ROLLBACK');
87 } else {
88 throw e;
89 }
90 }
91 }
92
93 throw new Error('Retry limit exceeded.');
94 };
95}
96
97type TransactionWithRetry = {
98 <T>(
99 options: TransactionOptions,
100 autoCallback: (t: Transaction) => PromiseLike<T>,
101 ): Promise<T>;
102 <T>(autoCallback: (t: Transaction) => PromiseLike<T>): Promise<T>;
103};