A database

MeoCord 4.1 · since 4.1.0

Keep a bot's data in PostgreSQL, with one pool provided before login and stores that tests can replace.

Before this

A bot that saves notes for each user in PostgreSQL. The connection pool is a provider, made once before the bot logs in and injected by a token into the stores that query it. Handlers inject the stores, and tests replace either one. The same shape works for any client library.

The code

The pool is provided under a token that createToken types with what it provides. The factory is async, and creates the table the first time:

recipes/database/database.ts

// What every store injects: one pool for the whole bot, typed by its token
export const DATABASE = createToken<pg.Pool>('Database')

export const databaseProvider: Provider = {
  provide: DATABASE,
  // Awaited before login, so a database that refuses the connection stops the bot with the reason
  useFactory: async () => {
    const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL })
    await pool.query(
      'CREATE TABLE IF NOT EXISTS notes (id SERIAL PRIMARY KEY, user_id TEXT NOT NULL, text TEXT NOT NULL)',
    )
    // A provided value's onShutdown runs as the bot stops, after every class that injects it
    return Object.assign(pool, { onShutdown: () => pool.end() } satisfies OnShutdown)
  },
}

The store is a service that injects the pool with @Inject(DATABASE). It holds the queries and nothing else, and every value reaches the database as a parameter:

recipes/database/notes.store.ts

export interface Note {
  id: number
  text: string
}

// The queries for notes: the pool comes from the provider, so this class never connects or closes
@Service()
export class NotesStore {
  constructor(@Inject(DATABASE) private readonly pool: pg.Pool) {}

  async add(userId: string, text: string): Promise<void> {
    await this.pool.query('INSERT INTO notes (user_id, text) VALUES ($1, $2)', [userId, text])
  }

  async list(userId: string): Promise<Note[]> {
    const { rows } = await this.pool.query<Note>('SELECT id, text FROM notes WHERE user_id = $1 ORDER BY id', [userId])
    return rows
  }
}

The controller injects the store. A query can outlast the three seconds Discord gives for a first answer, so both commands acknowledge privately first with @Defer:

recipes/database/notes.controller.ts

@Controller()
export class NotesController {
  constructor(private readonly notes: NotesStore) {}

  // A query can outlast Discord's three seconds, so @Defer acknowledges first
  @Command('note', NoteCommandBuilder)
  @Defer({ ephemeral: true })
  async add(interaction: ChatInputCommandInteraction, { text }: { text: string }) {
    await this.notes.add(interaction.user.id, text)
    await respond(interaction).send({ content: 'Saved.' })
  }

  @Command('notes', NotesCommandBuilder)
  @Defer({ ephemeral: true })
  async list(interaction: ChatInputCommandInteraction) {
    const notes = await this.notes.list(interaction.user.id)
    const content = notes.length ? notes.map(note => `${note.id}. ${note.text}`).join('\n') : 'No notes yet.'
    await respond(interaction).send({ content })
  }
}

The app lists the provider. The store needs no listing, because the controller injects it:

recipes/database/app.ts

@MeoCord({
  controllers: [NotesController],
  // NotesStore is bound because the controller injects it; the pool it injects comes from here
  providers: [databaseProvider],
  clientOptions: { intents: [GatewayIntentBits.Guilds] },
})
export default class App {}

How it works

Before login. start() makes every provided value first, awaiting an async factory, so no class ever injects a promise. A database that refuses the connection rejects start(): MeoCord logs which factory failed and why, the exit code is set to 1, and the bot never logs in.
One pool. A provided value is made once, so every store that injects DATABASE shares the same pool.
Closing it. The value the factory returns has an onShutdown hook of its own. Hooks stop in reverse dependency order, so the pool closes after every class that injects it has stopped. See Lifecycle hooks.
The connection string. DATABASE_URL comes from the environment, which meocord.config.ts loads before the factory runs. See Environment variables.

Testing it

The controller's tests give the testing module an in-memory store in place of NotesStore, so they need no database:

recipes/database/notes.controller.spec.ts

// The store, in memory: the controller is tested without a database
class MemoryNotesStore {
  private readonly rows: (Note & { userId: string })[] = []
  add = vi.fn(async (userId: string, text: string) => {
    this.rows.push({ id: this.rows.length + 1, userId, text })
  })
  list = vi.fn(async (userId: string) =>
    this.rows.filter(row => row.userId === userId).map(({ id, text }) => ({ id, text })),
  )
}

describe('NotesController', () => {
  let store: MemoryNotesStore
  let module: ReturnType<typeof compile>
  const compile = (value: MemoryNotesStore) =>
    MeoCordTestingModule.create({
      controllers: [NotesController],
      providers: [{ provide: NotesStore, useValue: value }],
    }).compile()
  beforeEach(() => {
    store = new MemoryNotesStore()
    module = compile(store)
  })
  const ada = createMockInteraction(User, { id: '111' })

  it('saves a note for the user, acknowledging privately first', async () => {
    const interaction = createMockInteraction(ChatInputCommandInteraction, {
      user: ada,
      options: createChatInputOptions({ text: 'Water the plants' }),
    })

    await module.invoke(NotesController, 'add', interaction)

    expect(store.add).toHaveBeenCalledWith('111', 'Water the plants')
    expect(getResponse(interaction).calls.map(call => call.method)).toEqual(['deferReply', 'editReply'])
  })

  it("lists only the user's own notes", async () => {
    await store.add('111', 'Water the plants')
    await store.add('222', 'Not yours')
    const interaction = createMockInteraction(ChatInputCommandInteraction, { user: ada })

    await module.invoke(NotesController, 'list', interaction)

    expect(getResponse(interaction).calls.at(-1)?.payload).toMatchObject({ content: '1. Water the plants' })
  })
})

The store's own tests provide a stand-in pool under the same token, so its queries run against a mock:

recipes/database/notes.store.spec.ts

describe('NotesStore', () => {
  it('queries through the injected pool, with the user id as a parameter', async () => {
    const query = createMockFn().mockResolvedValue({ rows: [{ id: 1, text: 'Water the plants' }] })
    // A stand-in for the pool under the same token, so the store's own code runs with no database
    const module = MeoCordTestingModule.create({
      providers: [
        { provide: DATABASE, useValue: { query } },
        { provide: NotesStore, useClass: NotesStore },
      ],
    }).compile()

    await expect(module.get(NotesStore).list('111')).resolves.toEqual([{ id: 1, text: 'Water the plants' }])
    expect(query).toHaveBeenCalledWith('SELECT id, text FROM notes WHERE user_id = $1 ORDER BY id', ['111'])
  })
})

A test of the whole app builds it from @MeoCord with MeoCordTestingModule.fromApp, replacing only the pool. The factory never runs, so nothing connects:

recipes/database/app.spec.ts

// A pool in memory, answering the two queries the store makes
function memoryPool() {
  const rows: { id: number; user_id: string; text: string }[] = []
  return {
    query: vi.fn(async (sql: string, [userId, text]: string[] = []) => {
      if (sql.startsWith('INSERT')) rows.push({ id: rows.length + 1, user_id: userId, text })
      return { rows: rows.filter(row => row.user_id === userId).map(({ id, text }) => ({ id, text })) }
    }),
  }
}

describe('the notes app', () => {
  it('saves and lists a note through its own wiring, with the database in memory', async () => {
    // Every controller, service and provider comes from @MeoCord; the database factory never runs
    const module = await MeoCordTestingModule.fromApp(App, {
      providers: [{ provide: DATABASE, useValue: memoryPool() }],
    })
      .compile()
      .init()
    const user = createMockInteraction(User, { id: '111' })
    const note = createMockInteraction(ChatInputCommandInteraction, {
      commandName: 'note',
      user,
      options: createChatInputOptions({ text: 'Water the plants' }),
    })
    const notes = createMockInteraction(ChatInputCommandInteraction, { commandName: 'notes', user })

    await module.dispatch(note)
    await module.dispatch(notes)

    expect(getResponse(notes).calls.at(-1)?.payload).toMatchObject({ content: '1. Water the plants' })
    await module.close()
  })
})

Variations

Another client

An ORM or another driver goes in the same place: construct and connect it in the factory, and close it in the returned value's onShutdown.

Native drivers

A driver with a compiled binary, such as a SQLite binding, works with self-contained builds, which pack it into dist.

Process sharding

With a process per shard, each process runs its own container, so each has its own pool. Size the pool for the number of processes. See Sharding.

Migrations

Once the schema changes over time, run migrations in a deploy step before the bot starts, rather than in the factory.

Next steps

Services and injection: providers, tokens and the shapes a provider takes.
Testing: fromApp, and replacing what a test must.
Cooldown stores: a cooldown store that injects this pool.