Prisma Postgres & Astro
이 콘텐츠는 아직 번역되지 않았습니다.
Prisma Postgres is a fully managed, serverless Postgres database built for modern web apps.
Connect via Prisma ORM (Recommended)
Section titled “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
Section titled “Prerequisites”- An Astro project with an adapter installed to enable on-demand rendering (SSR).
Install dependencies and initialize Prisma
Section titled “Install dependencies and initialize Prisma”Run the following commands to install the necessary Prisma dependencies:
npm install prisma tsx --save-devnpm 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 aschema.prisma
file - A
.env
file with aDATABASE_URL
already set
Define a Model
Section titled “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
Section titled “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
Section titled “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
Section titled “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
Section titled “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
Section titled “Prerequisites”- A Prisma Postgres database with a TCP enabled connection string
Install dependencies
Section titled “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
Section titled “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>