在Nuxt4中集成Drizzle ORM + SQLite加密

import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core"

export const users = sqliteTable("users", {
    id: integer("id").primaryKey({ autoIncrement: true }),
    name: text("name").notNull(),
    email: text("email").notNull(),
})

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: './server/database/schema.ts',
    out: './server/database/migrations',
    dialect: 'turso', // 这里选择 turso 以兼容 libSQL
    dbCredentials: {
        // 这里的配置仅用于本地生成迁移文件
        url: 'file:server/database/data/local.db',
    },
});

import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';
import * as schema from '../database/schema';

let _db: ReturnType<typeof drizzle<typeof schema>> | null = null;

export const useDb = () => {
    if (!_db) {
        const config = useRuntimeConfig();

        // 创建 libSQL 客户端
        const client = createClient({
            url: 'file:server/database/data/app.db', // 数据库文件存放路径
            // 传入加密密钥即可开启自动加解密
            // 生产环境务必通过 .env 配置：NUXT_DB_ENCRYPTION_KEY
            encryptionKey: config.dbEncryptionKey || 'your-fallback-secret-key',
        });

        _db = drizzle(client, { schema });
    }
    return _db;
};

export default defineNuxtConfig({
    runtimeConfig: {
        // 记得把NUXT_DB_ENCRYPTION_KEY配置到 .env 文件哦
        dbEncryptionKey: process.env.NUXT_DB_ENCRYPTION_KEY, 
    }
})

import { useDb } from '#server/utils/db';
import { users } from '#server/database/schema';

export default defineEventHandler(async (event) => {
    const db = useDb();

    const newUser = await db.insert(users).values({
        name: 'user' + (Math.random() * 100000).toFixed(),
        email: `${(Math.random() * 10000000).toFixed()}@test.com`,
    }).returning();

    return newUser;
});

import { createClient } from '@libsql/client';
import { drizzle } from 'drizzle-orm/libsql';
import * as schema from './schema.js';
import { sql } from 'drizzle-orm';
import 'dotenv/config';

if (!process.env.DB_FILE_NAME) {
    throw new Error('DB_FILE_NAME is not set');
}

if (!process.env.ENCRYPTION_KEY) {
    throw new Error('ENCRYPTION_KEY is not set');
}

export const client = createClient({
    url: `file:${process.env.DB_FILE_NAME}`,
    encryptionKey: process.env.ENCRYPTION_KEY,
});

export const db = drizzle(client, { schema });

async function createTables() {
    const { migrate } = await import('drizzle-orm/libsql/migrator');
    await migrate(db, { migrationsFolder: './drizzle' });
    console.log('Tables created successfully');
}

async function checkTables() {
    const tables = await db.select({ name: sql<string>`name` })
        .from(sql`sqlite_master`)
        .where(sql`type = 'table'`)
        .all();

    console.log('Tables in the database:', tables.map(t => t.name));
    return tables.map(t => t.name);
}

createTables()
    .then(checkTables)
    .then((tables) => {
        console.log('All tables created successfully:', tables);
    })
    .catch((error) => console.error('Error creating or checking tables:', error));