forked-deno-sqlite-drizzle-example.ts

import { drizzle } from "npm:@mizchi/drizzle-orm/dist/node-sqlite/index.js";
import {
  integer,
  sqliteTable,
  text,
} from "npm:@mizchi/drizzle-orm/dist/sqlite-core/index.js";
import { eq } from "npm:@mizchi/drizzle-orm/dist/index.js";

// Define the schema
const users = sqliteTable("users", {
  id: integer("id").primaryKey(),
  name: text("name").notNull(),
});

async function main() {
  // Initialize the database (in-memory)
  const db = drizzle(":memory:");
  // const db = drizzle("./out.db");

  console.log("Creating table...");
  // Create table (Synchronous based on driver implementation)
  db.run(`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);`);
  console.log("Table created.");

  console.log("Inserting data...");
  // Insert data (Synchronous)
  const insertResult = db
    .insert(users)
    .values([
      { id: 1, name: "Alice" },
      { id: 2, name: "Bob" },
    ])
    .run(); // Use run() for sync execution
  console.log("Insert result:", insertResult);

  console.log("Querying data...");
  // Query data (Synchronous)
  const allUsers = db.select().from(users).all(); // Use all() for sync execution
  console.log("All users:", allUsers);

  const userBob = db.select().from(users).where(eq(users.name, "Bob")).get(); // Use get() for single result sync
  console.log("User Bob:", userBob);

  // --- Prepared Statement Test ---
  console.log("\n--- Testing Prepared Statements ---");
  console.log("Preparing insert statement...");
  const preparedInsert = db.$client.prepare(
    // Use underlying client for prepare
    `INSERT INTO users (id, name) VALUES (?, ?)`,
  );
  console.log("Executing prepared insert...");
  preparedInsert.run(3, "Charlie"); // Sync execution
  preparedInsert.run(4, "David"); // Sync execution
  console.log("Prepared insert executed for Charlie and David.");

  console.log("Preparing select statement...");
  const preparedSelect = db.$client.prepare(
    `SELECT * FROM users WHERE name = ?`,
  ); // Use underlying client for prepare
  console.log("Executing prepared select for Charlie...");
  const userCharlie = preparedSelect.get("Charlie"); // Sync execution for single result
  console.log("User Charlie (prepared):", userCharlie);

  const allUsersAfterPrepared = db.select().from(users).all();
  console.log("All users after prepared statements:", allUsersAfterPrepared);

  // --- Transaction Test (Success) ---
  console.log("\n--- Testing Successful Transaction ---");
  try {
    db.transaction(() => {
      console.log("Inside successful transaction: Inserting Eve, Updating Bob");
      db.insert(users).values({ id: 5, name: "Eve" }).run();
      db.update(users)
        .set({ name: "Bob Updated" })
        .where(eq(users.id, 2))
        .run();
      console.log("Transaction operations completed.");
    }); // Sync execution
    console.log("Transaction committed successfully.");
  } catch (error) {
    console.error("Successful transaction failed unexpectedly:", error);
  }

  // Verify transaction results
  console.log("Querying data after successful transaction...");
  const usersAfterSuccessTx = db.select().from(users).all();
  console.log("Users after successful transaction:", usersAfterSuccessTx);

  // --- Transaction Test (Failure/Rollback) ---
  console.log("\n--- Testing Failing Transaction (Rollback) ---");
  try {
    db.transaction(() => {
      console.log(
        "Inside failing transaction: Inserting Frank, then duplicate Alice",
      );
      db.insert(users).values({ id: 6, name: "Frank" }).run();
      // Simulate an error by inserting a user with an existing primary key
      db.insert(users).values({ id: 1, name: "Duplicate Alice" }).run(); // This should fail due to PRIMARY KEY constraint
      console.log("This line should not be reached in failing transaction.");
    }); // Sync execution
  } catch (error: unknown) {
    // Explicitly type error as unknown
    // We expect an error here
    // Type guard to safely access error properties
    const errorMessage = error instanceof Error ? error.message : String(error);
    console.error("Transaction failed as expected:", errorMessage);
    console.log("Transaction rolled back.");
  }

  // Verify transaction rollback (Frank should not exist)
  console.log("Querying data after failed transaction...");
  const usersAfterFailTx = db.select().from(users).all();
  console.log(
    "Users after failed transaction (should not include Frank):",
    usersAfterFailTx,
  );
  const userFrank = db
    .select()
    .from(users)
    .where(eq(users.name, "Frank"))
    .get();
  console.log("Attempt to find Frank:", userFrank); // Should be undefined
}

main().catch((err) => {
  console.error("Error:", err);
  process.exit(1);
});

$ deno run -A poc/deno-dz-sqlite.ts  
Warning Trying to set up 'arrow2csv' bin for "/home/mizchi/lab/node_modules/.deno/[email protected]/node_modules/apache-arrow", but the entry point "/home/mizchi/lab/node_modules/.deno/[email protected]/node_modules/apache-arrow/bin/arrow2csv.cjs" doesn't exist.
Creating table...
Table created.
Inserting data...
Insert result: [Object: null prototype] { lastInsertRowid: 2, changes: 2 }
Querying data...
All users: [ { id: 1, name: "Alice" }, { id: 2, name: "Bob" } ]
User Bob: { id: 2, name: "Bob" }

--- Testing Prepared Statements ---
Preparing insert statement...
Executing prepared insert...
Prepared insert executed for Charlie and David.
Preparing select statement...
Executing prepared select for Charlie...
User Charlie (prepared): [Object: null prototype] { id: 3, name: "Charlie" }
All users after prepared statements: [
  { id: 1, name: "Alice" },
  { id: 2, name: "Bob" },
  { id: 3, name: "Charlie" },
  { id: 4, name: "David" }
]

--- Testing Successful Transaction ---
Inside successful transaction: Inserting Eve, Updating Bob
Transaction operations completed.
Transaction committed successfully.
Querying data after successful transaction...
Users after successful transaction: [
  { id: 1, name: "Alice" },
  { id: 2, name: "Bob Updated" },
  { id: 3, name: "Charlie" },
  { id: 4, name: "David" },
  { id: 5, name: "Eve" }
]

--- Testing Failing Transaction (Rollback) ---
Inside failing transaction: Inserting Frank, then duplicate Alice
Transaction failed as expected: UNIQUE constraint failed: users.id
  {
  code: 'ERR_SQLITE_ERROR',
  errcode: 23,
  errstr: 'authorization denied'
}
Transaction rolled back.
Querying data after failed transaction...
Users after failed transaction (should not include Frank): [
  { id: 1, name: "Alice" },
  { id: 2, name: "Bob Updated" },
  { id: 3, name: "Charlie" },
  { id: 4, name: "David" },
  { id: 5, name: "Eve" }
]
Attempt to find Frank: undefined