JavaScript 项目中的 PostgreSQL 自检

JavaScript

PostgreSQL

使用 Prisma ORM 检查你的数据库

¥Introspect your database with Prisma ORM

出于本指南的目的，我们将使用包含三个表的演示 SQL 架构：

¥For the purpose of this guide, we'll use a demo SQL schema with three tables:

CREATE TABLE "public"."User" (
  id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(255),
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE "public"."Post" (
  id SERIAL PRIMARY KEY NOT NULL,
  title VARCHAR(255) NOT NULL,
  "createdAt" TIMESTAMP NOT NULL DEFAULT now(),
  content TEXT,
  published BOOLEAN NOT NULL DEFAULT false,
  "authorId" INTEGER NOT NULL,
  FOREIGN KEY ("authorId") REFERENCES "public"."User"(id)
);

CREATE TABLE "public"."Profile" (
  id SERIAL PRIMARY KEY NOT NULL,
  bio TEXT,
  "userId" INTEGER UNIQUE NOT NULL,
  FOREIGN KEY ("userId") REFERENCES "public"."User"(id)
);

注意：某些字段用双引号编写，以确保 PostgreSQL 使用正确的大小写。如果不使用双引号，PostgreSQL 只会将所有内容读取为小写字符。

¥Note: Some fields are written in double-quotes to ensure PostgreSQL uses proper casing. If no double-quotes were used, PostgreSQL would just read everything as lowercase characters.

Expand for a graphical overview of the tables

用户

¥User

栏目名称	类型	首要的关键	外键	必需的	默认
id	SERIAL	**✔️ **	不	**✔️ **	自动递增
name	VARCHAR(255)	不	不	不	*
email	VARCHAR(255)	不	不	**✔️ **	*

邮政

¥Post

栏目名称	类型	首要的关键	外键	必需的	默认
id	SERIAL	**✔️ **	不	**✔️ **	自动递增
createdAt	TIMESTAMP	不	不	**✔️ **	now()
title	VARCHAR(255)	不	不	**✔️ **	*
content	TEXT	不	不	不	*
published	BOOLEAN	不	不	**✔️ **	false
authorId	INTEGER	不	**✔️ **	**✔️ **	*

轮廓

¥Profile

栏目名称	类型	首要的关键	外键	必需的	默认
id	SERIAL	**✔️ **	不	**✔️ **	自动递增
bio	TEXT	不	不	不	*
userId	INTEGER	不	**✔️ **	**✔️ **	*

下一步，你将检查你的数据库。内省的结果将是 Prisma 架构内的 数据模型。

¥As a next step, you will introspect your database. The result of the introspection will be a data model inside your Prisma schema.

运行以下命令来检查你的数据库：

¥Run the following command to introspect your database:

npx prisma db pull

此命令读取在 .env 中定义的 DATABASE_URL 环境变量并连接到你的数据库。一旦建立连接，它就会内省数据库（即读取数据库模式）。然后，它将数据库架构从 SQL 转换为 Prisma 架构中的数据模型。

¥This command reads the DATABASE_URL environment variable that's defined in .env and connects to your database. Once the connection is established, it introspects the database (i.e. it reads the database schema). It then translates the database schema from SQL into a data model in your Prisma schema.

自省完成后，你的 Prisma 架构将更新：

¥After the introspection is complete, your Prisma schema is updated:

数据模型现在看起来与此类似（请注意，模型上的字段已重新排序以获得更好的可读性）：

¥The data model now looks similar to this (note that the fields on the models have been reordered for better readability):

prisma/schema.prisma

model Post {
  id        Int      @id @default(autoincrement())
  title     String   @db.VarChar(255)
  createdAt DateTime @default(now()) @db.Timestamp(6)
  content   String?
  published Boolean  @default(false)
  authorId  Int
  User      User     @relation(fields: [authorId], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  userId Int     @unique
  User   User    @relation(fields: [userId], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

model User {
  id      Int      @id @default(autoincrement())
  name    String?  @db.VarChar(255)
  email   String   @unique @db.VarChar(255)
  Post    Post[]
  Profile Profile?
}

Prisma ORM 的数据模型是数据库模式的声明性表示，并作为生成的 Prisma 客户端库的基础。你的 Prisma 客户端实例将公开针对这些模型定制的查询。

¥Prisma ORM's data model is a declarative representation of your database schema and serves as the foundation for the generated Prisma Client library. Your Prisma Client instance will expose queries that are tailored to these models.

现在，数据模型有一些次要的 "issues"：

¥Right now, there's a few minor "issues" with the data model:

• User 关系字段是大写的，因此不遵守 Prisma 的 命名约定 。 为了表达更多的 "semantics"，如果这个字段被称为 author 也更好，可以更好地描述 User 和 Post 之间的关系。

  ¥The User relation field is uppercased and therefore doesn't adhere to Prisma's naming conventions . To express more "semantics", it would also be nice if this field was called author to describe the relationship between User and Post better.

• User 上的 Post 和 Profile 关系字段以及 Profile 上的 User 关系字段都是大写的。为了遵守 Prisma 的 命名约定 ，两个字段都应小写为 post、profile 和 user。

  ¥The Post and Profile relation fields on User as well as the User relation field on Profile are all uppercased. To adhere to Prisma's naming conventions , both fields should be lowercased to post, profile and user.

• 即使在小写之后，User 上的 post 字段仍然有轻微的命名错误。这是因为它实际上指的是 list 个帖子 - 因此更好的名称是复数形式：posts。

  ¥Even after lowercasing, the post field on User is still slightly misnamed. That's because it actually refers to a list of posts – a better name therefore would be the plural form: posts.

这些更改与生成的 Prisma 客户端 API 相关，其中使用小写关系字段 author、posts、profile 和 user 对于 JavaScript/TypeScript 开发者来说会感觉更自然和惯用。因此，你可以 配置你的 Prisma 客户端 API。

¥These changes are relevant for the generated Prisma Client API where using lowercased relation fields author, posts, profile and user will feel more natural and idiomatic to JavaScript/TypeScript developers. You can therefore configure your Prisma Client API.

由于 关系字段 是虚拟的（即它们不直接在数据库中体现），因此你可以在 Prisma 模式中手动重命名它们，而无需接触数据库：

¥Because relation fields are virtual (i.e. they do not directly manifest in the database), you can manually rename them in your Prisma schema without touching the database:

prisma/schema.prisma

model Post {
  id        Int      @id @default(autoincrement())
  title     String   @db.VarChar(255)
  createdAt DateTime @default(now()) @db.Timestamp(6)
  content   String?
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  userId Int     @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

model User {
  id      Int      @id @default(autoincrement())
  name    String?  @db.VarChar(255)
  email   String   @unique @db.VarChar(255)
  posts   Post[]
  profile Profile?
}

在这个例子中，数据库模式确实遵循 Prisma ORM 模型的 命名约定（只有通过内省生成的虚拟关系字段不遵守它们并且需要调整）。这优化了生成的 Prisma 客户端 API 的人机工程学。

¥In this example, the database schema did follow the naming conventions for Prisma ORM models (only the virtual relation fields that were generated from introspection did not adhere to them and needed adjustment). This optimizes the ergonomics of the generated Prisma Client API.

Using custom model and field names

但有时，你可能希望对 Prisma 客户端 API 中公开的列和表的名称进行其他更改。一个常见的例子是将数据库模式中经常使用的 Snake_case 表示法转换为 PascalCase 和 CamelCase 表示法，这对于 JavaScript/TypeScript 开发者来说感觉更自然。

¥Sometimes though, you may want to make additional changes to the names of the columns and tables that are exposed in the Prisma Client API. A common example is to translate snake_case notation which is often used in database schemas into PascalCase and camelCase notations which feel more natural for JavaScript/TypeScript developers.

假设你从基于 Snake_case 表示法的内省中获得了以下模型：

¥Assume you obtained the following model from introspection that's based on snake_case notation:

model my_user {
  user_id    Int     @id @default(autoincrement())
  first_name String?
  last_name  String  @unique
}

如果你为此模型生成了 Prisma 客户端 API，它会在其 API 中选取 Snake_case 表示法：

¥If you generated a Prisma Client API for this model, it would pick up the snake_case notation in its API:

const user = await prisma.my_user.create({
  data: {
    first_name: 'Alice',
    last_name: 'Smith',
  },
})

如果你不想在 Prisma 客户端 API 中使用数据库中的表和列名称，你可以使用 @map 和 @@map 配置它们：

¥If you don't want to use the table and column names from your database in your Prisma Client API, you can configure them with @map and @@map:

model MyUser {
  userId    Int     @id @default(autoincrement()) @map("user_id")
  firstName String? @map("first_name")
  lastName  String  @unique @map("last_name")

  @@map("my_user")
}

通过这种方法，你可以根据自己的喜好命名模型及其字段，并使用 @map（对于字段名称）和 @@map（对于模型名称）来指向基础表和列。你的 Prisma 客户端 API 现在如下所示：

¥With this approach, you can name your model and its fields whatever you like and use the @map (for field names) and @@map (for models names) to point to the underlying tables and columns. Your Prisma Client API now looks as follows:

const user = await prisma.myUser.create({
  data: {
    firstName: 'Alice',
    lastName: 'Smith',
  },
})

在 配置你的 Prisma 客户端 API 页面上了解更多相关信息。

¥Learn more about this on the Configuring your Prisma Client API page.