W3cubDocs

/Astro

Prisma Postgres & Astro

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

Section titled “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

Section titled “Prerequisites”

Install dependencies and initialize Prisma

Section titled “Install dependencies and initialize Prisma”

Run the following commands to install the necessary Prisma dependencies:

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

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

Terminal window 
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

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.

prisma/schema.prisma
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

Section titled “Generate client”

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

Terminal window 
npx prisma generate

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.

Terminal window 
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.

src/lib/prisma.ts
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

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:

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

Section titled “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

Section titled “Prerequisites”

Install dependencies

Section titled “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:

Terminal window 
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:

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>

Official Resources

Section titled “Official Resources”

More backend service guides

© 2021 Fred K. Schott
Licensed under the MIT License.
https://docs.astro.build/en/guides/backend/prisma-postgres/