This file should check if there is a local db if not create one and inject the schema found in the db folder. Very incomplete file, this file is just a "blank canvas".

index.ts

// tests/db-create/index.ts
import { createClient } from '@libsql/client';
import fs from 'fs/promises';
import path from 'path';

// Database configuration
const TEST_DB_PATH = path.join(process.cwd(), 'test.db');
const PROD_DB_PATH = path.join(process.cwd(), 'local.db');

export async function initializeTestDb() {
  try {
    // Ensure any existing test database is removed
    await fs.unlink(TEST_DB_PATH).catch(() => {});

    // Create new database file
    const db = createClient({
      url: `file:${TEST_DB_PATH}`,
    });

    // We need to use the file in the db area, with all the migration stuff.
    // Initialize schema
    await db.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);

    return db;
  } catch (error) {
    console.error('Failed to initialize test database:', error);
    throw error;
  }
}

export function getDb() {
  const dbPath = process.env.NODE_ENV === 'test' ? TEST_DB_PATH : PROD_DB_PATH;
  return createClient({
    url: `file:${dbPath}`,
  });
}

// Database operations
export async function addUser(db: ReturnType<typeof createClient>, user: User) {
  const { name, email } = user;
  const result = await db.execute({
    sql: 'INSERT INTO users (name, email) VALUES (?, ?)',
    args: [name, email],
  });
  return result;
}

// Types
export interface User {
  id?: number;
  name: string;
  email: string;
  created_at?: string;
}

// src/__tests__/setup.ts
import { initializeTestDb } from '../lib/db';

export async function setup() {
  process.env.NODE_ENV = 'test';
  const db = await initializeTestDb();
  return db;
}

export async function teardown(db: ReturnType<typeof createClient>) {
  await db.execute('DROP TABLE IF EXISTS users');
  await fs.unlink(TEST_DB_PATH).catch(() => {});
}

// src/__tests__/db.test.ts
import { describe, it, expect, beforeAll, afterAll, afterEach } from 'vitest';
import { addUser, User } from '../lib/db';
// add
import { setup, teardown } from './setup';
import type { Client } from '@libsql/client';

describe('Database Operations', () => {
  let db: Client;

  beforeAll(async () => {
    db = await setup();
  });

  afterEach(async () => {
    // Clear test data after each test
    await db.execute('DELETE FROM users');
  });

  afterAll(async () => {
    await teardown(db);
  });

  it('should add a new user successfully', async () => {
    const testUser: User = {
      name: 'Test User',
      email: '[email protected]',
    };

    const result = await addUser(db, testUser);

    // Verify insertion
    const dbUser = await db.execute({
      sql: 'SELECT name, email FROM users WHERE email = ?',
      args: [testUser.email],
    });

    expect(result.rowsAffected).toBe(1);
    expect(dbUser.rows[0]).toMatchObject({
      name: testUser.name,
      email: testUser.email,
    });
  });

  it('should throw error for duplicate email', async () => {
    const testUser: User = {
      name: 'Test User',
      email: '[email protected]',
    };

    await addUser(db, testUser);
    await expect(addUser(db, testUser)).rejects.toThrow();
  });

  it('should create user with timestamp', async () => {
    const testUser: User = {
      name: 'Time Test User',
      email: '[email protected]',
    };

    await addUser(db, testUser);

    const dbUser = await db.execute({
      sql: 'SELECT created_at FROM users WHERE email = ?',
      args: [testUser.email],
    });

    expect(dbUser.rows[0].created_at).toBeDefined();
    expect(new Date(dbUser.rows[0].created_at)).toBeInstanceOf(Date);
  });
});

// scripts/create-db.ts
import { getDb } from '../src/lib/db';

async function createProductionDb() {
  const db = getDb();

  try {
    await db.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);

    console.log('Production database initialized successfully');
  } catch (error) {
    console.error('Failed to initialize production database:', error);
    throw error;
  }
}

// Only run if called directly
if (require.main === module) {
  createProductionDb();
}