database-backends.md

layout	doc

Database Backends

The library uses Knex.js as its query layer, which means it supports every database that Knex supports. The database driver is a separate install alongside Knex.

Supported clients

Database	Knex client string	Driver package
SQLite (file-based)	better-sqlite3	better-sqlite3
PostgreSQL	pg	pg
MySQL	mysql2	mysql2
MariaDB	mysql2	mysql2
MSSQL	mssql	mssql

Install Knex and the driver together:

npm install knex <driver>

SQLite (recommended for local / personal use)

SQLite requires no server. The database is a single file on disk, which makes it ideal for a personal job search tracker.

npm install knex better-sqlite3

const tracker = new JobTracker({
  knex: {
    client: "better-sqlite3",
    connection: {
      filename: "./job-search.db", // relative to cwd, or an absolute path
    },
    useNullAsDefault: true, // required for SQLite
  },
});

::: tip useNullAsDefault SQLite does not support inserting DEFAULT for nullable columns the way PostgreSQL and MySQL do. Setting useNullAsDefault: true instructs Knex to substitute null for omitted values. Always include it for SQLite. :::

In-memory SQLite (for tests)

Use :memory: as the filename for a database that lives only for the duration of the process. Useful in unit and integration tests.

const tracker = new JobTracker({
  knex: {
    client: "better-sqlite3",
    connection: { filename: ":memory:" },
    useNullAsDefault: true,
  },
});

await tracker.initialize();
// ...run tests...
await tracker.destroy();

PostgreSQL

npm install knex pg

const tracker = new JobTracker({
  knex: {
    client: "pg",
    connection: {
      host: process.env.DB_HOST ?? "localhost",
      port: Number(process.env.DB_PORT ?? 5432),
      database: process.env.DB_NAME ?? "job_search",
      user: process.env.DB_USER ?? "postgres",
      password: process.env.DB_PASSWORD ?? "postgres",
    },
    pool: { min: 2, max: 10 },
  },
});

Alternatively, pass a connection string:

const tracker = new JobTracker({
  knex: {
    client: "pg",
    connection: process.env.DATABASE_URL,
    pool: { min: 2, max: 10 },
  },
});

MySQL / MariaDB

npm install knex mysql2

const tracker = new JobTracker({
  knex: {
    client: "mysql2",
    connection: {
      host: "localhost",
      port: 3306,
      database: "job_search",
      user: "root",
      password: "root",
    },
    pool: { min: 2, max: 10 },
  },
});

MSSQL (SQL Server)

npm install knex mssql

const tracker = new JobTracker({
  knex: {
    client: "mssql",
    connection: {
      server: "localhost",
      database: "job_search",
      user: "sa",
      password: "YourStrongPassword!",
      options: { enableArithAbort: true },
    },
  },
});

Notes on foreign key enforcement

SQLite does not enforce foreign keys by default. If you want referential integrity enforced (e.g. prevent linking an application to a non-existent company), enable it via a Knex hook:

const tracker = new JobTracker({
  knex: {
    client: "better-sqlite3",
    connection: { filename: "./job-search.db" },
    useNullAsDefault: true,
    pool: {
      afterCreate(conn: any, done: (err: Error | null, conn: any) => void) {
        conn.pragma("foreign_keys = ON");
        done(null, conn);
      },
    },
  },
});

PostgreSQL and MySQL enforce foreign keys by default.