Become a Gold Sponsor

开始使用 Drizzle 和 SQLite

This guide assumes familiarity with:
  • dotenv - 管理环境变量的包 - 阅读这里
  • tsx - 运行 TypeScript 文件的包 - 阅读这里
  • libsql - 针对低查询延迟优化的 SQLite 分支,使其适合全球应用 - 阅读这里

Drizzle 对 libsqlbetter-sqlite3 驱动程序的 SQLite 连接提供原生支持。

我们将在这个入门示例中使用 libsql。但是如果想了解更多连接 SQLite 的方式,请查看我们的 SQLite 连接 页面。

基本文件结构

这是项目的基本文件结构。在 src/db 目录中,我们在 schema.ts 中有表定义。在 drizzle 文件夹中,有 SQL 迁移文件和快照。

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

第一步 - 安装所需的包

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

第二步 - 设置连接变量

Create a .env file in the root of your project and add your database connection variable:

DB_FILE_NAME=
重要

例如,如果您想在项目根目录中创建一个 SQLite 数据库文件以进行测试,您需要在实际文件名之前使用 file:,这是 LibSQL 要求的格式,如下所示:

DB_FILE_NAME=file:local.db

您可以查看 LibSQL 文档 以获取更多信息。

第三步 - 将 Drizzle ORM 连接到数据库

Create a index.ts file in the src directory and initialize the connection:

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

const db = drizzle(process.env.DB_FILE_NAME!);

If you need to provide your existing driver:

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

const client = createClient({ url: process.env.DB_FILE_NAME! });
const db = drizzle({ client });

第四步 - 创建表

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

src/db/schema.ts
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(),
});

第五步 - 设置 Drizzle 配置文件

Drizzle config - a configuration file that is used by Drizzle Kit and contains all the information about your database connection, migration folder and schema files.

Create a drizzle.config.ts file in the root of your project and add the following content:

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

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

第六步 - 将更改应用到数据库

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.

Tips

Alternatively, you can generate migrations using the drizzle-kit generate command and then apply them using the drizzle-kit migrate command:

Generate migrations:

npx drizzle-kit generate

Apply migrations:

npx drizzle-kit migrate

Read more about migration process in documentation.

第七步 - 填充和查询数据库

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

src/index.ts
import 'dotenv/config';
import { drizzle } from 'drizzle-orm/libsql';
import { eq } from 'drizzle-orm';
import { usersTable } from './db/schema';
  
const db = drizzle(process.env.DB_FILE_NAME!);

async function main() {
  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();

第八步 - 运行 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

npm
yarn
pnpm
bun
npx tsx src/index.ts
tips

We suggest using bun to run TypeScript files. With bun, such scripts can be executed without issues or additional settings, regardless of whether your project is configured with CommonJS (CJS), ECMAScript Modules (ESM), or any other module format. To run a script with bun, use the following command:

bun src/index.ts

If you don’t have bun installed, check the Bun installation docs