Unique constraints and indexes (SQLite)

Prerequisites

In order to follow this guide, you need:

• the sqlite3 command line client for SQLite
• 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

Afterwards, execute the following command in your terminal to create a new database called UniqueDemo:

sqlite3 UniqueDemo.db

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

sqlite3 UniqueDemo.db '.tables'

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 SQLite 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 "User" (
  "id" INTEGER PRIMARY KEY,
  "email" TEXT UNIQUE
);

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

sqlite3 UniqueDemo.db < single-column-unique.sql

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

CREATE UNIQUE INDEX "sqlite_autoindex_User_1" ON "User"("email");

CREATE TABLE "User" (
  "id" INTEGER PRIMARY KEY,
  "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 "AnotherUser" (
  "id" INTEGER PRIMARY KEY,
  "firstName" TEXT,
  "lastName" TEXT,
  UNIQUE ("firstName", "lastName")
);

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

sqlite3 UniqueDemo.db < multi-column-unique.sql

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

CREATE UNIQUE INDEX "sqlite_autoindex_AnotherUser_1" ON "AnotherUser"("firstName", "lastName");

4. Introspect your database with Prisma

In the previous sections, you created two 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

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

In order to introspect your database, you need to tell Prisma how to connect to it. You do so by configuring a datasource in your Prisma schema.

Create a new file named schema.prisma and add the following code to it:

schema.prisma
1datasource db {
2  provider = "sqlite"
3  url      = env("DATABASE_URL")
4}

The database connection URL is set via an environment variable. The Prisma CLI automatically supports the dotenv format which automatically picks up environment variables defined in a file named .env.

Create a new file named .env and set your database connection URL as the DATABASE_URL environment variable:

DATABASE_URL=file:UniqueDemo.db

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:

schema.prisma
1datasource db {
2  provider = "sqlite"
3  url      = env("DATABASE_URL")
4}
5
6model User {
7  email String? @unique
8  id    Int     @default(autoincrement()) @id
9}
10
11model AnotherUser {
12  firstName String?
13  id        Int     @default(autoincrement()) @id
14  lastName  String?
15
16  @@unique([firstName, lastName], name: "sqlite_autoindex_AnotherUser_1")
17}

5. 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):

schema.prisma
1generator client {
2  provider = "prisma-client-js"
3}

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.

6. 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:

{ email: 'alice@prisma.io', id: 1 }
(node:14072) UnhandledPromiseRejectionWarning: Error:
Invalid `newUser2 = await prisma.user.create()` invocation in
/Users/janedoe/unique-demo/index.js:12:38

   8     email: "alice@prisma.io"
   9   }
  10 })
  11 console.log(newUser1)
→ 12 const newUser2 = 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:

{ firstName: 'Alice', id: 1, lastName: 'Smith' }
(node:14273) UnhandledPromiseRejectionWarning: Error:
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.