Add to existing project

JavaScript
MySQL

This page walks you through the process of adding Prisma to a TypeScript project with an existing database.

This page walks you through the process of adding Prisma to a Node.js project with an existing database.

You can also watch this short video series that shows the Prisma setup process with an existing database.

Prerequisites

In order to successfully complete this guide, you need:

  • an existing Node.js (version 10 or higher) project with a package.json
  • Node.js installed on your machine
  • a PostgreSQL database server running and a database with at least one table
  • an existing Node.js (version 10 or higher) project with a package.json
  • Node.js installed on your machine
  • a MySQL database server running and a database with at least one table

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/cli --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 with the following contents:

  • schema.prisma: The Prisma schema with your database connection and the Prisma Client generator
  • .env: 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}

In this case, the url is set via an environment variable which is defined in prisma/.env:

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

You now need to adjust the connection URL to point to your own database.

The format of the connection URL for your database depends on the database you use. For PostgreSQL, it looks as follows (the parts spelled all-uppercased are placeholders for your specific connection details):

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

Here's a short explanation of each component:

  • USER: The name of your database user
  • PASSWORD: The password for your database user
  • PORT: The port where your database server is running (typically 5432 for PostgreSQL)
  • DATABASE: The name of the database
  • SCHEMA: The name of the schema inside the database

Note: In most cases, you can use the postgres:// and postgresql:// URI scheme designators interchangeably - however, depending on how your database is hosted, you might need to be specific.

If you're unsure what to provide for the schema parameter for a PostgreSQL connection URL, you can probably omit it. In that case, the default schema name public will be used.

As an example, for a PostgreSQL database hosted on Heroku, the connection URL might look similar to this:

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

When running PostgreSQL locally on Mac OS, your user and password as well as the database name typically correspond to the current user of your OS, e.g. assuming the user is called janedoe:

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}

Note that the default schema created by prisma init uses PostgreSQL, so you first need to switch the provider to mysql:

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

In this case, the url is set via an environment variable which is defined in prisma/.env:

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

You now need to adjust the connection URL to point to your own database.

The format of the connection URL for your database typically depends on the database you use. For MySQL, it looks as follows (the parts spelled all-uppercased are placeholders for your specific connection details):

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

Here's a short explanation of each component:

  • USER: The name of your database user
  • PASSWORD: The password for your database user
  • PORT: The port where your database server is running (typically 3306 for MySQL)
  • DATABASE: The name of the database

As an example, for a MySQL database hosted on AWS RDS, the connection URL might look similar to this:

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

When running MySQL locally, your connection URL typically looks similar to this:

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

Introspect your database with Prisma

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

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.

User

Column nameTypePrimary keyForeign keyRequiredDefault
idSERIAL✔️No✔️autoincrementing
nameVARCHAR(255)NoNoNo-
emailVARCHAR(255)NoNo✔️-

Post

Column nameTypePrimary keyForeign keyRequiredDefault
idSERIAL✔️No✔️autoincrementing
createdAtTIMESTAMPNoNo✔️now()
titleVARCHAR(255)NoNo✔️-
contentTEXTNoNoNo-
publishedBOOLEANNoNo✔️false
authorIdINTEGERNo✔️✔️false

Profile

Column nameTypePrimary keyForeign keyRequiredDefault
idSERIAL✔️No✔️autoincrementing
bioTEXTNoNoNo-
userIdINTEGERNo✔️✔️-

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

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

User

Column nameTypePrimary keyForeign keyRequiredDefault
idINTEGER✔️No✔️autoincrementing
nameVARCHAR(255)NoNoNo-
emailVARCHAR(255)NoNo✔️-

Post

Column nameTypePrimary keyForeign keyRequiredDefault
idINTEGER✔️No✔️autoincrementing
createdAtDATETIME(3)NoNo✔️now()
titleVARCHAR(255)NoNo✔️-
contentTEXTNoNoNo-
publishedBOOLEANNoNo✔️false
authorIdINTEGERNo✔️✔️false

Profile

Column nameTypePrimary keyForeign keyRequiredDefault
idINTEGER✔️No✔️autoincrementing
bioTEXTNoNoNo-
userIdINTEGERNo✔️✔️-

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:

Introspect your database with Prisma

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 @default(autoincrement()) @id
3 createdAt DateTime @default(now())
4 title String
5 content String?
6 published Boolean @default(false)
7 User User @relation(fields: [authorId], references: [id])
8 authorId Int
9}
10
11model Profile {
12 id Int @default(autoincrement()) @id
13 bio String?
14 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
21 name String?
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 createdAt DateTime @default(now())
4 title String
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
21 name String?
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.

Install and generate Prisma Client

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

The .prisma and @prisma folders

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

Create a new file called index.ts and add the following code to it:

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

Create a new file called index.js and add the following code to it:

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 folowing 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}

Now run the code with your current TypeScript setup. If you're using ts-node, you can run it like this:

$npx ts-node index.ts

Now run the code with this command:

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

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.authorUser.posts and Profile.userUser.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.

Run the code with your current TypeScript setup. If you're using ts-node, you can run it like this:

$npx ts-node index.ts

Run the code with this command:

$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}

Run the code with your current TypeScript setup. If you're using ts-node, you can run it like this:

$npx ts-node index.ts

Now run the code using the same command as before:

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

Continue exploring the Prisma Client API

You can send a variety of queries with the Prisma Client API. Check out the API reference and use your existing database setup from this guide to try them out.

Tip: You can use your editor's auto-completion feature to learn about the different API calls and the arguments it takes. Auto-completion is commonly invoked by hitting CTRL+SPACE on your keyboard.

Here are a few suggestions for a number of more queries you can send with Prisma Client:

Filter all Post records that contain "hello"

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

Create a new Post record and connect it to an existing User record

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

Use the fluent relations API to retrieve the Post records of a User by traversing the relations

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

Delete a User record

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

Explore the data in Prisma Studio

Prisma Studio is a visual editor for the data in your database.
You can run it with two ways:

  1. Run $ npx prisma studio in your terminal.
  2. Install the macOS app from the installers which you can find here. Currently, only macOS is supported - if you want to install the native app on Windows or Linux, please let us know by opening an issue in Prisma Studio.

Change the database schema (e.g. add more tables)

To evolve the app, you need to follow the same flow of the tutorial:

  1. Manually adjust your database schema using SQL
  2. Re-introspect your database
  3. Optionally re-configure your Prisma Client API
  4. Re-generate Prisma Client

Introspect workflow

Try a Prisma example

The prisma-examples repository contains a number of ready-to-run examples:

TypeScript
JavaScript (Node.js)
DemoStackDescription
rest-nextjs-api-routesFullstackSimple Next.js app (React) with a REST API
graphql-nextjsFullstackSimple Next.js app (React) with a GraphQL API
graphql-apollo-serverBackend onlySimple GraphQL server based on apollo-server
rest-expressBackend onlySimple REST API with Express.JS
grpcBackend onlySimple gRPC API
Edit this page on GitHub