sqlite-backend.ts

import { safeParseJobData, SQLBackend, SQLDriverConfig } from "@sidequest/backend";
import { JobData } from "@sidequest/core";
import createKnex, { Knex } from "knex";
import { hostname } from "os";
import path from "path";

const defaultKnexConfig = {
  client: "better-sqlite3",
  useNullAsDefault: true,
  migrations: {
    directory: path.join(import.meta.dirname, "..", "migrations"),
    tableName: "sidequest_migrations",
    extension: "cjs",
  },
};

/**
 * Represents a backend implementation for SQLite databases using Knex.
 *
 * This class extends the `SQLBackend` and configures a Knex instance
 * for better-sqlite3, specifying the database file path, migration directory,
 * migration table name, and file extension for migration files.
 *
 * @example
 * ```typescript
 * const backend = new SqliteBackend('./mydb.sqlite');
 * ```
 *
 * @extends SQLBackend
 */
export default class SqliteBackend extends SQLBackend {
  constructor(dbConfig: string | SQLDriverConfig = "./sidequest.sqlite") {
    const knexConfig: Knex.Config = {
      ...defaultKnexConfig,
      ...(typeof dbConfig === "string"
        ? {
            connection: {
              filename: dbConfig,
            },
          }
        : dbConfig),
    };

    const knex = createKnex(knexConfig);
    super(knex);
  }

  async claimPendingJob(queue: string, quantity = 1): Promise<JobData[]> {
    const workerName = `sidequest@${hostname()}-${process.pid}`;

    const rowsToUpdate = (await this.knex("sidequest_jobs")
      .select("id")
      .where("state", "waiting")
      .andWhere("queue", queue)
      .andWhere("available_at", "<=", new Date())
      .orderBy("inserted_at")
      .limit(quantity)) as { id: number }[];

    if (rowsToUpdate.length === 0) {
      return [];
    }

    const result = (await this.knex("sidequest_jobs")
      .update({
        claimed_by: workerName,
        claimed_at: new Date(),
        state: "claimed",
      })
      .where("state", "waiting")
      .andWhere(
        "id",
        "in",
        rowsToUpdate.map((row) => row.id),
      )
      .returning("*")) as JobData[];

    return result.map(safeParseJobData);
  }

  truncDate(date: string, unit: "m" | "h" | "d"): string {
    let format: string;
    switch (unit) {
      case "m":
        format = "%Y-%m-%dT%H:%M:00.000"; // Truncate to minute
        break;
      case "h":
        format = "%Y-%m-%dT%H:00:00.000"; // Truncate to hour
        break;
      case "d":
        format = "%Y-%m-%dT00:00:00.000"; // Truncate to day
        break;
    }
    return this.knex.raw(`strftime(?, datetime(${date} / 1000, 'unixepoch', 'localtime'))`, [format]).toQuery();
  }
}