drizzle-duckdb 简单尝试

drizzle-duckdb 官方介绍是支持bun，node ，但是没说明是否支持deno，刚好尝试下

参考示例

• 基本链接

// test.ts
import { DuckDBInstance } from '@duckdb/node-api';
import { drizzle } from '@duckdbfan/drizzle-duckdb';
import { sql } from 'drizzle-orm';

async function test() {
  const instance = await DuckDBInstance.create(':memory:');
  const connection = await instance.connect();
  const db = drizzle(connection);

  const result = await db.execute(sql`SELECT 'Hello, DuckDB!' as message`);
  console.log(result[0].message); // Hello, DuckDB!

  connection.closeSync();
}

test();

 运行( 正常)

deno run --allow-ffi main.ts 

• schema + 查询模式

user.ts

import {
  pgTable,
  integer,
  text,
  doublePrecision,
  boolean,
  serial,
} from 'drizzle-orm/pg-core';
import {
  duckDbList,
  duckDbStruct,
  duckDbMap,
  duckDbJson,
  duckDbTimestamp,
} from '@duckdbfan/drizzle-duckdb';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  metadata: duckDbJson<{
    signupSource: string;
    referralCode?: string;
    preferences: { theme: string; notifications: boolean };
  }>('metadata'),
  tags: duckDbList<string>('tags', 'VARCHAR'),
  createdAt: duckDbTimestamp('created_at', { withTimezone: true }),
});

main.ts

// test.ts
import { DuckDBInstance } from "@duckdb/node-api";
import { drizzle } from "@duckdbfan/drizzle-duckdb";
import { sql } from "drizzle-orm";
import { users } from "./user.ts";
async function test() {
  const instance = await DuckDBInstance.create("./my-database.duckdb");
  const connection = await instance.connect();
  const db = drizzle(connection);
  // await db.execute(sql`CREATE SEQUENCE IF NOT EXISTS users_id_seq`);
  // await db.execute(sql`CREATE SEQUENCE IF NOT EXISTS products_id_seq`);

  // Create tables with DuckDB-specific types
  await db.execute(sql`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY DEFAULT nextval('users_id_seq'),
    email VARCHAR NOT NULL UNIQUE,
    name VARCHAR NOT NULL,
    metadata JSON,
    tags VARCHAR[],
    created_at TIMESTAMPTZ
  )
`);

  // await db.insert(users).values({
  //   email: "testv2@example.com",
  //   name: "Tesv2 User",
  //   metadata: {
  //     signupSource: "web",
  //     preferences: { theme: "dark", notifications: true },
  //   },
  //   tags: ["tag1", "tag2"],
  //   createdAt: new Date(),
  // });

  const allUsers = await db.select().from(users).offset(0).limit(10);
  console.log(allUsers);

  connection.closeSync();
}

test();

运行(可以正常)

deno run --allow-ffi  --allow-env  main.ts

说明

deno 目前与node 周边的兼容是越来越好了，是一个很不错的js runtime ，很值得尝试下

参考资料

https://github.com/leonardovida/drizzle-duckdb

https://leonardovida.github.io/drizzle-duckdb/reference/configuration.html

https://leonardovida.github.io/drizzle-duckdb/integrations/ducklake.html