Skip to main content

SQLite

SQLite 数据源连接器将 Prisma ORM 连接到 SQLite 数据库文件。这些文件始终以 .db 结尾（例如：dev.db）。

¥The SQLite data source connector connects Prisma ORM to a SQLite database file. These files always have the file ending .db (e.g.: dev.db).

默认情况下，SQLite 连接器包含负责连接到数据库的数据库驱动程序。你可以使用 驱动适配器（预览版）通过 Prisma 客户端中的 JavaScript 数据库驱动程序连接到数据库。

¥By default, the SQLite connector contains a database driver responsible for connecting to your database. You can use a driver adapter (Preview) to connect to your database using a JavaScript database driver from Prisma Client.

示例​

¥Example

要连接到 SQLite 数据库文件，你需要在 Prisma 架构 中配置 datasource 块：

¥To connect to a SQLite database file, you need to configure a datasource block in your Prisma schema:

schema.prisma
datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

传递到 datasource 块的字段是：

¥The fields passed to the datasource block are:

  • provider：指定 sqlite 数据源连接器。

    ¥provider: Specifies the sqlite data source connector.

  • url：指定 SQLite 数据库的 连接网址。连接 URL 始终以前缀 file: 开头，然后包含指向 SQLite 数据库文件的文件路径。在本例中，该文件位于同一目录中，名为 dev.db。

    ¥url: Specifies the connection URL for the SQLite database. The connection URL always starts with the prefix file: and then contains a file path pointing to the SQLite database file. In this case, the file is located in the same directory and called dev.db.

使用 better-sqlite3 驱动程序​

¥Using the better-sqlite3 driver

从 v5.4.0 开始，你可以将 Prisma ORM 与 JavaScript 生态系统中的数据库驱动程序一起使用（而不是使用 Prisma ORM 的内置驱动程序）。你可以使用 驱动适配器 来完成此操作。

¥As of v5.4.0, you can use Prisma ORM with database drivers from the JavaScript ecosystem (instead of using Prisma ORM's built-in drivers). You can do this by using a driver adapter.

对于 SQLite，better-sqlite3 是 JavaScript 生态系统中最流行的驱动程序之一。

¥For SQLite, better-sqlite3 is one of the most popular drivers in the JavaScript ecosystem.

本节介绍如何将其与 Prisma ORM 和 @prisma/adapter-better-sqlite3 驱动程序适配器一起使用。

¥This section explains how you can use it with Prisma ORM and the @prisma/adapter-better-sqlite3 driver adapter.

1. 安装依赖​

¥ Install the dependencies

首先，安装 Prisma ORM 的 better-sqlite3 驱动程序适配器：

¥First, install Prisma ORM's driver adapter for better-sqlite3:

npm install @prisma/adapter-better-sqlite3

2. 使用驱动适配器实例化 Prisma Client​

¥ Instantiate Prisma Client using the driver adapter

现在，当你实例化 Prisma 客户端时，你需要将 Prisma ORM 驱动程序适配器的实例传递给 PrismaClient 构造函数：

¥Now, when you instantiate Prisma Client, you need to pass an instance of Prisma ORM's driver adapter to the PrismaClient constructor:

import { PrismaBetterSQLite3 } from '@prisma/adapter-better-sqlite3';
import { PrismaClient } from './generated/prisma';

const adapter = new PrismaBetterSQLite3({
  url: "file:./prisma/dev.db"
});
const prisma = new PrismaClient({ adapter });

3. 配置时间戳格式以实现向后兼容​

¥ Configure timestamp format for backward compatibility

在 SQLite 中使用驱动程序适配器时，你可以使用 timestampFormat 选项配置 DateTime 值在数据库中的存储方式。

¥When using driver adapters with SQLite, you can configure how DateTime values are stored in the database using the timestampFormat option.

默认情况下，驱动程序适配器将 DateTime 值存储为 ISO 8601 字符串，这是 SQLite 最方便的格式，因为 SQLite 日期/时间函数默认使用 ISO 8601 字符串。

¥By default, driver adapters store DateTime values as ISO 8601 strings, which is the most convenient format for SQLite since SQLite date/time functions expect ISO 8601 by default.

但是，如果你需要与 Prisma ORM 的原生 SQLite 驱动程序 100% 向后兼容（例如，在迁移现有数据库时），则应使用 unixepoch-ms 格式，该格式将时间戳存储为自 Unix 纪元以来的毫秒数：

¥However, if you need 100% backward compatibility with Prisma ORM's native SQLite driver (for example, when migrating an existing database), you should use the unixepoch-ms format, which stores timestamps as the number of milliseconds since the Unix epoch:

import { PrismaBetterSQLite3 } from '@prisma/adapter-better-sqlite3';
import { PrismaClient } from './generated/prisma';

const adapter = new PrismaBetterSQLite3({
  url: "file:./prisma/dev.db"
}, {
  timestampFormat: 'unixepoch-ms'
});
const prisma = new PrismaClient({ adapter });
信息

timestampFormat 选项适用于 @prisma/adapter-better-sqlite3 和 @prisma/adapter-libsql 驱动程序适配器。

¥The timestampFormat option is available for both @prisma/adapter-better-sqlite3 and @prisma/adapter-libsql driver adapters.

何时使用每种格式：

¥When to use each format:

  • ISO 8601（默认）：最适合新项目，并且与 SQLite 的内置日期/时间函数完美集成。

    ¥ISO 8601 (default): Best for new projects and integrates well with SQLite's built-in date/time functions.

  • unixepoch-ms：从 Prisma ORM 的原生 SQLite 驱动程序迁移时必需，以保持与现有时间戳数据的兼容性。

    ¥unixepoch-ms: Required when migrating from Prisma ORM's native SQLite driver to maintain compatibility with existing timestamp data.

SQLite 到 Prisma 模式之间的类型映射​

¥Type mapping between SQLite to Prisma schema

SQLite 连接器将 标量类型 从 数据模型 映射到原生列类型，如下所示：

¥The SQLite connector maps the scalar types from the data model to native column types as follows:

或者，请参阅 Prisma 架构参考 了解按 Prisma ORM 类型组织的类型映射。

¥Alternatively, see Prisma schema reference for type mappings organized by Prisma ORM type.

从 Prisma ORM 到 SQLite 的原生类型映射​

¥Native type mapping from Prisma ORM to SQLite

Prisma ORMSQLite
StringTEXT
BooleanBOOLEAN
IntINTEGER
BigIntINTEGER
FloatREAL
DecimalDECIMAL
DateTimeNUMERIC
JsonJSONB
BytesBLOB
EnumTEXT
注意

SQLite 没有专用的布尔类型。虽然此表显示了 BOOLEAN，但列被分配了数字亲和性（如果为 false，则存储 0；如果为 true，则存储 1）。了解更多。

¥SQLite doesn't have a dedicated Boolean type. While this table shows BOOLEAN, columns are assigned a NUMERIC affinity (storing 0 for false and 1 for true). Learn more.

警告

在 SQLite 中使用 enum 字段时，请注意以下几点：

¥When using enum fields in SQLite, be aware of the following:

  • 没有数据库级别的正确性强制执行：如果你绕过 Prisma ORM 并在数据库中存储无效的枚举条目，则 Prisma Client 查询在读取该条目时将在运行时失败。

    ¥No database-level enforcement for correctness: If you bypass Prisma ORM and store an invalid enum entry in the database, Prisma Client queries will fail at runtime when reading that entry.

  • 没有迁移级别的正确性强制执行：与 MongoDB 类似，在模式更改后可能会出现不正确的数据（因为数据库不会检查枚举）。

    ¥No migration-level enforcement for correctness: It's possible to end up with incorrect data after schema changes similarly to MongoDB (since the enums aren't checked by the database).

大数的舍入误差​

¥Rounding errors on big numbers

SQLite 是一个松散类型的数据库。如果你的架构具有 Int 类型的字段，则 Prisma ORM 会阻止你插入大于整数的值。但是，没有什么可以阻止数据库直接接受更大的数字。这些手动插入的大数字在查询时会导致舍入错误。

¥SQLite is a loosely-typed database. If your Schema has a field of type Int, then Prisma ORM prevents you from inserting a value larger than an integer. However, nothing prevents the database from directly accepting a bigger number. These manually-inserted big numbers cause rounding errors when queried.

为了避免此问题，Prisma ORM 4.0.0 及更高版本会检查从数据库中流出的数字，以验证它们是否符合整数的边界。如果数字不适合，Prisma ORM 会抛出 P2023 错误，例如：

¥To avoid this problem, Prisma ORM 4.0.0 and later checks numbers on the way out of the database to verify that they fit within the boundaries of an integer. If a number does not fit, then Prisma ORM throws a P2023 error, such as:

Inconsistent column data: Conversion failed:
Value 9223372036854775807 does not fit in an INT column,
try migrating the 'int' column type to BIGINT

连接详情​

¥Connection details

连接网址​

¥Connection URL

SQLite 连接器的连接 URL 指向文件系统上的文件。例如，以下两个路径是等效的，因为 .db 位于同一目录中：

¥The connection URL of a SQLite connector points to a file on your file system. For example, the following two paths are equivalent because the .db is in the same directory:

schema.prisma
datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

是相同的：

¥is the same as:

schema.prisma
datasource db {
  provider = "sqlite"
  url      = "file:dev.db"
}

你还可以从根目录或文件系统中的任何其他位置定位文件：

¥You can also target files from the root or any other place in your file system:

schema.prisma
datasource db {
  provider = "sqlite"
  url      = "file:/Users/janedoe/dev.db"
}