Drizzle <> Appwrite Postgres

Appwrite 提供专用的原生 PostgreSQL 数据库，可通过 TLS 直接连接。Appwrite 没有专用驱动或抽象层，因此你可以像连接其他 PostgreSQL 服务器一样，搭配 node-postgres 或 postgres.js 驱动使用 Drizzle。

要获取连接字符串，请在 Appwrite Console 中打开数据库并点击 Credentials。Drizzle 选项卡中包含可直接使用的代码片段，DSN 和 .env 选项卡中则包含原始连接字符串。你也可以通过 Appwrite API 调用 postgresql.get() 获取。连接字符串已包含 sslmode=require，因此无需额外配置 TLS。

DATABASE_URL="postgresql://admin:<password>@db-<hash>.<region>.appwrite.center:5432/<database>?sslmode=require"

第一步：安装依赖包

npm i drizzle-orm@rc pg
npm i -D drizzle-kit@rc

yarn add drizzle-orm@rc pg
yarn add -D drizzle-kit@rc

pnpm add drizzle-orm@rc pg
pnpm add -D drizzle-kit@rc

bun add drizzle-orm@rc pg
bun add -D drizzle-kit@rc

npm i drizzle-orm@rc postgres
npm i -D drizzle-kit@rc

yarn add drizzle-orm@rc postgres
yarn add -D drizzle-kit@rc

pnpm add drizzle-orm@rc postgres
pnpm add -D drizzle-kit@rc

bun add drizzle-orm@rc postgres
bun add -D drizzle-kit@rc

第二步：初始化驱动并执行查询

// Make sure to install the 'pg' package
import { drizzle } from 'drizzle-orm/node-postgres';

const db = drizzle(process.env.DATABASE_URL);

const result = await db.execute('select 1');

// Make sure to install the 'postgres' package
import { drizzle } from 'drizzle-orm/postgres-js';

const db = drizzle(process.env.DATABASE_URL);

const result = await db.execute('select 1');

如果需要传入已有驱动：

// Make sure to install the 'pg' package
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});
const db = drizzle({ client: pool });

const result = await db.execute('select 1');

// Make sure to install the 'postgres' package
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const queryClient = postgres(process.env.DATABASE_URL);
const db = drizzle({ client: queryClient });

const result = await db.execute('select 1');

无服务器环境中的连接池

Appwrite 在同一主机名的 6432 端口上运行连接池。在无服务器和边缘环境中，请通过连接池转发应用流量，避免短暂运行的实例耗尽数据库引擎的连接上限；同时让 Drizzle Kit 连接引擎的直连端口 5432，因为迁移会执行需要会话级连接的 DDL。

# Runtime: pooled, transaction mode
DATABASE_URL="postgresql://admin:<password>@db-<hash>.<region>.appwrite.center:6432/<database>?sslmode=require"

# Migrations & introspection: direct connection to the engine
DIRECT_URL="postgresql://admin:<password>@db-<hash>.<region>.appwrite.center:5432/<database>?sslmode=require"

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/schema.ts',
  out: './drizzle',
  dbCredentials: {
    url: process.env.DIRECT_URL!,
  },
});

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const queryClient = postgres(process.env.DATABASE_URL, { prepare: false });
const db = drizzle({ client: queryClient });

下一步