Add to existing project
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 project with a
package.json
- Node.js installed on your machine
- a PostgreSQL database server running and a database with at least one table
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:
$
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:
$
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}
In this case, the url
is set via an environment variable which is defined in .env
:
.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 userPASSWORD
: The password for your database userPORT
: The port where your database server is running (typically5432
for PostgreSQL)DATABASE
: The name of the databaseSCHEMA
: The name of the schema inside the database
Note: In most cases, you can use the
postgres://
andpostgresql:// 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:
.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
:
.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 .env
:
.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 userPASSWORD
: The password for your database userPORT
: The port where your database server is running (typically3306
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:
.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:
.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 name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | SERIAL | ✔️ | No | ✔️ | autoincrementing |
name | VARCHAR(255) | No | No | No | - |
email | VARCHAR(255) | No | No | ✔️ | - |
Post
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | SERIAL | ✔️ | No | ✔️ | autoincrementing |
createdAt | TIMESTAMP | No | No | ✔️ | now() |
title | VARCHAR(255) | No | No | ✔️ | - |
content | TEXT | No | No | No | - |
published | BOOLEAN | No | No | ✔️ | false |
authorId | INTEGER | No | ✔️ | ✔️ | false |
Profile
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | SERIAL | ✔️ | No | ✔️ | autoincrementing |
bio | TEXT | No | No | No | - |
userId | INTEGER | No | ✔️ | ✔️ | - |
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 name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | INTEGER | ✔️ | No | ✔️ | autoincrementing |
name | VARCHAR(255) | No | No | No | - |
email | VARCHAR(255) | No | No | ✔️ | - |
Post
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | INTEGER | ✔️ | No | ✔️ | autoincrementing |
createdAt | DATETIME(3) | No | No | ✔️ | now() |
title | VARCHAR(255) | No | No | ✔️ | - |
content | TEXT | No | No | No | - |
published | BOOLEAN | No | No | ✔️ | false |
authorId | INTEGER | No | ✔️ | ✔️ | false |
Profile
Column name | Type | Primary key | Foreign key | Required | Default |
---|---|---|---|---|---|
id | INTEGER | ✔️ | No | ✔️ | autoincrementing |
bio | TEXT | No | No | No | - |
userId | INTEGER | No | ✔️ | ✔️ | - |
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:
$
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 @default(autoincrement()) @id3 createdAt DateTime @default(now())4 title String5 content String?6 published Boolean @default(false)7 User User @relation(fields: [authorId], references: [id])8 authorId Int9}1011model Profile {12 id Int @default(autoincrement()) @id13 bio String?14 User User @relation(fields: [userId], references: [id])15 userId Int @unique16}1718model User {19 id Int @default(autoincrement()) @id20 email String @unique21 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 calledauthor
to describe the relationship betweenUser
andPost
better. - The
Post
andProfile
relation fields onUser
as well as theUser
relation field onProfile
are all uppercased. To adhere to Prisma's naming conventions , both fields should be lowercased topost
,profile
anduser
. - Even after lowercasing, the
post
field onUser
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()) @id3 createdAt DateTime @default(now())4 title String5 content String?6 published Boolean @default(false)✎ author User @relation(fields: [authorId], references: [id])8 authorId Int9}1011model Profile {12 id Int @default(autoincrement()) @id13 bio String?✎ user User @relation(fields: [userId], references: [id])15 userId Int @unique16}1718model User {19 id Int @default(autoincrement()) @id20 email String @unique21 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()) @idfirst_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:
$
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:
$
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).
Create a new file called index.ts
and add the following code to it:
index.ts
1import { PrismaClient } from "@prisma/client"23const prisma = new PrismaClient()45async function main() {6 // ... you will write your Prisma Client queries here7}89main()10 .catch(e => {11 throw e12 })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")23const prisma = new PrismaClient()45async function main() {6 // ... you will write your Prisma Client queries here7}89main()10 .catch(e => {11 throw e12 })13 .finally(async () => {14 await prisma.$disconnect()15 })
Here's a quick overview of the different parts of the code snippet:
- Import the
PrismaClient
constructor from the@prisma/client
node module - Instantiate
PrismaClient
- Define an
async
function calledmain
to send queries to the database - Call the
main
function - 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:
$
Now run the code with this command:
$
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() {23 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 })1516 const allUsers = await prisma.user.findMany({17 include: {18 posts: true,19 profile: true20 },21 })22 console.dir(allUsers, { depth: null })23}
index.js
1async function main() {23 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 })1516 const allUsers = await prisma.user.findMany({17 include: {18 posts: true,19 profile: true20 },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.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.
Run the code with your current TypeScript setup. If you're using ts-node
, you can run it like this:
$
Run the code with this command:
$
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:
$
Now run the code using the same command as before:
$
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:
- Run
$ npx prisma studio
in your terminal. - Install the desktop app from the installers. Windows, macOS and Linux are supported.
Change the database schema (e.g. add more tables)
To evolve the app, you need to follow the same flow of the tutorial:
- Manually adjust your database schema using SQL
- Re-introspect your database
- Optionally re-configure your Prisma Client API
- Re-generate Prisma Client
Try a Prisma example
The prisma-examples
repository contains a number of ready-to-run examples:
Demo | Stack | Description |
---|---|---|
rest-nextjs-api-routes | Fullstack | Simple Next.js app (React) with a REST API |
graphql-nextjs | Fullstack | Simple Next.js app (React) with a GraphQL API |
graphql-apollo-server | Backend only | Simple GraphQL server based on apollo-server |
rest-express | Backend only | Simple REST API with Express.JS |
grpc | Backend only | Simple gRPC API |