Overview

This page explains how to configure foreign key constraints in your SQLite database. Foreign keys are used to represent relations in your database.

In this guide, you'll create two tables where one references the other via a foreign key. Foreign keys can be defined on a single column or on multiple columns. This guide covers both approaches.

At the end of the guide, you'll introspect your database to reflect the foreign key in the Prisma schema, then you'll generate Prisma Client and write a simple Node.js script to test the foreign key relation.

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 foreign-key-demo
cd foreign-key-demo

Next, execute the following command in your terminal to create a new database called ForeignKeyDemo:

sqlite3 ForeignKeyDemo.db '.databases'

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 ForeignKeyDemo.db '.tables'

2. Create two tables with a single-column foreign key constraint

In this section, you'll create two tables where one references the other via a single-column foreign key in the ForeignKeyDemo database.

Create a new file called single-column-foreign-key.sql and add the following code to it:

CREATE TABLE "User" (
"id" INTEGER PRIMARY KEY,
"name" TEXT
);
CREATE TABLE "Post" (
"id" INTEGER PRIMARY KEY,
"title" TEXT,
"authorId" INTEGER,
FOREIGN KEY ("authorId") REFERENCES "User" ("id")
);

Now run the SQL statement against your database to create the two tables:

sqlite3 ForeignKeyDemo.db < single-column-foreign-key.sql

Congratulations, you just created two tables called User and Post in the database. The Post table references the User table via the foreign key defined on the authorId column.


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

To add the foreign key constraint as a column constraint, you need to adjust your SQL statement for creating the Post table to look as follows:

CREATE TABLE "Post" (
"id" SERIAL,
"title" TEXT,
"authorId" INTEGER REFERENCES "User"("id")
);

3. Create a table with a multi-column foreign key constraint

In this section, you'll create two tables where one references the other via a single-column foreign key in the ForeignKeyDemo database.

Create a new file called multi-column-foreign-key.sql and add the following code to it:

CREATE TABLE "AnotherUser" (
"id" INTEGER PRIMARY KEY,
"firstName" TEXT,
"lastName" TEXT,
UNIQUE ("firstName", "lastName")
);
CREATE TABLE "AnotherPost" (
"id" INTEGER PRIMARY KEY,
"title" TEXT,
"authorFirstName" TEXT,
"authorLastName" TEXT,
FOREIGN KEY ("authorFirstName", "authorLastName") REFERENCES "AnotherUser"("firstName", "lastName")
);

Note: The UNIQUE constraint on AnotherUser is needed to be able to reference the columns as foreign keys.

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

sqlite3 ForeignKeyDemo.db < multi-column-foreign-key.sql

Congratulations, you just created two tables called AnotherUser and AnotherPost in the database. The AnotherPost table references the AnotherUser table via the foreign key defined on the authorFirstName and authorLastName columns.

4. Introspect your database with Prisma

In the previous sections, you created two times two tables with foreign key constraints:

  • The table Post has a singe-column foreign key on it's authorId column which points to the id field of the User table.
  • The table AnotherPost has a multi-column foreign key on it's authorFirstName and authorLastName columns which point to the firstName and lastField columns of the AnotherUser table.

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 called schema.prisma and add the following code to it:

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

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 called .env.

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

DATABASE_URL=file:../ForeignKeyDemo.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:

datasource db {
provider = "sqlite"
url = env("DATABASE_URL")
}
model User {
id Int @default(autoincrement()) @id
name String?
Post Post[]
}
model Post {
authorId Int?
id Int @default(autoincrement()) @id
title String?
User User? @relation(fields: [authorId], references: [id])
}
model AnotherUser {
firstName String?
id Int @default(autoincrement()) @id
lastName String?
AnotherPost AnotherPost[]
@@unique([firstName, lastName], name: "sqlite_autoindex_AnotherUser_1")
}
model AnotherPost {
authorFirstName String?
authorLastName String?
id Int @default(autoincrement()) @id
title String?
AnotherUser AnotherUser? @relation(fields: [authorFirstName, authorLastName], references: [firstName, lastName])
}

5. Generate Prisma Client

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

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.

6. Validate the foreign key constraints in a Node.js script

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

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
async function main() {
const userWithPost = await prisma.user.create({
data: {
name: 'Alice',
Post: {
create: {
title: 'Hello World from Alice',
},
},
},
include: {
Post: true,
},
});
console.log(userWithPost);
const anotherUserWithPost = await prisma.anotherUser.create({
data: {
firstName: 'Bob',
lastName: 'Smith',
AnotherPost: {
create: {
title: 'Hello World from Bob',
},
},
},
include: {
AnotherPost: true,
},
});
console.log(anotherUserWithPost);
}
main();

In this code, you're creating two User records, each with a related Post record.

Run the code with this command:

node index.js

The following output indicates that the foreign key constraint is working as intended:

{
id: 1,
name: 'Alice',
Post: [ { authorId: 1, id: 1, title: 'Hello World from Alice' } ]
}
{
firstName: 'Bob',
id: 1,
lastName: 'Smith',
AnotherPost: [
{
authorFirstName: 'Bob',
authorLastName: 'Smith',
id: 1,
title: 'Hello World from Bob'
}
]
}
Edit this page on Github