Prisma Postgres & Astro

Dieser Inhalt ist noch nicht in deiner Sprache verfügbar.

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

Connect via 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/extension-accelerate @prisma/client

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

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

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.

Update the generator provider from prisma-client-js to prisma-client in your prisma/schema.prisma file:

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

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 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 { PrismaClient } from "../generated/prisma/client";
import { withAccelerate } from "@prisma/extension-accelerate";

const prisma = new PrismaClient({
  datasourceUrl: import.meta.env.DATABASE_URL,
}).$extends(withAccelerate());

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.

Direct TCP connection

To connect to Prisma Postgres via direct TCP, you can create a direct connection string in your Prisma Console. This allows you to connect any other ORM, database library, or tool of your choice.

Prerequisites

A Prisma Postgres database with a TCP enabled connection string

Install dependencies

This example will make a direct TCP connection using pg, a PostgreSQL client for Node.js.

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>