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