prisma-postgres-setup

intent

Set up a new Prisma Postgres database via the Management API and connect it to a local project. use this skill when provisioning a fresh database, creating a new Prisma Postgres project, obtaining connection strings, or integrating database setup into a workflow. do not use this for existing connected databases (schema and migration tasks are standard Prisma CLI), CI/CD preview databases (use prisma-postgres-cicd), or multi-tenant provisioning at app scale (use prisma-postgres-integrator).

inputs

• PRISMA_SERVICE_TOKEN: workspace service token. obtain from Prisma Console → Workspace Settings → Service Tokens. can be passed inline in user message, loaded from environment variable, or read from .env file. required for all Management API calls.
• Prisma workspace: existing workspace at https://console.prisma.io. create one if needed.
• Node.js 18+: required for running npm scripts and Prisma CLI.
• Local project directory: the directory where you'll initialize Prisma schema and config files.
• External connection: https://api.prisma.io/v1 (Management API base URL).

edge cases to handle: token expiry (401 errors), rate limits (429), database creation limits (existing databases must be deleted first), network timeouts, and provisioning delays (polling required for status checks).

procedure

step 1: authenticate with service token

• input: service token from user message, environment (PRISMA_SERVICE_TOKEN), or .env file.
• check for token in this order: (1a) inline in user prompt, (1b) environment variable PRISMA_SERVICE_TOKEN, (1c) .env file.
• if no token found, instruct user to navigate to Prisma Console → Workspace Settings → Service Tokens, create one, and paste it.
• store token in shell variable: export PRISMA_SERVICE_TOKEN="<token>".
• output: shell variable set and ready for API calls.

step 2: list available regions

• input: PRISMA_SERVICE_TOKEN, Management API endpoint.
• call: curl -s -H "Authorization: Bearer $PRISMA_SERVICE_TOKEN" https://api.prisma.io/v1/regions/postgres
• parse response and filter for regions where status is available.
• output: list of region objects with id, name, status.
• present regions to user as interactive menu (selectable options, not a static table). let user pick one region.

step 3: create project with database

• input: project name (default to current directory name if not provided), region ID from step 2, service token.
• call: curl -s -X POST https://api.prisma.io/v1/projects -H "Authorization: Bearer $PRISMA_SERVICE_TOKEN" -H "Content-Type: application/json" -d '{"name":"<project-name>","region":"<region-id>","createDatabase":true}'
• parse response: extract data.id (project ID, prefixed proj_), data.database.id (database ID, prefixed db_), and data.database.connections[0].endpoints.direct.connectionString (direct PostgreSQL connection string).
• if response status is provisioning, poll GET /v1/databases/<database-id> every 2-3 seconds until status is ready.
• output: project ID, database ID, direct connection string (store in variable for step 5).

step 4: create named connection (optional)

• input: database ID from step 3, connection name (e.g., "dev" or "prod"), service token.
• call: curl -s -X POST https://api.prisma.io/v1/databases/<database-id>/connections -H "Authorization: Bearer $PRISMA_SERVICE_TOKEN" -H "Content-Type: application/json" -d '{"name":"<connection-name>"}'
• parse response and extract data.endpoints.direct.connectionString.
• output: named connection string (alternative to step 3 direct string).
• note: only create this if user needs multiple named connections per developer or environment.

step 5: configure local project

• input: direct connection string from step 3 or 4, local project directory.
• install dependencies: npm install prisma @prisma/client @prisma/adapter-pg pg dotenv. all five packages are required.
• append connection string to .env file (do not overwrite existing entries): DATABASE_URL="<direct-connection-string>".
• verify .env is in .gitignore. create .gitignore if missing. warn user if .env is not gitignored.
• set "type": "module" in package.json (Prisma 7 generates ESM output).
• if prisma/schema.prisma does not exist, run npx prisma init. this scaffolds prisma/schema.prisma and prisma.config.ts.
• ensure schema.prisma has postgresql provider and no url or directUrl in datasource block:

  datasource db {
    provider = "postgresql"
  }
  

• ensure prisma.config.ts exists and loads DATABASE_URL from environment:

  import path from 'node:path'
  import { defineConfig } from 'prisma/config'
  import 'dotenv/config'
  
  export default defineConfig({
    earlyAccess: true,
    schema: path.join(import.meta.dirname, 'prisma', 'schema.prisma'),
    datasource: {
      url: process.env.DATABASE_URL!,
    },
  })
  

• output: .env configured, schema.prisma and prisma.config.ts ready for next step.

step 6: define schema and push

• input: existing prisma/schema.prisma or empty schema from step 5.
• if schema already contains models, skip to pushing.
• if schema is empty, present three interactive options to user:
  • "i'll define my schema manually": tell user to edit prisma/schema.prisma and confirm when ready. wait for user confirmation before proceeding.
  • "give me a starter schema": add a blog starter schema (User, Post, Comment with relations) to prisma/schema.prisma. show user what was added and ask for approval before pushing.
  • "i'll describe what i need": ask user to describe data model in natural language (e.g., "task manager with projects, tasks, team members"). generate schema from description, display it, ask for confirmation.
• once schema has models and user approves, create migration and generate client: npx prisma migrate dev --name init.
• output: migration files in prisma/migrations/, generated Prisma client.

step 7: verify connection

• input: generated Prisma client, DATABASE_URL in .env, pg and @prisma/adapter-pg modules installed.
• create test file test-connection.ts:

  import 'dotenv/config'
  import pg from 'pg'
  import { PrismaPg } from '@prisma/adapter-pg'
  import { PrismaClient } from './generated/prisma/client.js'
  
  const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL })
  const adapter = new PrismaPg(pool)
  const prisma = new PrismaClient({ adapter })
  
  const result = await prisma.$queryRawUnsafe('SELECT 1 as connected')
  console.log('Connected to Prisma Postgres:', result)
  
  await prisma.$disconnect()
  await pool.end()
  

• run: npx tsx test-connection.ts.
• output: "Connected to Prisma Postgres: [{ connected: 1 }]" (or equivalent confirmation).
• delete test-connection.ts after success.
• share links: Prisma Studio local (npx prisma studio) and Console dashboard (https://console.prisma.io/<workspaceId>/<projectId>/<databaseId>/dashboard, strip prefixes wksp_, proj_, db_ from IDs).

decision points

if service token not found: ask user to create one in Console → Workspace Settings → Service Tokens and paste it. do not proceed without token.

if region list is empty or no available regions: inform user that the workspace has no available Postgres regions. suggest creating a new workspace or contacting support.

if project creation returns HTTP 422 validation-error: check request body matches endpoint schema. common issues: missing name, invalid region ID. re-prompt user.

if project creation returns HTTP 429 rate-limit-exceeded: back off and retry after 3-5 seconds. inform user of retry attempt.

if database provisioning status is not ready immediately: poll GET /v1/databases/ every 2-3 seconds for up to 2 minutes. if still provisioning after 2 minutes, inform user provisioning is in progress and they can check Console for status.

if HTTP 401 authentication-failed: service token is invalid or expired. ask user to create a new one in Console → Workspace Settings → Service Tokens.

if project creation fails with database limit error: list user's existing projects via GET /v1/projects as interactive menu. let user pick one to delete. call DELETE /v1/projects/. retry project creation.

if schema already has models in step 6: skip to running npx prisma migrate dev --name init directly.

if user explicitly requests prototyping-only mode (no migration history): use npx prisma db push instead of migrate dev, then run npx prisma generate separately. warn user that migration history will not be tracked.

if test-connection.ts fails with connection error: check DATABASE_URL is correct and database is ready. verify pg.Pool is initialized. ensure PrismaPg adapter is instantiated. do not delete test-connection.ts until connection succeeds.

output contract

• .env file: contains DATABASE_URL="<direct-connection-string>". file exists in project root and is gitignored.
• prisma/schema.prisma: valid Prisma schema with postgresql provider, datasource block (no url or directUrl), and at least one model. location: <project>/prisma/schema.prisma.
• prisma.config.ts: valid Prisma 7 config that loads DATABASE_URL from environment. location: <project>/prisma.config.ts.
• prisma/migrations/: directory containing migration history. first migration named <timestamp>_init. location: <project>/prisma/migrations/<timestamp>_init/migration.sql.
• generated Prisma client: generated from schema.prisma. location: <project>/generated/prisma/client.js (ESM output).
• package.json: contains "type": "module" and dependencies: prisma, @prisma/client, @prisma/adapter-pg, pg, dotenv.
• connection verified: test-connection.ts runs successfully and logs "Connected to Prisma Postgres: [...]" before being deleted.

outcome signal

user can:

• run npx prisma studio and see their database tables and data in the local web UI.
• navigate to https://console.prisma.io/<workspaceId>/<projectId>/<databaseId>/dashboard and see the database online in Prisma Console.
• import and use PrismaClient in their app (e.g., import { PrismaClient } from './generated/prisma/client.js') and execute queries without connection errors.
• run npx prisma migrate dev in the future and see migration history tracked in prisma/migrations/.
• confirm DATABASE_URL is set and .env is gitignored, so the connection string is not committed to version control.