Start from scratch
This page walks you through the process of setting up Prisma from scratch with your own database. It uses Prisma Migrate to create tables in your database and Prisma Client for querying it.
Prerequisites
In order to successfully complete this guide, you need:
- Node.js installed on your machine
- a PostgreSQL database server running
See System requirements for exact version requirements.
Make sure you have your database connection URL at hand. If you don't have a database server running and just want to explore Prisma, check out the Quickstart.
Create project setup
As a first step, create a project directory and navigate into it:
$$
Next, initialize a TypeScript project and add the Prisma CLI as a development dependency to it:
$$
This creates a package.json
with an initial setup for your TypeScript app.
Next, create a tsconfig.json
file and add the following configuration to it:
tsconfig.json
123456789
Next, initialize a Node.js project and add the Prisma CLI as a development dependency to it:
$$
This creates a package.json
with an initial setup for a Node.js app.
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 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
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 =2 '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 =2 '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'
Create database tables with Prisma Migrate
In this guide, you'll use Prisma Migrate to create the tables in your database. Add the following Prisma data model to your Prisma schema in prisma/schema.prisma
:
prisma/schema.prisma
12345678910111213141516171819202122232425
To map your data model to the database schema, you need to use the prisma migrate
CLI commands:
$npx prisma migrate dev --name init
This command does two things:
- It creates a new SQL migration file for this migration
- It runs the SQL migration file against the database
Note:
generate
is called under the hood by default, after runningprisma migrate dev
. If theprisma-client-js
generator is defined in your schema, this will check if@prisma/client
is installed and install it if it's missing.
Great, you now created three tables in your database with Prisma Migrate 🚀
CREATE TABLE "Post" ("id" SERIAL,"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,"updatedAt" TIMESTAMP(3) NOT NULL,"title" VARCHAR(255) NOT NULL,"content" TEXT,"published" BOOLEAN NOT NULL DEFAULT false,"authorId" INTEGER NOT NULL,PRIMARY KEY ("id"));CREATE TABLE "Profile" ("id" SERIAL,"bio" TEXT,"userId" INTEGER NOT NULL,PRIMARY KEY ("id"));CREATE TABLE "User" ("id" SERIAL,"email" TEXT NOT NULL,"name" TEXT,PRIMARY KEY ("id"));CREATE UNIQUE INDEX "Profile.userId_unique" ON "Profile"("userId");CREATE UNIQUE INDEX "User.email_unique" ON "User"("email");ALTER TABLE "Post" ADD FOREIGN KEY("authorId")REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;ALTER TABLE "Profile" ADD FOREIGN KEY("userId")REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE "Post" ("id" SERIAL,"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,"updatedAt" TIMESTAMP(3) NOT NULL,"title" TEXT NOT NULL,"content" TEXT,"published" BOOLEAN NOT NULL DEFAULT false,"authorId" INTEGER NOT NULL,PRIMARY KEY ("id"));CREATE TABLE "Profile" ("id" SERIAL,"bio" TEXT,"userId" INTEGER NOT NULL,PRIMARY KEY ("id"));CREATE TABLE "User" ("id" SERIAL,"email" TEXT NOT NULL,"name" TEXT,PRIMARY KEY ("id"));CREATE UNIQUE INDEX "Profile.userId_unique" ON "Profile"("userId");CREATE UNIQUE INDEX "User.email_unique" ON "User"("email");ALTER TABLE "Post" ADD FOREIGN KEY("authorId")REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;ALTER TABLE "Profile" ADD FOREIGN KEY("userId")REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
Install and generate Prisma Client
To get started with Prisma Client, you need to install the @prisma/client
package:
$
Notice that the install command automatically invokes prisma generate
for you which reads your Prisma schema and generates a version of Prisma Client that is tailored to your models.
Whenever you make changes to your Prisma schema in the future, you manually need to invoke prisma generate
in order to accomodate the changes in your Prisma Client API.
Write your first query with Prisma Client
Now that you have generated Prisma Client, you can start writing queries to read and write data in your database. For the purpose of this guide, you'll use a plain Node.js script to explore some basic features of Prisma Client.
Create a new file called index.ts
and add the following code to it:
index.ts
123456789101112131415
Create a new file called index.js
and add the following code to it:
index.js
123456789101112131415
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
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 // ... you will write your Prisma Client queries here+ const allUsers = await prisma.user.findMany()+ console.log(allUsers)5}
index.js
1async function main() {- // ... you will write your Prisma Client queries here+ const allUsers = await prisma.user.findMany()+ console.log(allUsers)5}
Now run the code with this command:
$
$
This should print an empty array because there are no User
records in the database yet:
[]
Write data into the database
The findMany
query you used in the previous section only reads data from the database (although it was still empty). 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
12345678910111213141516171819202122
index.js
12345678910111213141516171819202122
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 this command:
$
$
The output should look similar to this:
[{email: 'alice@prisma.io',id: 1,name: 'Alice',posts: [{content: null,createdAt: 2020-03-21T16:45:01.246Z,id: 1,published: false,title: 'Hello World',authorId: 1,}],profile: {bio: 'I like turtles',id: 1,userId: 1,}}]
Also note that allUsers
is statically typed thanks to Prisma Client's generated types. You can observe the type by hovering over the allUsers
variable in your editor. It should be typed as follows:
const allUsers: (User & {posts: Post[]})[]export type Post = {id: numbertitle: stringcontent: string | nullpublished: booleanauthorId: number | null}
The query added new records to the User
and the Post
tables:
User
id | name | |
---|---|---|
1 | "alice@prisma.io" | "Alice" |
Post
id | createdAt | title | content | published | authorId |
---|---|---|---|---|---|
1 | 2020-03-21T16:45:01.246Z | "Hello World" | null | false | 1 |
Profile
id | bio | userId |
---|---|---|
1 | "I like turtles" | 1 |
Note: The numbers in the
authorId
column onPost
anduserId
column onProfile
both reference theid
column of theUser
table, meaning theid
value1
column therefore refers to the first (and only)User
record in the database.
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
1234567
index.js
1234567
Now run the code using the same command as before:
$
Now run the code using the same command as before:
$
You will see the following output:
{id: 1,title: 'Hello World',content: null,published: true,authorId: 1}
The Post
record with an id
of 1
now got updated in the database:
Post
id | title | content | published | authorId |
---|---|---|---|---|
1 | "Hello World" | null | true | 1 |
Fantastic, you just wrote new data into your database for the first time using Prisma Client 🚀
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.
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 |