postdown/utils/db.ts

import { DatabaseSync } from "node:sqlite";

function getDbPath(): string {
  return Deno.env.get("POSTDOWN_DB_PATH") || "data/postdown.db";
}

function prepareDB(tableName: string, dbPath?: string) {
  const db = new DatabaseSync(dbPath || getDbPath());
  switch (tableName) {
    case "User":
      db.exec(`
        CREATE TABLE IF NOT EXISTS user (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name VARCHAR(128),
          email VARCHAR(128),
          password TEXT,
          created DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated DATETIME,
          UNIQUE(name)
        )
      `);
      break;
    case "Token":
      db.exec(`
        CREATE TABLE IF NOT EXISTS token (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          user_id INTEGER,
          token VARCHAR(128),
          created DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated DATETIME
        )
      `);
      break;
    case "Post":
      db.exec(`
        CREATE TABLE IF NOT EXISTS post (
          id VARCHAR(64) PRIMARY KEY,
          user_id INTEGER,
          title VARCHAR(256),
          content TEXT,
          shared BOOLEAN,
          share_password TEXT DEFAULT '',
          created DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated DATETIME
        )
      `);
      try {
        db.exec(`ALTER TABLE post ADD COLUMN share_password TEXT DEFAULT ''`);
      } catch {
        // Column already exists
      }
  }
  return db;
}

export function find(
  tableName: string,
  queryObject: { [key: string]: string | number | boolean },
  targetKeys: string[] = [],
  limit?: number,
  dbPath?: string,
) {
  const db = prepareDB(tableName, dbPath);
  const findQuery = db.prepare(
    `SELECT ${
      targetKeys.length > 0 ? targetKeys.join(", ") : "*"
    } FROM ${tableName.toLowerCase()} WHERE ${
      Object.keys(queryObject)
        .map((queryKey) => `${queryKey} = ?`)
        .join(" AND ")
    } ORDER BY updated DESC ${limit ? ` LIMIT ${limit}` : ""}`,
  );
  try {
    return findQuery.all(
      ...Object.values(queryObject).map((v) => v.toString()),
    );
  } catch (e) {
    console.error("Find error:", e);
    return [];
  } finally {
    db.close();
  }
}

export function insert(
  tableName: string,
  userInsertObject: { [key: string]: string | number | boolean },
  dbPath?: string,
) {
  const db = prepareDB(tableName, dbPath);
  const insertObject = {
    ...userInsertObject,
    updated: new Date().toISOString().slice(0, 19).replace("T", " "),
  };
  const insertQuery = db.prepare(
    `INSERT INTO ${tableName.toLowerCase()} (${
      Object.keys(insertObject).join(
        ", ",
      )
    }) VALUES (${
      Object.keys(insertObject)
        .map(() => "?")
        .join(", ")
    })`,
  );
  try {
    insertQuery.run(...Object.values(insertObject).map((v) => v.toString()));
    return find(tableName, userInsertObject, ["id"], 1, dbPath);
  } catch (e) {
    console.error("Insert error:", e);
    return [];
  } finally {
    db.close();
  }
}

export function update(
  tableName: string,
  id: number | string,
  userUpdateObject: { [key: string]: string | number | boolean },
  dbPath?: string,
) {
  const db = prepareDB(tableName, dbPath);
  const updateObject = {
    ...userUpdateObject,
    updated: new Date().toISOString().slice(0, 19).replace("T", " "),
  };
  const updateQuery = db.prepare(
    `UPDATE ${tableName.toLowerCase()} SET ${
      Object.keys(updateObject)
        .map((updateKey) => `${updateKey} = ?`)
        .join(", ")
    } WHERE id = ?`,
  );
  try {
    updateQuery.run(
      ...Object.values(updateObject).map((v) => v.toString()),
      id,
    );
    return find(tableName, userUpdateObject, ["id"], 1, dbPath);
  } catch (e) {
    console.error("Update error:", e);
    return [];
  } finally {
    db.close();
  }
}

export function del(
  tableName: string,
  queryObject: { [key: string]: string | number | boolean },
  dbPath?: string,
) {
  const db = prepareDB(tableName, dbPath);
  const deleteQuery = db.prepare(
    `DELETE FROM ${tableName.toLowerCase()} WHERE ${
      Object.keys(queryObject)
        .map((queryKey) => `${queryKey} = ?`)
        .join(" AND ")
    }`,
  );
  try {
    deleteQuery.all(...Object.values(queryObject).map((v) => v.toString()));
    return true;
  } catch (e) {
    console.error("Delete error:", e);
    return false;
  } finally {
    db.close();
  }
}