Zum Inhalt springen

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.

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

This guide uses Prisma ORM 8. If you are on Prisma ORM 7, follow the Prisma ORM 7 guide for Astro in Prisma docs instead.

Install dependencies and initialize Prisma

Section titled “Install dependencies and initialize Prisma”

Run the following command in your project to install Prisma ORM and create its starter files:

Terminal window
npx prisma@latest orm init --target postgres --authoring psl

This installs the prisma CLI, @prisma/orm-postgres, and dotenv, and creates:

  • A prisma.config.ts file that loads your DATABASE_URL from .env
  • A src/prisma/contract.prisma file with a starter data model
  • A src/prisma/db.ts file that exports the Prisma ORM client
  • A .env.example file

If you already have a Prisma Postgres database, add its connection string to .env as DATABASE_URL. Otherwise, run the following command to create a database and automatically write its connection string:

Terminal window
npx create-db@latest --env .env

The command prints a claim URL. Open it and sign in to keep the database. Prisma deletes unclaimed databases after 24 hours.

Prisma requires at least one model in the contract to create the database tables. The following example defines a Post model as a placeholder. Replace the contents of src/prisma/contract.prisma with it to get started. You can safely delete or replace it later with models that reflect your actual data.

src/prisma/contract.prisma
// use prisma-8
model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
}

Keep the // use prisma-8 line at the top of the file: it tells Prisma which contract syntax the file uses. Learn more about configuring your Prisma ORM setup in the Prisma contract reference.

Run the following command to generate the typed files that src/prisma/db.ts imports. Run it again whenever you change your contract:

Terminal window
npx prisma contract emit

Run the following command to create the tables from your contract. This also creates a migrations/ directory that records the state of your database:

Terminal window
npx prisma db init

If the command stops with Connection terminated unexpectedly, a database you just created is still starting. Wait a few seconds and run it again.

After changing your contract, run npx prisma db update again to apply the change to your database.

The db client exported from src/prisma/db.ts is created once and reused across your project. Models are grouped by their PostgreSQL schema, so the Post model is available as db.orm.public.Post.

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

src/pages/posts.astro
---
import { db } from '../prisma/db';
const posts = await db.orm.public.Post
.where({ published: true })
.orderBy((post) => post.id.desc())
.all();
---
<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 and Prisma’s guide to reading data.

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.

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:

Terminal window
npm install pg

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:

src/pages/index.astro
---
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>

Weitere Backend-Anleitungen

Wirke mit Community Sponsor