Add to existing project

Prerequisites

In order to successfully complete this guide, you need:

See System requirements for exact version requirements.

Make sure you have your database connection URL (that includes your authentication credentials) at hand! If you don't have a database server running and just want to explore Prisma, check out the Quickstart.

Set up Prisma

As a first step, navigate into it your project directory that contains the package.json file.

Next, add the Prisma CLI as a development dependency to your project:

$npm install prisma --save-dev

You can now invoke the Prisma CLI by prefixing it with npx:

$npx prisma

Next, set up your Prisma project by creating your Prisma schema file template with the following command:

$npx prisma init

This command created a new directory called prisma which contains a file called schema.prisma and a .env file in the root of the project. schema.prisma contains the Prisma schema with your database connection and the Prisma Client generator. .env is a dotenv file for defining environment variables (used for your database connection).

Connect your database

To connect your database, you need to set the url field of the datasource block in your Prisma schema to your database connection URL:

prisma/schema.prisma
1datasource db {
2  provider = "postgresql"
3  url      = env("DATABASE_URL")
4}

.env
1DATABASE_URL = 'postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public'

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA

.env
1DATABASE_URL =
2  'postgresql://opnmyfngbknppm:XXX@ec2-46-137-91-216.eu-west-1.compute.amazonaws.com:5432/d50rgmkqi2ipus?schema=hello-prisma'

.env
1DATABASE_URL = 'postgresql://janedoe:janedoe@localhost:5432/janedoe?schema=hello-prisma'

prisma/schema.prisma
1datasource db {
2  provider = "postgresql"
3  url      = env("DATABASE_URL")
4}

prisma/schema.prisma
1datasource db {
✎  provider = "mysql"
3  url      = env("DATABASE_URL")
4}

.env
1DATABASE_URL = 'mysql://johndoe:randompassword@localhost:3306/mydb'

mysql://USER:PASSWORD@HOST:PORT/DATABASE

.env
1DATABASE_URL =
2  'mysql://johndoe:XXX@mysql–instance1.123456789012.us-east-1.rds.amazonaws.com:3306/mydb'

.env
1DATABASE_URL = 'mysql://root:randompassword@localhost:3306/mydb'

Introspect your database with Prisma

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)
);

CREATE TABLE User (
  id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
  name VARCHAR(255),
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE Post (
  id INTEGER PRIMARY KEY AUTO_INCREMENT 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 User(id)
);

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

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 introspect

This commands 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 Prisma data model.

After the introspection is complete, your Prisma schema file was 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
1model Post {
2  id        Int      @id @default(autoincrement())
3  title     String   @db.VarChar(255)
4  createdAt DateTime @default(now()) @db.Timestamp(6)
5  content   String?
6  published Boolean  @default(false)
7  authorId  Int
8  User      User     @relation(fields: [authorId], references: [id])
9}
10
11model Profile {
12  id     Int     @id @default(autoincrement())
13  bio    String?
14  userId Int     @unique
15  User   User    @relation(fields: [userId], references: [id])
16}
17
18model User {
19  id      Int      @id @default(autoincrement())
20  name    String?  @db.VarChar(255)
21  email   String   @unique @db.VarChar(255)
22  Post    Post[]
23  Profile Profile?
24}

Prisma'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.

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

• 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.
• 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.
• 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.

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.

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
1model Post {
2  id        Int      @default(autoincrement()) @id
3  title     String   @db.VarChar(255)
4  createdAt DateTime @default(now()) @db.Timestamp(6)
5  content   String?
6  published Boolean  @default(false)
✎  author    User     @relation(fields: [authorId], references: [id])
8  authorId  Int
9}
10
11model Profile {
12  id     Int     @default(autoincrement()) @id
13  bio    String?
✎  user   User    @relation(fields: [userId], references: [id])
15  userId Int     @unique
16}
17
18model User {
19  id      Int      @default(autoincrement()) @id
20  email   String   @unique @db.VarChar(255)
21  name    String?  @db.VarChar(255)
✎  posts   Post[]
✎  profile Profile?
24}

In this example, the database schema did follow the naming conventions for Prisma 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.

Sometimes though, you may want to make additional changes to the 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.

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

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

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',
  },
})

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       @default(autoincrement()) @id @map("user_id")
  firstName  String?   @map("first_name")
  lastName   String    @unique @map("last_name")

  @@map("my_user")
}

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',
  },
})

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

Install and generate Prisma Client

To get started with Prisma Client, you need to install the @prisma/client package:

$npm install @prisma/client

Notice that the @prisma/client node module references a folder named .prisma\client. The .prisma\client folder contains your generated Prisma client, and is modified each time you change the schema and run the following command:

$npx prisma generate

This command reads your Prisma schema and generates your Prisma Client library into node_modules/@prisma/client.

The @prisma/client node module references a folder named .prisma/client, which contains your unique, generated Prisma client:

Write your first query with Prisma Client

Now that you generated your Prisma Client library, you can start writing queries to read and write data in your database. So, now would be the time to start integrating Prisma Client into your application. For example, if you're building a REST API, you can use Prisma Client in your route handlers to read and write data in the database based on incoming HTTP requests. If you're building a GraphQL API, you can use Prisma Client in your resolvers to read and write data in the database based on incoming queries and mutations.

For the purpose of this guide however, you'll just create a plain Node.js script to learn how to send queries to your database using Prisma Client. Once you have an understanding of how the API works, you can start integrating it into your actual application code (e.g. REST route handlers or GraphQL resolvers).

index.ts
1import { PrismaClient } from '@prisma/client'
2
3const prisma = new PrismaClient()
4
5async function main() {
6  // ... you will write your Prisma Client queries here
7}
8
9main()
10  .catch(e => {
11    throw e
12  })
13  .finally(async () => {
14    await prisma.$disconnect()
15  })

index.js
1const { PrismaClient } = require('@prisma/client')
2
3const prisma = new PrismaClient()
4
5async function main() {
6  // ... you will write your Prisma Client queries here
7}
8
9main()
10  .catch(e => {
11    throw e
12  })
13  .finally(async () => {
14    await prisma.$disconnect()
15  })

Here's a quick overview of the different parts of the code snippet:

1. Import the PrismaClient constructor from the @prisma/client node module
2. Instantiate PrismaClient
3. Define an async function called main to send queries to the database
4. Call the main function
5. Close the database connections when the script terminates

Depending on what your models look like, the Prisma Client API will look different as well. For example, if you have a User model, your PrismaClient instance exposes a property called user on which you can call CRUD methods like findMany, create or update. The property is named after the model, but the first letter is lowercased (so for the Post model it's called post, for Profile it's called profile).

The following examples are all based on the models in the Prisma schema.

Inside the main function, add the following query to read all User records from the database and print the result:

index.ts
1async function main() {
2  const allUsers = await prisma.user.findMany()
3  console.log(allUsers)
4}

index.js
1async function main() {
2  const allUsers = await prisma.user.findMany()
3  console.log(allUsers)
4}

$npx ts-node index.ts

$node index.js

This will print an array of User records as plain old JavaScript objects.

Write data into the database

The findMany query you used in the previous section only reads data from the database. In this section, you'll learn how to write a query to write new records into the Post and User tables.

Adjust the main function to send a create query to the database:

index.ts
1async function main() {
2  await prisma.user.create({
3    data: {
4      name: 'Alice',
5      email: 'alice@prisma.io',
6      posts: {
7        create: { title: 'Hello World' },
8      },
9      profile: {
10        create: { bio: 'I like turtles' },
11      },
12    },
13  })
14
15  const allUsers = await prisma.user.findMany({
16    include: {
17      posts: true,
18      profile: true,
19    },
20  })
21  console.dir(allUsers, { depth: null })
22}

index.js
1async function main() {
2  await prisma.user.create({
3    data: {
4      name: 'Alice',
5      email: 'alice@prisma.io',
6      posts: {
7        create: { title: 'Hello World' },
8      },
9      profile: {
10        create: { bio: 'I like turtles' },
11      },
12    },
13  })
14
15  const allUsers = await prisma.user.findMany({
16    include: {
17      posts: true,
18      profile: true,
19    },
20  })
21  console.dir(allUsers, { depth: null })
22}

This code creates a new User record together with new Post and Profile records using a nested write query. The User record is connected to the two other ones via the Post.author ↔ User.posts and Profile.user ↔ User.profile relation fields respectively.

Notice that you're passing the include option to findMany which tells Prisma Client to include the posts and profile relations on the returned User objects.

$npx ts-node index.ts

$node index.js

Before moving on to the next section, you'll "publish" the Post record you just created using an update query. Adjust the main function as follows:

index.ts
1async function main() {
2  const post = await prisma.post.update({
3    where: { id: 1 },
4    data: { published: true },
5  })
6  console.log(post)
7}

index.js
1async function main() {
2  const post = await prisma.post.update({
3    where: { id: 1 },
4    data: { published: true },
5  })
6  console.log(post)
7}

$npx ts-node index.ts

$node index.js

Next steps

This section lists a number of potential next steps you can now take from here. Feel free to explore these or read the Introduction page to get a high-level overview of Prisma.

const filteredPosts = await prisma.post.findMany({
  where: {
    OR: [
      { title: { contains: "hello" },
      { content: { contains: "hello" },
    ],
  },
})

const post = await prisma.post.create({
  data: {
    title: 'Join us for Prisma Day 2020',
    author: {
      connect: { email: 'alice@prisma.io' },
    },
  },
})

const posts = await prisma.profile
  .findUnique({
    where: { id: 1 },
  })
  .user()
  .posts()

const deletedUser = await prisma.user.delete({
  where: { email: 'sarah@prisma.io' },
})