Astro is a web framework for content-driven websites like blogs and marketing sites, but it also supports API routes, allowing you to use it as a full-stack framework like Next.js, Nuxt, SvelteKit, or Tanstack Start.
In this tutorial, we'll explore how to integrate Prisma ORM with Prisma Postgres in Astro. It's actually fairly simple and works similarly to how you'd set it up in other frameworks.
The workflow is straightforward: create API routes for GET and POST requests, use Prisma ORM to query the database, and then consume these APIs in your client-side pages.
What we'll Build
In this tutorial, we'll create a simple blog application with users and posts, complete with:
- Type-safe database queries using Prisma
- API routes for data fetching
- Seed data for development
- Server-side rendering with Astro
Quick Setup
1. Create Your Project
bun create astro@latest astro-db-app
cd astro-db-app
2. Install Dependencies
bun add -d prisma tsx
bun add @prisma/client @prisma/extension-accelerate
3. Initialize Database
bunx prisma init --db --output ./generated --generator-provider prisma-client
This command does several things:
--db: Automatically configures Prisma Postgres for you
--output ./generated: Sets the output directory for the generated Prisma Client
--generator-provider prisma-client: Uses the newprisma-clientgenerator instead ofprisma-client-js
Why these flags? The prisma-client generator is the new Rust-free version of Prisma ORM with improved performance and a smaller bundle size. These options are expected to become the default in future Prisma releases, so we're opting in early to use best practices from the start!
This will create:
- A
prismafolder with aschema.prismafile already configured with the modern generator - A
.envfile with yourDATABASE_URL
- The output path set to
./generated(relative to the schema file)
4. Define Database Models
Now we need to add some models for our database. Let's add User and Post models with a one-to-many relationship to your prisma/schema.prisma:
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
authorId Int
author User @relation(fields: [authorId], references: [id])
}
Your complete schema should now look like this:
generator client {
provider = "prisma-client"
engineType = "client"
output = "./generated"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
authorId Int
author User @relation(fields: [authorId], references: [id])
}
You can learn more about the Prisma schema in the official documentation.
6. Create a Seed File
During development, I like to add a seed file to populate the database with test data. Create prisma/seed.ts:
import { PrismaClient, Prisma } from "../prisma/generated/client";
const prisma = new PrismaClient();
const userData: Prisma.UserCreateInput[] = [
{
name: "Alice",
email: "[email protected]",
posts: {
create: [
{
title: "Join the Prisma Discord",
content: "https://pris.ly/discord",
published: true,
},
{
title: "Prisma on YouTube",
content: "https://pris.ly/youtube",
},
],
},
},
{
name: "Bob",
email: "[email protected]",
posts: {
create: [
{
title: "Follow Prisma on Twitter",
content: "https://www.twitter.com/prisma",
published: true,
},
],
},
},
];
export async function main() {
for (const u of userData) {
await prisma.user.upsert({
where: { email: u.email },
update: {},
create: u,
});
}
console.log("Seed data created successfully!");
}
main()
.then(async () => {
await prisma.$disconnect();
})
.catch(async (e) => {
console.error(e);
await prisma.$disconnect();
process.exit(1);
});
7. Push Schema to Database
Now we need to migrate the schema to the actual database. There are two ways to do this: prisma migrate or prisma db push. During development, I prefer db push because it's great for quick prototyping, and I don't want to deal with migrations when I'm just starting out.
Learn more about db push in the Prisma docs.
bunx prisma db push
This command will:
- Push your schema to the database
- Generate the Prisma Client
8. Seed the Database
Now let's run the seed file to populate our database:
bunx tsx prisma/seed.ts
Setting Up Prisma Client in Astro
9. Create Prisma Client Instance
We need to create a Prisma Client instance that we can use throughout our application. Create src/lib/prisma.ts:
import { PrismaClient } from "../../prisma/generated/client";
import { withAccelerate } from "@prisma/extension-accelerate";
const prisma = new PrismaClient({
datasourceUrl: import.meta.env.DATABASE_URL,
}).$extends(withAccelerate());
export default prisma;
10. Type Environment Variables
You may notice a TypeScript error on import.meta.env. In Astro, we need to manually type environment variables. Create src/env.d.ts:
interface ImportMetaEnv {
readonly DATABASE_URL: string;
}
interface ImportMeta {
readonly env: ImportMetaEnv;
}
Creating API Routes
11. Create Users API Endpoint
Now that all the Prisma setup is complete, let's add some API routes. Create src/pages/api/users.ts:
import type { APIRoute } from "astro";
import prisma from "../../lib/prisma";
export const GET: APIRoute = async () => {
const users = await prisma.user.findMany({
include: { posts: true },
});
return new Response(JSON.stringify(users), {
status: 200,
headers: { "Content-Type": "application/json" },
});
};
This creates a GET endpoint that queries all users with their posts and returns JSON data.
Using the API in Astro Pages
12. Display Users and Posts
Now, how do we actually use this API in an Astro page? One of Astro's powerful features is that you can directly import API route functions instead of making HTTP requests.
Create or update src/pages/index.astro:
---
import type { User, Post } from "../../prisma/generated/client";
import { GET } from "./api/users.ts";
type UserWithPosts = User & { posts: Post[] };
const response = await GET(Astro);
const users: UserWithPosts[] = await response.json();
---
<html lang="en">
<head>
<meta charset="utf-8" />
<link rel="icon" type="image/svg+xml" href="/favicon.svg" />
<meta name="viewport" content="width=device-width" />
<meta name="generator" content={Astro.generator} />
<title>Astro + Prisma Blog</title>
</head>
<body>
<h1>Users and Their Posts</h1>
<ul>
{
users.map((user: UserWithPosts) => (
<li>
<h2>{user.name}</h2>
<p>Email: {user.email}</p>
<h3>Posts:</h3>
<ul>
{user.posts.map((post: Post) => (
<li>
<strong>{post.title}</strong>
{post.published && <span> (Published)</span>}
{post.content && <p>{post.content}</p>}
</li>
))}
</ul>
</li>
))
}
</ul>
</body>
</html>
Key Advantages of This Approach
Direct Function Import: Instead of using
fetch("https://mysite.com/api/users"), we directly import the GET function. This is more efficient during build time and development.Type Safety: We can use the types generated by Prisma to ensure our data is properly typed throughout the application.
Server-Side Execution: The database queries run on the server, keeping your database credentials secure.
Flexibility: You can still access these routes via HTTP if you need to fetch data client-side with JavaScript.
Running the Development Server
Start your development server:
bun run dev
Visit http://localhost:4321 to see your users and posts displayed!
SOCIAL SHARE CARD GENERATOR