Aman Varshney

Posted on Oct 14

How to Use Prisma ORM with Astro

#prisma #astro #postgres #webdev

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 new prisma-client generator instead of prisma-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 prisma folder with a schema.prisma file already configured with the modern generator
A .env file with your DATABASE_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: "alice@prisma.io",
    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: "bob@prisma.io",
    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!

DEV Community