在现有项目中开始使用 Drizzle 和 Turso

Basic file structure

This is the basic file structure of the project. In the src/db directory, we have table definition in schema.ts. In drizzle folder there are sql migration file and snapshots.

📦 <project root>
 ├ 📂 drizzle
 ├ 📂 src
 │   ├ 📂 db
 │   │  └ 📜 schema.ts
 │   └ 📜 index.ts
 ├ 📜 .env
 ├ 📜 drizzle.config.ts
 ├ 📜 package.json
 └ 📜 tsconfig.json

步骤 1 - 安装所需软件包

npm i drizzle-orm @libsql/client dotenv
npm i -D drizzle-kit tsx

yarn add drizzle-orm @libsql/client dotenv
yarn add -D drizzle-kit tsx

pnpm add drizzle-orm @libsql/client dotenv
pnpm add -D drizzle-kit tsx

bun add drizzle-orm @libsql/client dotenv
bun add -D drizzle-kit tsx

步骤 2 - 设置连接变量

在项目根目录中创建一个 .env 文件并添加 Turso 数据库 URL 和身份验证令牌：

TURSO_DATABASE_URL=
TURSO_AUTH_TOKEN=

步骤 3 - 将 Drizzle ORM 连接到数据库

Drizzle 原生支持所有 @libsql/client 驱动程序变体：

import { drizzle } from 'drizzle-orm/libsql';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

import { drizzle } from 'drizzle-orm/libsql/node';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

import { drizzle } from 'drizzle-orm/libsql/web';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

import { drizzle } from 'drizzle-orm/libsql/http';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

import { drizzle } from 'drizzle-orm/libsql/ws';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

import { drizzle } from 'drizzle-orm/libsql/wasm';

const db = drizzle({ connection: {
  url: process.env.DATABASE_URL, 
  authToken: process.env.DATABASE_AUTH_TOKEN 
}});

在 src 目录中创建一个 index.ts 文件并初始化连接：

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/libsql';

// You can specify any property from the libsql connection options
const db = drizzle({ 
  connection: { 
    url: process.env.TURSO_DATABASE_URL!, 
    authToken: process.env.TURSO_AUTH_TOKEN!
  }
});

如果你需要提供现有的驱动程序：

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';

const client = createClient({ 
  url: process.env.TURSO_DATABASE_URL!, 
  authToken: process.env.TURSO_AUTH_TOKEN!
});
const db = drizzle({ client });

步骤 4 - 创建表

Create a schema.ts file in the src/db directory and declare your table:

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

export const usersTable = sqliteTable("users_table", {
  id: int().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
  age: int().notNull(),
  email: text().notNull().unique(),
});

步骤 5 - 设置 Drizzle 配置文件

Drizzle 配置 - Drizzle 套件 使用的配置文件，包含有关数据库连接、迁移文件夹和模式文件的所有信息。

在项目根目录中创建一个 drizzle.config.ts 文件并添加以下内容：

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  out: './drizzle',
  schema: './src/db/schema.ts',
  dialect: 'turso',
  dbCredentials: {
    url: process.env.TURSO_DATABASE_URL,
    authToken: process.env.TURSO_AUTH_TOKEN,
  },
});

步骤 6 - 应用更改到数据库

You can directly apply changes to your database using the drizzle-kit push command. This is a convenient method for quickly testing new schema designs or modifications in a local development environment, allowing for rapid iterations without the need to manage migration files:

npx drizzle-kit push

Read more about the push command in documentation.

npx drizzle-kit generate

npx drizzle-kit migrate

步骤 7 - 种子和查询数据库

Let’s update the src/index.ts file with queries to create, read, update, and delete users

import 'dotenv/config';
import { eq } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/libsql';
import { usersTable } from './db/schema';

async function main() {
  const db = drizzle({ 
    connection: { 
        url: process.env.TURSO_DATABASE_URL!, 
        authToken: process.env.TURSO_AUTH_TOKEN!
    }
  });

  const user: typeof usersTable.$inferInsert = {
    name: 'John',
    age: 30,
    email: 'john@example.com',
  };

  await db.insert(usersTable).values(user);
  console.log('New user created!')

  const users = await db.select().from(usersTable);
  console.log('Getting all users from the database: ', users)
  /*
  const users: {
    id: number;
    name: string;
    age: number;
    email: string;
  }[]
  */

  await db
    .update(usersTable)
    .set({
      age: 31,
    })
    .where(eq(usersTable.email, user.email));
  console.log('User info updated!')

  await db.delete(usersTable).where(eq(usersTable.email, user.email));
  console.log('User deleted!')
}

main();

步骤 8 - 运行 index.ts 文件

To run any TypeScript files, you have several options, but let’s stick with one: using tsx

You’ve already installed tsx, so we can run our queries now

Run index.ts script

npx tsx src/index.ts

yarn tsx src/index.ts

pnpm tsx src/index.ts

bun tsx src/index.ts

bun src/index.ts