This example shows how to implement a REST API with TypeScript using Express, Prisma ORM and a Prisma Postgres database.
Download this example:
npx try-prisma@latest --template orm/express --install npm --name express
Then, navigate into the project directory:
cd express
Alternative: Clone the entire repo
Clone this repository:
git clone [email protected]:prisma/prisma-examples.git --depth=1
Install npm dependencies:
cd prisma-examples/orm/express
npm install
Create a new Prisma Postgres database by executing:
npx prisma init --db
If you don't have a Prisma Data Platform account yet, or if you are not logged in, the command will prompt you to log in using one of the available authentication providers. A browser window will open so you can log in or create an account. Return to the CLI after you have completed this step.
Once logged in (or if you were already logged in), the CLI will prompt you to:
- Select a region (e.g.
us-east-1) - Enter a project name
After successful creation, you will see output similar to the following:
CLI output
Let's set up your Prisma Postgres database!
? Select your region: ap-northeast-1 - Asia Pacific (Tokyo)
? Enter a project name: testing-migration
âś” Success! Your Prisma Postgres database is ready âś…
We found an existing schema.prisma file in your current project directory.
--- Database URL ---
Connect Prisma ORM to your Prisma Postgres database with this URL:
postgresql://user:password@host:port/database
--- Next steps ---
Go to https://pris.ly/ppg-init for detailed instructions.
1. Install the PostgreSQL adapter
This example uses the PostgreSQL driver adapter. If you haven't already installed it, install it in your project:
npm install @prisma/adapter-pg
2. Apply migrations
Run the following command to create and apply a migration:
npx prisma migrate dev
3. Manage your data
View and edit your data locally by running this command:
npx prisma studio
...or online in Console:
https://console.prisma.io/{workspaceId}/{projectId}/studio
4. Send queries from your app
If you already have an existing app with Prisma ORM, you can now run it and it will send queries against your newly created Prisma Postgres instance.
5. Learn more
For more info, visit the Prisma Postgres docs: https://pris.ly/ppg-docs
Locate and copy the database URL provided in the CLI output. Then, create a .env file in the project root:
touch .envNow, paste the URL into it as a value for the DATABASE_URL environment variable. For example:
# .env
DATABASE_URL=postgresql://user:password@host:port/databaseRun the following command to create tables in your database. This creates the User and Post tables that are defined in prisma/schema.prisma:
npx prisma migrate dev --name init
This example uses the PostgreSQL driver adapter. The Prisma Client is configured in src/index.ts:
import { PrismaClient } from '../prisma/generated/client'
import { PrismaPg } from '@prisma/adapter-pg'
const pool = new PrismaPg({ connectionString: process.env.DATABASE_URL! })
const prisma = new PrismaClient({ adapter: pool })Execute the seed file in prisma/seed.ts to populate your database with some sample data, by running:
npx prisma db seed
Start the development server:
npm run dev
The server is now running on http://localhost:3000. You can now run the API requests, e.g. http://localhost:3000/feed.
You can run these curl commands to test all API endpoints:
curl -X GET http://localhost:3000/post/1curl -X GET "http://localhost:3000/feed?searchString=prisma&take=2&orderBy=desc"curl -X GET http://localhost:3000/user/3/draftscurl -X GET http://localhost:3000/userscurl -X POST http://localhost:3000/post \
-H "Content-Type: application/json" \
-d '{
"title": "My New Post",
"content": "This is an example post.",
"authorEmail": "[email protected]"
}'curl -X POST http://localhost:3000/signup \
-H "Content-Type: application/json" \
-d '{
"email": "[email protected]",
"name": "Ankur Datta",
"postData": [
{
"title": "Hello World",
"content": "This is the content of the post"
}
]
}'curl -X PUT http://localhost:3000/publish/4curl -X PUT http://localhost:3000/post/2/viewscurl -X DELETE http://localhost:3000/post/1Expand to see all API endpoints
/post/:id: Fetch a single post by itsid/feed?searchString={searchString}&take={take}&skip={skip}&orderBy={orderBy}: Fetch all published posts- Query Parameters
searchString(optional): This filters posts bytitleorcontenttake(optional): This specifies how many objects should be returned in the listskip(optional): This specifies how many of the returned objects in the list should be skippedorderBy(optional): The sort order for posts in either ascending or descending order. The value can eitherascordesc
- Query Parameters
/user/:id/drafts: Fetch user's drafts by theirid/users: Fetch all users
/post: Create a new post- Body:
title: String(required): The title of the postcontent: String(optional): The content of the postauthorEmail: String(required): The email of the user that creates the post
- Body:
/signup: Create a new user- Body:
email: String(required): The email address of the username: String(optional): The name of the userpostData: PostCreateInput[](optional): The posts of the user
- Body:
/publish/:id: Toggle the publish value of a post by itsid/post/:id/views: Increases theviewCountof aPostby oneid
/post/:id: Delete a post by itsid
Evolving the application typically requires two steps:
- Migrate your database using Prisma Migrate
- Update your application code
For the following example scenario, assume you want to add a "profile" feature to the app where users can create a profile and write a short bio about themselves.
The first step is to add a new table, e.g. called Profile, to the database. You can do this by adding a new model to your Prisma schema file file and then running a migration afterwards:
// ./prisma/schema.prisma
model User {
id Int @default(autoincrement()) @id
name String?
email String @unique
posts Post[]
+ profile Profile?
}
model Post {
id Int @id @default(autoincrement())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
title String
content String?
published Boolean @default(false)
viewCount Int @default(0)
author User? @relation(fields: [authorId], references: [id])
authorId Int?
}
+model Profile {
+ id Int @default(autoincrement()) @id
+ bio String?
+ user User @relation(fields: [userId], references: [id])
+ userId Int @unique
+}Once you've updated your data model, you can execute the changes against your database with the following command:
npx prisma migrate dev --name add-profile
This adds another migration to the prisma/migrations directory and creates the new Profile table in the database.
You can now use your PrismaClient instance to perform operations against the new Profile table. Those operations can be used to implement API endpoints in the REST API.
Update your index.ts file by adding a new endpoint to your API:
app.post('/user/:id/profile', async (req, res) => {
const { id } = req.params
const { bio } = req.body
const profile = await prisma.profile.create({
data: {
bio,
user: {
connect: {
id: Number(id)
}
}
}
})
res.json(profile)
})Restart your application server and test out your new endpoint.
/user/:id/profile: Create a new profile based on the user id- Body:
bio: String: The bio of the user
- Body:
Expand to view more sample Prisma Client queries on Profile
Here are some more sample Prisma Client queries on the new Profile model:
const profile = await prisma.profile.create({
data: {
bio: 'Hello World',
user: {
connect: { email: '[email protected]' },
},
},
})const user = await prisma.user.create({
data: {
email: '[email protected]',
name: 'John',
profile: {
create: {
bio: 'Hello World',
},
},
},
})const userWithUpdatedProfile = await prisma.user.update({
where: { email: '[email protected]' },
data: {
profile: {
update: {
bio: 'Hello Friends',
},
},
},
})If you want to try this example with another database than Postgres, you can adjust the the database connection in prisma/schema.prisma by reconfiguring the datasource block.
Learn more about the different connection configurations in the docs.
Expand for an overview of example configurations with different databases
This example already uses a standard PostgreSQL connection with the @prisma/adapter-pg adapter. You can connect to any PostgreSQL database using a standard connection string.
Modify the provider value in the datasource block in the prisma.schema file:
datasource db {
provider = "sqlite"
url = env("DATABASE_URL")
}Create an .env file and add the SQLite database connection string in it. For example:
DATABASE_URL="file:./dev.db""
Modify the provider value in the datasource block in the prisma.schema file:
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}Create an .env file and add a MySQL database connection string in it. For example:
## This is a placeholder url
DATABASE_URL="mysql://janedoe:mypassword@localhost:3306/notesapi"
Modify the provider value in the datasource block in the prisma.schema file:
datasource db {
provider = "sqlserver"
url = env("DATABASE_URL")
}Create an .env file and add a Microsoft SQL Server database connection string in it. For example:
## This is a placeholder url
DATABASE_URL="sqlserver://localhost:1433;initial catalog=sample;user=sa;password=mypassword;"
Modify the provider value in the datasource block in the prisma.schema file:
datasource db {
provider = "mongodb"
url = env("DATABASE_URL")
}Create an .env file and add a local MongoDB database connection string in it. For example:
## This is a placeholder url
DATABASE_URL="mongodb://USERNAME:PASSWORD@HOST/DATABASE?authSource=admin&retryWrites=true&w=majority"
- Check out the Prisma docs
- Join our community on Discord to share feedback and interact with other users.
- Subscribe to our YouTube channel for live demos and video tutorials.
- Follow us on X for the latest updates.
- Report issues or ask questions on GitHub.