// Deno 2.1+ 内置 SQLite 支持
const db = new Deno.Sqlite(":memory:");

// 创建表
db.execute(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TEXT DEFAULT (datetime('now'))
  )
`);

// 插入数据
db.execute("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);
db.execute("INSERT INTO users (name, email) VALUES (?, ?)", ["Bob", "bob@example.com"]);

// 查询数据
const rows = db.query("SELECT * FROM users WHERE id > ?", [0]);
for (const [id, name, email, createdAt] of rows) {
  console.log(`ID: ${id}, Name: ${name}, Email: ${email}, Created: ${createdAt}`);
}

db.close();

import { drizzle } from "npm:drizzle-orm/better-sqlite3";
import { sqliteTable, text, integer } from "npm:drizzle-orm/sqlite-core";

// 定义 Schema
const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  email: text("email").unique(),
  age: integer("age"),
});

// 连接数据库（需要 Node 兼容的 SQLite 驱动）
import Database from "npm:better-sqlite3";
const sqlite = new Database("./app.db");
const db = drizzle(sqlite);

// 插入
const newUser = await db.insert(users).values({
  name: "Alice",
  email: "alice@example.com",
  age: 30,
}).returning();
console.log(newUser);

// 查询
const allUsers = await db.select().from(users).where(eq(users.age, 30));
console.log(allUsers);

import { Client } from "https://deno.land/x/postgres@v0.19.3/mod.ts";

const client = new Client({
  user: "postgres",
  password: "password",
  database: "testdb",
  hostname: "localhost",
  port: 5432,
});

await client.connect();

// 创建表
await client.execute(`
  CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
  )
`);

// 插入数据
await client.execute(
  "INSERT INTO products (name, price, category) VALUES ($1, $2, $3)",
  ["笔记本电脑", 5999.00, "电子产品"]
);

// 查询数据
const result = await client.queryObject("SELECT * FROM products WHERE category = $1", ["电子产品"]);
console.log(result.rows);

await client.end();

import { drizzle } from "npm:drizzle-orm/node-postgres";
import { pgTable, serial, text, decimal, timestamp } from "npm:drizzle-orm/pg-core";
import { eq, gt, like } from "npm:drizzle-orm";
import pg from "npm:pg";

// 定义 Schema
const products = pgTable("products", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  price: decimal("price", { precision: 10, scale: 2 }).notNull(),
  category: text("category"),
  createdAt: timestamp("created_at").defaultNow(),
});

// 连接数据库
const pool = new pg.Pool({
  connectionString: "postgresql://user:password@localhost:5432/mydb",
});
const db = drizzle(pool);

// CRUD 操作
// 创建
const newProduct = await db.insert(products).values({
  name: "无线鼠标",
  price: "199.00",
  category: "电子产品",
}).returning();

// 查询
const electronics = await db.select().from(products).where(
  eq(products.category, "电子产品")
);

// 更新
await db.update(products).set({ price: "179.00" }).where(
  eq(products.name, "无线鼠标")
);

// 删除
await db.delete(products).where(gt(products.id, 100));

// src/db/schema.ts
import { pgTable, serial, text, integer, boolean, timestamp, jsonb } from "npm:drizzle-orm/pg-core";

// 用户表
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  username: text("username").notNull().unique(),
  email: text("email").notNull().unique(),
  passwordHash: text("password_hash").notNull(),
  avatar: text("avatar"),
  role: text("role").default("user").notNull(),
  metadata: jsonb("metadata").default({}),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 帖子表
export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  content: text("content").notNull(),
  published: boolean("published").default(false),
  authorId: integer("author_id").references(() => users.id).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

// 标签表
export const tags = pgTable("tags", {
  id: serial("id").primaryKey(),
  name: text("name").notNull().unique(),
});

import { relations } from "npm:drizzle-orm";

// 定义关系
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));

// 查询时包含关联
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
});

console.log(usersWithPosts[0].posts);

// 使用事务确保数据一致性
await db.transaction(async (tx) => {
  // 扣减库存
  await tx.update(products).set({
    stock: sql`stock - 1`,
  }).where(eq(products.id, productId));
  
  // 创建订单
  await tx.insert(orders).values({
    userId,
    productId,
    quantity: 1,
    total: product.price,
  });
});

# 初始化 Prisma
npx prisma init --datasource-provider postgresql

# 这会生成 prisma/schema.prisma

// 需要先生成客户端
// deno run -A npm:prisma generate

import { PrismaClient } from "npm:@prisma/client";

const prisma = new PrismaClient();

// 创建用户
const user = await prisma.user.create({
  data: {
    email: "alice@example.com",
    name: "Alice",
    posts: {
      create: { title: "第一篇文章", content: "Hello World" },
    },
  },
  include: { posts: true },
});
console.log(user);

// 查询用户
const users = await prisma.user.findMany({
  where: { email: { contains: "example.com" } },
  include: { posts: { where: { published: true } } },
});

await prisma.$disconnect();

import knex from "npm:knex";

const db = knex({
  client: "pg",
  connection: {
    host: "localhost",
    port: 5432,
    user: "postgres",
    password: "password",
    database: "mydb",
  },
});

// 创建表
await db.schema.createTableIfNotExists("users", (table) => {
  table.increments("id").primary();
  table.string("name", 100).notNullable();
  table.string("email", 255).unique().notNullable();
  table.timestamps(true, true);
});

// 插入
await db("users").insert({ name: "Alice", email: "alice@example.com" });

// 查询
const users = await db("users").where("name", "like", "%Ali%").select("*");

// 更新
await db("users").where({ id: 1 }).update({ name: "Alice Updated" });

// 删除
await db("users").where({ id: 1 }).del();

// 关闭连接
await db.destroy();

// 打开 KV 数据库
const kv = await Deno.openKv();

// 写入数据
await kv.set(["users", "alice"], { name: "Alice", age: 30 });
await kv.set(["users", "bob"], { name: "Bob", age: 25 });

// 读取数据
const entry = await kv.get(["users", "alice"]);
console.log(entry.value);  // { name: "Alice", age: 30 }

// 删除数据
await kv.delete(["users", "bob"]);

// 列表查询
const entries = kv.list({ prefix: ["users"] });
for await (const entry of entries) {
  console.log(entry.key, entry.value);
}

// 原子操作
const result = await kv.atomic()
  .check({ key: ["counter"], versionstamp: null })
  .set(["counter"], 1)
  .commit();

// 设置过期时间
await kv.set(["session", "abc123"], { userId: "alice" }, { expireIn: 60 * 60 * 1000 }); // 1小时过期

kv.close();

import pg from "npm:pg";

const pool = new pg.Pool({
  connectionString: "postgresql://user:password@localhost:5432/mydb",
  max: 20,           // 最大连接数
  idleTimeoutMillis: 30000,  // 空闲连接超时
  connectionTimeoutMillis: 2000, // 连接超时
});

// 自动管理连接
const result = await pool.query("SELECT NOW()");
console.log(result.rows[0]);

// 关闭连接池
await pool.end();

# 生成迁移文件
npx drizzle-kit generate:pg

# 执行迁移
npx drizzle-kit push:pg

# 或在代码中执行迁移

import { migrate } from "npm:drizzle-orm/node-postgres/migrator";
import { drizzle } from "npm:drizzle-orm/node-postgres";
import pg from "npm:pg";

const pool = new pg.Pool({ connectionString: "..." });
const db = drizzle(pool);

await migrate(db, { migrationsFolder: "./drizzle" });
console.log("迁移完成");
await pool.end();