Node ORM framework Prisma Quick Start

Introduction

Prisma is an open source, next-generation ORM that contains the following components.

Prisma Client : auto-generated, type-safe query builder for Node.js and TypeScript
Prisma Migrate : data migration system
Prisma Studio : Graphical interface for querying and editing data in the database

The Prisma client can be used in any Node.js or TypeScript backend application (including Serverless applications and microservices). It can be a REST API, a GraphQL API, a gRPC API, or anything else that requires a database.

This article will give a quick overview of the basic usage of Prisma.

Official Quickstart example

Direct access to official example

Create a project template

Create a typescript project, using gts:

mkdir -p hello-prisma
cd hello-prisma
npx gts init -y
npm install -D @vercel/ncc

Install prisma dependencies:

`1`	`npm install -D prisma`

Slightly adjust the contents of tsconfig.json as follows (the module used by gts is configured as commonjs, the main one needs to be adjusted):

{
  "extends": "./node_modules/gts/tsconfig-google.json",
  "compilerOptions": {
    "rootDir": "src",
    "outDir": "dist",
    "module": "ES2020",
    "target": "ES2020",
    "lib": ["ES2020"],
    "esModuleInterop": true,
    "moduleResolution": "node"
  },
  "include": ["src/**/*.ts", "test/**/*.ts"]
}

Initialize prisma templates:

$ npx prisma init

✔ Your Prisma schema was created at prisma/schema.prisma
  You can now open it in your favorite editor.

Next steps:
1. Set the DATABASE_URL in the .env file to point to your existing database. If your database has no tables yet, read https://pris.ly/d/getting-started
2. Set the provider of the datasource block in schema.prisma to match your database: postgresql, mysql, sqlite, sqlserver or mongodb (Preview).
3. Run prisma db pull to turn your database schema into a Prisma schema.
4. Run prisma generate to generate the Prisma Client. You can then start querying your database.

More information in our documentation:
https://pris.ly/d/getting-started

prisma cli has generated a schema template for us prisma/schema.prisma :

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

and a dotenv configuration file .env :

# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#using-environment-variables

# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server and MongoDB (Preview).
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"

Basic workflow

Adjust the database connection and just append the table structure definition on top of that. For example, we define a user table:

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  email     String   @unique
  name      String?
}

model naming needs to meet the uppercase hump style and cannot use reserved words, refer to the official documentation prisma-schema-reference#naming-conventions

Run npx prisma migrate dev --name init to create the database and generate the migration script:

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "email" TEXT NOT NULL,
    "name" TEXT,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

Local rapid prototyping also allows you to use db push to modify the database directly without creating a migrate script, which is useful in scenarios where you are iterating locally and don’t care about the changes in between.

However, in scenarios where the product environment expects to safely migrate the database without losing data, migrate deploy should be used instead of db push. For a description of db push and a comparison with migrate see the official documentation: db-push

Modify the schema to add a field:

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  email     String   @unique
  name      String?
  // 增加一个 foo 字段，定义为 VARCHAR 类型
  foo       String?  @db.VarChar(10)
}

Run npx prisma migrate dev --name add_foo_column and the resulting sql is as follows:

1
2

-- AlterTable
ALTER TABLE "User" ADD COLUMN     "foo" VARCHAR(10);

Later, modify prisma/schema.prisma and then run npx prisma migrate dev again to synchronize the changes to the database. If you append the --create-only parameter, only the migration.sql file will be generated and not synchronized to the database.

The migrate script (mainly the -migrate dev and -migrate reset commands require the shadow database) requires the createdb permission of the database, otherwise it will report an error. If your environment does not provide createdb permissions, you need to create a separate shadow database, specified by adding shadowDatabaseUrl = env('SHADOW_DATABASE_URL') to the datasource db {} configuration block, see the official documentation: shadow-database

The product environment runs the migrate script directly with migrate deploy or migrate resolve without creating a shadow database.

Schema Definition

Note that Prisma maintains a default set of schema to database type mappings, which can be changed by appending the @db.<database_type> parameter if it does not meet your needs, e.g. the String type maps to the TEXT type of the database by default, and you may want to map to the VARCHAR type:

1
2
3

model User {
  userName String @db.VarChar(10)
}

prisma schema All supported field types and the database types that can be mapped ( @db.<database_type> ) can be found in the official documentation: model-field-scalar-types

Other common schema definitions

Annotations

prisma supports two types of comments // and ///, the former is just a normal comment on the code, the latter appears in the node syntax tree (AST) as Data Model Meta Format (DMMF):

/// This comment will get attached to the `User` node in the AST
model User {
  /// This comment will get attached to the `id` node in the AST
  id     Int   @default(autoincrement())
  // This comment is just for you
  weight Float /// This comment gets attached to the `weight` node
}

// This comment is just for you. It will not
// show up in the AST.

/// This comment will get attached to the
/// Customer node.
model Customer {}

Former prisma does not yet support database level table and field comments (DDL COMMENT), see the official issue

Currently you can only generate sql with -create-only parameter and change it by hand

Renaming tables/fields

To circumvent database case issues, the general database best practice is to name tables and fields consistently using lowercase underscores. For prisma, use the @@map and @map function definitions:

model User {
  id     Int @id
  // @map 用于定义列名
  cardId Int @map("card_id")

  // @@map 用于定义表名
  @@map("user")
}

The corresponding generated sql is as follows:

-- CreateTable
CREATE TABLE "user" (
    "id" INTEGER NOT NULL,
    "card_id" INTEGER NOT NULL,

    CONSTRAINT "user_pkey" PRIMARY KEY ("id")
);

For naming indexes, constraints, etc., refer to the official documentation: names-in-underlying-databas#using-custom-constraint-index-names

Field defaults ( `@default` )

@default can define the default value of a field, the parameters can be static fixed values like 5 , false etc. or several functions provided by prisma like autoincrement() , uuid() , now() etc. See the documentation: prisma-schema- reference#default

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
}

Enumeration types

Fields can be defined as enumerated types:

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  role  Role    @default(USER)
}

enum Role {
  USER
  ADMIN
}

Corresponding to sql:

-- CreateEnum
CREATE TYPE "Role" AS ENUM ('USER', 'ADMIN');

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT,
    "role" "Role" NOT NULL DEFAULT E'USER',

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

Automatically store the update time ( `@updatedAt` )

This property automatically sets the update time:

model Post {
  id        String   @id
  createdAt DateTime @default(now()) @db.Timestamptz
  updatedAt DateTime @updatedAt @db.Timestamptz
}

Indexes ( `@@index` )

Indexes can be created directly using the @@index function:

model Post {
  id      Int     @id @default(autoincrement())
  title   String
  content String?

  @@index([title, content])
}

If you want to customize the index type, you can use npx prisma migrate dev --create-only to generate a migration.sql file and then modify it in migration.sql.

If you expect to define them in the schema file, you need to enable the extendedIndexes preview feature:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["extendedIndexes"]
}

You can use the type parameter in @@index to specify the index type:

model Example {
  id    Int @id
  value Int

  @@index([value], type: Hash)
}

For documentation on the @@index section refer to: prisma-schema-reference#index

Documentation for the extendedIndexes section: indexes

Field constraints

primary-key ( `@id` / `@@id` )

Single primary key

1
2
3

model Table {
  id Int @id @default(autoincrement())
}

Generate sql:

-- CreateTable
CREATE TABLE "Table" (
    "id" SERIAL NOT NULL,

    CONSTRAINT "Table_pkey" PRIMARY KEY ("id")
);

Union Primary Key

model Table {
  firstName String @db.VarChar(10)
  lastName  String @db.VarChar(10)

  @@id([firstName, lastName])
}

Generate sql:

-- CreateTable
CREATE TABLE "Table" (
    "firstName" VARCHAR(10) NOT NULL,
    "lastName" VARCHAR(10) NOT NULL,

    CONSTRAINT "Table_pkey" PRIMARY KEY ("firstName","lastName")
);

uniqueness ( `@unique` / `@@unique` )

Single column uniqueness

model User {
  id    Int     @id @default(autoincrement())
  email String? @unique
  name  String
}

Multi-column uniqueness

model User {
  id        Int  @id
  firstname Int
  lastname  Int
  card      Int?

  @@unique([firstname, lastname, card])
}

Non-Null Constraints

The default field types are all non-null, and can be null by adding ? and you’re done:

model User {
  id        Int  @id
  firstname Int
  lastname  Int
  card      Int?
}

foreign key constraint ( `@relation` )

is a little trickier, as you need to define the mutual correspondence between the two models as follows:

model User {
  id        Int    @id
  firstname String @db.VarChar(10)
  lastname  String @db.VarChar(10)
  Post      Post[]
}

model Post {
  id      Int    @id
  author  User   @relation(fields: [userId], references: [id])
  content String @db.VarChar(200)
  userId  Int
}

Generate sql:

-- CreateTable
CREATE TABLE "User" (
    "id" INTEGER NOT NULL,
    "firstname" VARCHAR(10) NOT NULL,
    "lastname" VARCHAR(10) NOT NULL,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Post" (
    "id" INTEGER NOT NULL,
    "content" VARCHAR(200) NOT NULL,
    "userId" INTEGER NOT NULL,

    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
);

-- AddForeignKey
ALTER TABLE "Post" ADD CONSTRAINT "Post_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

There is a convenient way to quickly create such mappings, which is to use the prisma format function directly.

For example, first create a table association like Hibernate:

model User {
  id        Int    @id
  firstname String @db.VarChar(10)
  lastname  String @db.VarChar(10)
}

model Post {
  id      Int    @id
  author  User
  content String @db.VarChar(200)
}

Then execute npx prisma format at the command line, or install the plugin Prisma in vscode and format it directly in vscode The above model mapping relationships are generated automatically.

For more definitions of mapping relations (one-to-one, one-to-many, many-to-many), please refer to the official documentation: relations

Using Prisma Client

After defining the schema and completing the migrate, you need to call the schema through prisma-client to complete the database operations.

`1`	`npm install @prisma/client`

After installing @prisma/client for the first time, prisma generate will be called automatically to generate a customized client, later you need to call npx prisma generate manually to generate a new client model after each schema change.

Just introduce the PrismaClient component in your project:

1
2
3

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

To print sql you can configure it like this:

1
2
3

const prisma = new PrismaClient({
  log: ["query", "info", "warn", "error"],
});

PrismaClient uses connection pooling by default. For the configuration of connection pooling, please refer to the official documentation: connection-pool

Basic CURD operations

For detailed basic CURD operations refer directly to the official documentation for a complete example: crud

It’s all relatively simple and easy to understand at a glance. The basic model-based CURD functions are mainly the following:

findUnique
findFirst
findMany
create
update
upsert
delete
createMany
updateMany
deleteMany
count
aggregate
groupBy

In addition, you can control the fields returned via the select attribute:

// Returns an object or null
const getUser: object | null = await prisma.user.findUnique({
  where: {
    id: 22,
  },
  select: {
    email: true,
    name: true,
  },
});

See: select-fields for more details

Currently prisma does not support exclusion of specific fields (e.g. password), this design is being explored, see issue: prisma/prisma#5042

If you want to use correlation queries like join, you can refer to the documentation: relation-queries

For JSONB related queries, the prisma API also has support: working-with-json-fields fields/working-with-json-fields)

Transactional

Each basic CURD operation runs in a separate transaction (you can see the wrapper BEGIN....COMMIT when printing the sql), and you can also explicitly use the $transaction function to wrap multiple operations in a single transaction:

const [posts, totalPosts] = await prisma.$transaction([
  prisma.post.findMany({ where: { title: { contains: "prisma" } } }),
  prisma.post.count(),
]);

Calling sql

prisma provides some functions that contain the Raw keyword and can be used directly with sql, such as :

1
2
3

const result = await prisma.$queryRaw`SELECT * FROM User`;
const result: number =
  await prisma.$executeRaw`UPDATE User SET active = true WHERE emailValidated = true`;

All functions that call sql directly can be found in the official documentation: raw-database-access

Extended Reading

Backend with TypeScript PostgreSQL & Prisma: Data Modeling & CRUD

Table of Contents

Introduction

Official Quickstart example

Create a project template

Basic workflow

Schema Definition

Annotations

Renaming tables/fields

Field defaults ( @default )

Enumeration types

Automatically store the update time ( @updatedAt )

Indexes ( @@index )

Field constraints

primary-key ( @id / @@id )

uniqueness ( @unique / @@unique )

Non-Null Constraints

foreign key constraint ( @relation )

Using Prisma Client

Basic CURD operations

Transactional

Calling sql

Extended Reading

Field defaults ( `@default` )

Automatically store the update time ( `@updatedAt` )

Indexes ( `@@index` )

primary-key ( `@id` / `@@id` )

uniqueness ( `@unique` / `@@unique` )

foreign key constraint ( `@relation` )