Skip to content

Latest commit

 

History

History
409 lines (285 loc) · 14.3 KB

01-postgresql.mdx

File metadata and controls

409 lines (285 loc) · 14.3 KB
Raw
title metaTitle metaDescription
Unique constraints and indexes (PostgreSQL)
Unique constraints & indexes with Prisma and PostgreSQL
Learn how to configure unique constraints and indexes with Prisma and PostgreSQL by following the step-by-step instructions in this practical guide.

Overview

This page explains how to configure a unique constraint / index in your PostgreSQL database. Constraints and indexes are very similar in PostgreSQL (learn more here): When adding a unique constraint to one or more columns, PostgreSQL will always create a corresponding unique index.

In this guide, you'll always configure unique constraints (which will automatically configure unique indexes as well).

You can configure unique constraints either on a single column or on multiple columns. These can be added when you create the table initially (using CREATE TABLE) or to an already existing table (using ALTER TABLE). This guide covers all four combinations.

At the end of the guide, you'll introspect your database to reflect the unique constraint in the Prisma schema, then you'll generate Prisma Client and write a simple Node.js script to validate the constraints.

Prerequisites

In order to follow this guide, you need:

  • a PostgreSQL database server running
  • the createdb command line utility
  • the psql command line client for PostgreSQL
  • Node.js installed on your machine

1. Create a new database and project directory

Start by creating a project directory where you can put the files you'll create throughout this guide:

mkdir unique-demo
cd unique-demo

Next, make sure that your PostgreSQL database server is running. Then execute the following command in your terminal to create a new database called UniqueDemo:

createdb UniqueDemo

You can validate that the database was created by running the \dt command which lists all tables (relations) in your database (right now there are none):

psql -d UniqueDemo -c "\dt"

2. Create a table with a single-column unique constraint and index

In this section, you'll create a new table with a single-column unique constraint in the UniqueDemo database. As mentioned above, this means that PostgreSQL automatically adds a unique index to the same column.

Create a new file named single-column-unique.sql and add the following code to it:

CREATE TABLE "public"."User" (
  email TEXT UNIQUE
);

Now run the SQL statement against your database to create a new table called User:

psql UniqueDemo < single-column-unique.sql

Congratulations, you just created a table called User in the database. The table has one column called email on which you defined a unique index. PostgreSQL also automatically added a corresponding unique index (do not run this code):

CREATE UNIQUE INDEX "User_email_key" ON "User"(email text_ops);
Alternative: Define the constraint as a table constraint

In the code above, you created the unique constraint as a column constraint. Alternatively, you can define it as a table constraint. There's no practical difference between the two, the alternative is just added for completeness.

To add the unique constraint as a table constraint, you need to adjust your SQL statement to look as follows:

CREATE TABLE "public"."User" (
  email TEXT,
  UNIQUE ("email")
);

3. Create a table with a multi-column unique constraint and index

Next, you'll create a table with a multi-column unique constraint. This also adds a unique index to the columns with the constraint.

Create a new file named multi-column-unique.sql and add the following code to it:

CREATE TABLE "public"."AnotherUser" (
  "firstName" TEXT,
  "lastName" TEXT,
  UNIQUE (firstName, lastName)
)

Now run the SQL statement against your database to create a new table called AnotherUser:

psql UniqueDemo < multi-column-unique.sql

Congratulations, you just created a table called AnotherUser in the database. The table has two column called firstName and lastName on which you defined a unique index. PostgreSQL also automatically added a corresponding unique index (do not run this code):

CREATE UNIQUE INDEX "AnotherUser_firstname_lastname_key" ON "AnotherUser"(firstname text_ops,lastname text_ops);

4. Adding a single-column unique constraint to an existing table

In this section, you'll add a single-column unique constraint to a table that already exists in your database. To do so, you first need to create a new table and then alter the table to add the constraint.

Create a new file named add-single-unique-constraint-later.sql and add the following code:

CREATE TABLE "public"."OneMoreUser" (
  email TEXT
);

ALTER TABLE "public"."OneMoreUser" ADD CONSTRAINT "OneMoreUser_email_unique_constraint" UNIQUE (email);

This code contains two SQL statements:

  1. Create a new table called OneMoreUser
  2. Alter the table to add an unique constraint

Now run the SQL statements against your database to create a new table called OneMoreUser:

psql UniqueDemo < add-single-unique-constraint-later.sql

Congratulations, you just created a table called OneMoreUser in the database. The table has one column called email on which you later added a unique constraint in the second SQL statement. PostgreSQL also automatically added a corresponding unique index (do not run this code):

CREATE UNIQUE INDEX "OneMoreUser_email_unique_constraint" ON "OneMoreUser"(email text_ops);

5. Adding a multi-column unique constraint to an existing table

In this section, you'll add a multi-column unique constraint to a table that already exists in your database. To do so, you first need to create a new table and then alter the table to add the constraint.

Create a new file named add-multi-unique-constraint-later.sql and add the following code:

CREATE TABLE "public"."TheLastUser" (
  "firstName" TEXT,
  "lastName" TEXT
);

ALTER TABLE "public"."TheLastUser" ADD CONSTRAINT "TheLastUser_firstName_lastName_unique_constraint" UNIQUE (firstName, lastName);

This code contains two SQL statements:

  1. Create a new table called TheLastUser
  2. Alter the table to add an unique constraint

Now run the SQL statements against your database to create a new table called OneMoreUser:

psql UniqueDemo < add-multi-unique-constraint-later.sql

Congratulations, you just created a table called OneMoreUser in the database. The table has two columns called firstName and lastName on which you later added a unique constraint in the second SQL statement. PostgreSQL also automatically added a corresponding unique index (do not run this code):

CREATE UNIQUE INDEX "TheLastUser_firstName_lastName_unique_constraint" ON "TheLastUser"(firstname text_ops,lastname text_ops);

6. Introspect your database with Prisma

Deprecation warning
From Prisma 3.0.0 onwards, the prisma introspect command will be deprecated and replaced with the prisma db pull command.

In the previous sections, you created four tables with unique constraints:

  • The table User has a singe-column unique constraint and index on the email column
  • The table AnotherUser has a multi-column unique constraint and index on the firstName and lastName columns
  • The table OneMoreUser has a singe-column unique constraint and index on the email column
  • The table TheLastUser has a multi-column unique constraint and index on the firstName and lastName columns

In this section you'll introspect your database to generate the Prisma models for these tables.

To start, set up a new Node.js project and add the Prisma CLI as a development dependency:

npm init -y
npm install prisma --save-dev

Next, set up your Prisma project by creating your Prisma schema file with the following command:

npx prisma init

This command created a new directory called prisma with the following contents:

  • schema.prisma: The Prisma schema with your database connection and the Prisma Client generator
  • .env: A dotenv file for defining environment variables (used for your database connection)

To connect your database, you need to set the url field of the datasource block in your Prisma schema to your database connection URL:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

In this case, the url is set via an environment variable which is defined in prisma/.env:

DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"

The format of the connection URL for your database depends on the database you use. For PostgreSQL, it looks as follows (the parts spelled all-uppercased are placeholders for your specific connection details):

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA

Here's a short explanation of each component:

  • USER: The name of your database user
  • PASSWORD: The password for your database user
  • PORT: The port where your database server is running (typically 5432 for PostgreSQL)
  • DATABASE: The name of the database
  • SCHEMA: The name of the schema inside the database

With both the schema.prisma and .env files in place, you can run Prisma's introspection with the following command:

npx prisma introspect

This command introspects your database and for each table adds a Prisma model to the Prisma schema:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model AnotherUser {
  firstName String?
  lastName  String?

  @@unique([firstName, lastName], name: "AnotherUser_firstname_lastname_key")
}

model OneMoreUser {
  email String? @unique
}

model TheLastUser {
  firstName String?
  lastName  String?

  @@unique([firstName, lastName], name: "TheLastUser_firstName_lastName_unique_constraint")
}

model User {
  email String? @unique
}

7. Generate Prisma Client

To validate whether the unique constraints work, you'll now generate Prisma Client and send a few sample queries to the database.

First, add a generator block to your Prisma schema (typically added right below the datasource block):

generator client {
  provider = "prisma-client-js"
}

Run the following command to install and generate Prisma Client in your project:

npx prisma generate

Now you can use Prisma Client to send database queries in Node.js.

8. Validate the unique constraints in a Node.js script

Create a new file named index.js and add the following code to it:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const newUser1 = await prisma.user.create({
    data: {
      email: 'alice@prisma.io',
    },
  })
  console.log(newUser1)
  const newUser2 = await prisma.user.create({
    data: {
      email: 'alice@prisma.io',
    },
  })
  console.log(newUser2)
}

main()

In this code, you're creating two users with the same email, so you're violating the unique constraint that's configured on the User table.

Run the code with this command:

node index.js

After newUser1 gets printed to the console successfully, the script throws an error indicating that the unique constraint on email is violated:

Invalid `const newUser1 = await prisma.user.create()` invocation in
/Users/janedoe/unique-demo/index.js:6:38

  2
  3 const prisma = new PrismaClient()
  4
  5 async function main() {
→ 6   const newUser1 = await prisma.user.create(Unique constraint failed on the fields: (`email`)

To validate the multi-column unique constraint, replace the code in index.js with the following:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const newUser1 = await prisma.anotherUser.create({
    data: {
      firstName: 'Alice',
      lastName: 'Smith',
    },
  })
  console.log(newUser1)
  const newUser2 = await prisma.anotherUser.create({
    data: {
      firstName: 'Alice',
      lastName: 'Smith',
    },
  })
  console.log(newUser2)
}

main()

Run the script again with this command:

node index.js

This time, you'll see a similar error message indicating the unique constraint on firstName and lastName was violated:

Invalid `newUser2 = await prisma.anotherUser.create()` invocation in
/Users/janedoe/unique-demo/index.js:13:45

   9     lastname: "Smith"
  10   }
  11 })
  12 console.log(newUser1)
→ 13 const newUser2 = await prisma.anotherUser.create(Unique constraint failed on the fields: (`firstname`,`lastname`)

Note that you can add NULL values for these columns without violating the constraints. For example, the following code snippet will not fail:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const newUser1 = await prisma.user.create({ data: {} })
  console.log(newUser1)
  const newUser2 = await prisma.user.create({ data: {} })
  console.log(newUser2)
}

main()

It will create two new records where the email is set to NULL in the database.