Write your own database adapter

The helpdesk's store never writes SQL. It asks a database adapter to create, find, count, update and delete rows of the tables helpdeskModel describes, and to run some of that in a transaction. The four built-in adapters answer those requests in SQL; an adapter of yours can answer them however your database does.

There are two ways to write one. If your database speaks the SQL of Postgres, MySQL, SQL Server or SQLite and has a Kysely dialect, pass the dialect to kyselyAdapter. Otherwise, implement the contract.

Pass a Kysely dialectCopy Anchor Link

kyselyAdapter is what the built-in adapters are made of: postgresAdapter({ pool }) is kyselyAdapter with Kysely's PostgresDialect. Give it another dialect and the family whose SQL it should write:

import { buildHelpdesk, kyselyAdapter } from 'better-helpdesk';

import { dialect } from './dialect'; // a Kysely Dialect for your driver

export const db = kyselyAdapter({ dialect, family: 'postgres' });

export const helpdesk = buildHelpdesk({
  db,
  // ...the rest of your configuration
});

family is 'postgres', 'mysql', 'mssql' or 'sqlite'. It decides the SQL the adapter writes, the tables' names, and which capabilities come with it: all six for Postgres, lastMessages, topTags and contactIdsByActivity for the others. The built-in migrations work too. better-helpdesk-migrate opens only the URLs it knows, so call migrate(db) from a script, as in Run the migrations from your own code.

The checks the named adapters make are not part of kyselyAdapter. sqliteAdapter turns on pragma foreign_keys and sets pragma busy_timeout = 5000 on each connection, and mysqlAdapter refuses a pool that does not keep times in UTC. Do the same in your dialect's connection setup.

Implement the contractCopy Anchor Link

An adapter of your own is an object of type AdapterInput:

import type { AdapterInput } from 'better-helpdesk/adapters';

export function myAdapter(): AdapterInput {
  const adapter: AdapterInput = {
    id: 'my-database',
    supports: { dates: false, booleans: true, json: true },
    async create(model, data) {},
    async findMany(model, query) { return []; },
    async count(model, where) { return 0; },
    async updateMany(model, where, set) { return 0; },
    async deleteMany(model, where) { return 0; },
    async transaction(fn) { return fn(adapter); },
  };
  return adapter;
}

You do not pass it to buildHelpdesk directly. createAdapter wraps it in the DatabaseAdapter the store uses, and does a share of the work for you:

• create receives every field of the model. createAdapter has filled in what the store left out: a random UUID for id, the app's clock for a now default, the model's other defaults, and null for the rest.
• Values reach you in the forms supports declares and come back to the store in its own: a Date for a time, a boolean, parsed JSON, a number for an integer and a string for a decimal.
• An in or notIn with an empty list never reaches you; it arrives as an empty or or an empty and.

Rows and valuesCopy Anchor Link

Rows are plain objects keyed by the model's field names, in camel case: contactId, lastMessageAt. How you name the stored columns is up to you; the built-in adapters use snake case.

supports says which values your database keeps natively:

Times must keep their milliseconds, and compare and sort as times. ISO strings from createAdapter all have the same length, so they sort correctly as text. A decimal arrives as a string, such as '1234.50', and must be stored exactly.

ConditionsCopy Anchor Link

Where is a tree. Its leaf is { field, op, value }, and op is eq when left out.

Without insensitive, text compares exactly, case included. That matters most for identifiers: two users of your app whose ids differ only in case are two people.

Finding rowsCopy Anchor Link

findMany(model, query) takes a FindQuery:

count(model, where) returns the number of rows that match.

Changing rowsCopy Anchor Link

create stores one row and returns nothing. It must throw when the row would repeat a primary key or a unique key of the model: the store relies on that to refuse duplicates.

updateMany(model, where, set) returns the number of rows it changed, and deleteMany(model, where) the number it removed. With where left out, both apply to every row. In set, a value of { increment: n } means: add n to the stored number, in place.

Both must be atomic per row. The store claims jobs with an updateMany whose where checks the row's current value: when several of those race for one row, exactly one may change it. It counts rate limits with { increment: 1 }: when several of those race, every one must count.

TransactionsCopy Anchor Link

transaction(fn) calls fn with an adapter whose every call is part of one transaction, and returns what fn returns. When fn throws, nothing it wrote stays, and the error reaches the caller. The store also calls transaction on the adapter it was handed inside a transaction; that call must run as part of the transaction already open, as it does in the in-memory example.

A row read with forUpdate stays locked until its transaction ends. The store numbers conversations that way: it reads the reference counter for update, then writes the next value, and two conversations opened at once must not get the same number.

Create the tablesCopy Anchor Link

The built-in migrations create tables only through the built-in adapters. With an adapter of your own, the tables are yours to create, from helpdeskModel:

import { helpdeskModel } from 'better-helpdesk/adapters';

for (const [model, table] of Object.entries(helpdeskModel)) {
  console.log(model, table.primaryKey, table.unique, Object.keys(table.fields));
}

Each table lists its fields, each with a kind (uuid, string, text, integer, boolean, date, json or decimal), whether it may be null, its default, and the table it references. string is short text that is indexed or compared exactly; text is long text.

Create every key the model declares, because the store relies on them. One of them holds only for some rows: identity has { fields: ['channel', 'externalId'], where: 'verified' }, so a verified email address or host user id belongs to one contact, while unverified copies of it may sit with several. Where your database has no partial unique index, the built-in MySQL migration shows one way round it: a unique key over expressions that are NULL unless verified is true.

Foreign keys are yours to add or leave out. The store deletes what hangs off a row itself, as the references in the model say, and never relies on the database to cascade. Indexes are a matter of speed, and the built-in migrations are a guide to which ones the queries use.

The built-in migrations also seed the reference counter. Without that row, the first conversation creates it and takes the number 1000.

When an upgrade changes helpdeskModel, your tables must follow it.

Add capabilitiesCopy Anchor Link

With the CRUD methods alone, the helpdesk works, and some reads cost more queries or more memory than they would in one query written for your database. capabilities is where you supply those queries. Each is optional:

DatabaseAdapter, Capabilities and Notification in better-helpdesk/adapters give the exact signatures. The Postgres adapter implements all six, in src/db/kysely.ts.

Start from the in-memory exampleCopy Anchor Link

examples/adapters/database-memory.ts is a complete adapter. It keeps its rows in memory, so it is no use in production, but it implements every rule on this page against the contract alone, without capabilities: conditions, NULL ordering, keys, increments, and transactions that run one at a time and roll back on a throw. Copy it, and replace its arrays with your database.

Prove it with the conformance testsCopy Anchor Link

better-helpdesk/testing holds the tests every built-in adapter passes. They run on vitest 4 or 5:

npm install --save-dev vitest

Point runAdapterTests at your adapter. adapter returns it, over a database that already has the tables; reset empties them before each test:

import { runAdapterTests } from 'better-helpdesk/testing';

import { myAdapter } from './my-adapter';

const adapter = myAdapter();

runAdapterTests({
  name: 'my database',
  adapter: () => adapter,
  reset: async () => {
    // Delete every row of every helpdesk table.
  },
});

npx vitest run lib/my-adapter.test.ts

The tests write rows and read them back: every kind of value, NULL comparisons, exact and case-insensitive text, literal search characters, times and numbers compared and ordered by value, ordering with NULLs, paging, conditions through another table, counted updates and concurrent increments, one winner among simultaneous compare-and-swap updates, locked reads, whole transactions, a repeated primary key refused, and the partial verified-identity key held. The in-memory example's own test is database-memory.test.ts.

Use itCopy Anchor Link

Wrap your adapter in createAdapter and pass it as db:

import { buildHelpdesk, createAdapter } from 'better-helpdesk';

import { myAdapter } from './my-adapter';

export const helpdesk = buildHelpdesk({
  db: createAdapter(myAdapter()),
  // ...the rest of your configuration
});

Package exports lists everything better-helpdesk/adapters exports.

To share it with others, see Community adapters.