Prisma Postgres & Astro

Prisma Postgres is a fully managed, serverless Postgres database built for modern web apps.

Connect with Prisma ORM (Recommended)

Prisma ORM is the recommended way to connect to your Prisma Postgres database. It provides type-safe queries, migrations, and global performance.

Prerequisites

An Astro project with an adapter installed to enable on-demand rendering (SSR).

Install dependencies and initialize Prisma

Run the following commands to install the necessary Prisma dependencies:

npm install prisma tsx --save-dev
npm install @prisma/adapter-pg @prisma/client

Once installed, initialize Prisma in your project with the following command:

npx prisma init --db --output ./generated

You’ll need to answer a few questions while setting up your Prisma Postgres database. Select the region closest to your location and a memorable name for your database, like “My Astro Project.”

This will create:

A prisma/ directory with a schema.prisma file
A .env file with a DATABASE_URL already set

Define a Model

Even if you don’t need any specific data models yet, Prisma requires at least one model in the schema in order to generate a client and apply migrations.

The following example defines a Post model as a placeholder. Add the model to your schema to get started. You can safely delete or replace it later with models that reflect your actual data.

generator client {
 provider = "prisma-client"
 output = "./generated"
}
 
datasource db {
 provider = "postgresql"
 url = env("DATABASE_URL")
}
 
model Post {
 id Int @id @default(autoincrement())
 title String
 content String?
 published Boolean @default(false)
}

Learn more about configuring your Prisma ORM setup in the Prisma schema reference.

Generate client

Run the following command to generate the Prisma Client from your schema:

npx prisma generate

Generate migration files

Run the following command to create the database tables and generate the Prisma Client from your schema. This will also create a prisma/migrations/ directory with migration history files.

npx prisma migrate dev --name init

Create a Prisma Client

Inside of /src/lib, create a prisma.ts file. This file will initialize and export your Prisma Client instance so you can query your database throughout your Astro project.

import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from '../../prisma/generated/client';
 
const connectionString = import.meta.env.DATABASE_URL;
const adapter = new PrismaPg({ connectionString });
const prisma = new PrismaClient({ adapter });
 
export default prisma;

Querying and displaying data

The following example shows fetching only your published posts with the Prisma Client sorted by id, and then displaying titles and post content in your Astro template:

---
import prisma from '../lib/prisma';
 
const posts = await prisma.post.findMany({
 where: { published: true },
 orderBy: { id: 'desc' }
});
---
 
<html>
 <head>
 <title>Published Posts</title>
 </head>
 <body>
 <h1>Published Posts</h1>
 <ul>
 {posts.map((post) => (
 <li>
 <h2>{post.title}</h2>
 {post.content && <p>{post.content}</p>}
 </li>
 ))}
 </ul>
 </body>
</html>

It is best practice to handle queries in an API route. For more information on how to use Prisma ORM in your Astro project, see the Astro + Prisma ORM Guide.

Connect with Other ORMs and Libraries

You can connect to Prisma Postgres via direct TCP using any other ORM, database library, or tool of your choice. Create a direct connection string in your Prisma Console to get started.

Prerequisites

An Astro project with an adapter installed to enable on-demand rendering (SSR).
A Prisma Postgres database with a TCP enabled connection string

Install dependencies

This example uses pg, a PostgreSQL client for Node.js to make a direct TCP connection.

Run the following command to install the pg package:

npm install pg

Query your database client

Provide your connection string to the pg client to communicate with your SQL server and fetch data from your database.

The following example of creating a table and inserting data can be used to validate your query URL and TCP connection:

---
import { Client } from 'pg';
const client = new Client({
 connectionString: import.meta.env.DATABASE_URL,
 ssl: { rejectUnauthorized: false }
});
await client.connect();
 
await client.query(`
 CREATE TABLE IF NOT EXISTS posts (
 id SERIAL PRIMARY KEY,
 title TEXT UNIQUE,
 content TEXT
 );
 
 INSERT INTO posts (title, content)
 VALUES ('Hello', 'World')
 ON CONFLICT (title) DO NOTHING;
`);
 
const { rows } = await client.query('SELECT * FROM posts');
await client.end();
---
 
<h1>Posts</h1>
<p>{rows[0].title}: {rows[0].content}</p>