db0

PostgreSQL

Connect DB0 to PostgreSQL

Read more in www.postgresql.org.

#Usage

For this connector, you need to install pg dependency:

npmĀ i pg @types/pg

Use postgresql connector:

import { createDatabase } from "db0";
import postgresql from "db0/connectors/postgresql";

const db = createDatabase(
  postgresql({
    bindingName: "DB",
  }),
);

#Options

#url

Connection URL string.

Alternatively, you can add connection configuration.

Read more in node-postgres client options.

#Query parameters

PostgreSQL uses numbered placeholders ($1, $2, ...), while DB0 uses ?. The connector rewrites ? to $n before sending the query:

await db.sql`SELECT * FROM users WHERE id = ${userId}`;
// sent as: SELECT * FROM users WHERE id = $1

Only a ? in SQL code is a placeholder. A ? inside a string literal, a quoted identifier, a comment or a dollar-quoted body is data and is left untouched:

await db.sql`INSERT INTO posts (body) VALUES ('what? really?')`;
// sent unchanged: the question marks are stored as-is

#Operators containing ?

Because ? is the placeholder token, operators that contain one cannot be told apart from a placeholder and are not supported. Use the equivalent function instead:

OperatorUse instead
jsonb ? textjsonb_exists(jsonb, text)
jsonb ?| text[]jsonb_exists_any(jsonb, text[])
jsonb ?& text[]jsonb_exists_all(jsonb, text[])
jsonb @? jsonpathjsonb_path_exists(jsonb, jsonpath)

#Mixing placeholder styles

Both styles number from $1, so hand-written $n parameters cannot be combined with ? in the same query: the generated placeholders would collide with the hand-written ones. Running such a query throws:

await db.prepare("SELECT * FROM users WHERE id = $1 AND name = ?").all(id, name);
// [db0] cannot mix `?` placeholders with numbered `$n` parameters in the same query

Use one style consistently. A query that only uses $n is passed through untouched.

#Prisma Postgres

Prisma Postgres provides a fast, managed PostgreSQL database service. While there's no specific DB0 connector for Prisma Postgres, you can use the existing PostgreSQL connector to connect smoothly.

#Create a database

You can either create an account in Prisma Data Platform and create a database manually or use create-db cli tool to create a Prisma Postgres database.

Let's create a new Prisma Postgres database using the create-db cli tool:

npx create-db

This will output something like:

ā”Œ  šŸš€ Creating a Prisma Postgres database
ā”‚
ā”‚  Provisioning a temporary database in us-east-1...
ā”‚
ā”‚  It will be automatically deleted in 24 hours, but you can claim it.
ā”‚
ā—‡  Database created successfully!
ā”‚
ā”‚
ā—  Database Connection
ā”‚
ā”‚
ā”‚    Connection String:
ā”‚
ā”‚    postgresql://username:password@db.prisma.io:5432/postgres?sslmode=require
ā”‚
ā”‚
ā—†  Claim Your Database
ā”‚
ā”‚    Keep your database for free:
ā”‚
ā”‚    https://create-db.prisma.io/claim?projectID=your_project_id&utm_source=db0&utm_campaign=ppg-awareness
ā”‚
ā”‚    Database will be deleted on [DATE] if not claimed.
ā”‚
ā””

Important: Save your actual connection string to an .env file:

DATABASE_URL="postgresql://your_username:your_password@db.prisma.io:5432/postgres?sslmode=require"

#Usage

Use the postgresql connector with your Prisma Postgres database:

import "dotenv/config";
import { createDatabase } from "db0";
import postgresql from "db0/connectors/postgresql";

const db = createDatabase(
  postgresql({
    url: process.env.DATABASE_URL,
  }),
);

Then you should be able to query your Prisma Postgres database:

import "dotenv/config";
import { createDatabase } from "db0";
import postgresql from "db0/connectors/postgresql";

const db = createDatabase(
  postgresql({
    url: process.env.DATABASE_URL!,
  }),
);

async function main() {
  console.log(await db.sql`SELECT 1`);
}

main();
Edit this page
Previous PlanetScale Next SQLite

db0 Ā tiny sql connector.