Generated columns with PostgreSQL and Prisma

This example shows how to use generated columns (aka computed fields) with PostgreSQL and Prisma.

While the GENERATED keyword that's used in PostgreSQL to define generated columns is not supported in the Prisma schema, you can still customize the SQL that's generated by Prisma Migrate to make sure certain fields of your Prisma models will be using GENERATED under the hood.

This project shows an example of such a migration.

Workflow

1. Define your Prisma model

In this step, you define the Prisma model inclduding the field you want to be generated. For this simple demo, you can use the following:

model User {
  id        Int     @id @default(autoincrement())
  firstName String?
  lastName  String?
  fullName  String? // GENERATED
}

2. Create the SQL migration using the --create-only option

Next, you'll generate the SQL migration file without yet applying it to the database using the following commnad:

npx prisma migrate dev --name init --create-only

3. Customize the generated SQL to include the GENERATED keyword

After you ran the command above, you can open the migration.sql-file inside of the migrations/TIMESTAMP-init-file. It should look as follows:

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "firstName" TEXT,
    "lastName" TEXT,
    "fullName" TEXT,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

You can now manually edit this file to determine which of the columns should be generated, and by which rules. In this case, we want to concatenate the firstName and the lastName with a space in between:

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "firstName" TEXT,
    "lastName" TEXT,
+   "fullName" TEXT GENERATED ALWAYS AS (
+       CASE
+           WHEN "firstName" IS NULL THEN "lastName"
+           WHEN "lastName" IS NULL THEN "firstName"
+           ELSE "firstName" || ' ' || "lastName"
+       END
+   ) STORED,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

4. Apply the migration against the database

Finally, you can use Prisma Migrate to actually make changes against the database schema:

npx prisma migrate dev

5. Query with Prisma Client

You can now send queries against the User table using the generated PrismaClient instance.

Note that **it is not possible to set a value for the fullName field when invoking prisma.user.create(...), e.g.:

const newUser = await prisma.user.create({
  data: {
    firstName: "Jane",
    lastName: "Doe",
    fullName: "asd" // not possible to set a value for a `GENERATED` column
  }
})

You will see this error:

PrismaClientUnknownRequestError: 
Invalid `prisma.user.create()` invocation in
/Users/nikolasburk/Desktop/script/script.ts:8:37

   5 // A `main` function so that we can use async/await
   6 async function main() {
   7 
  8   const newUser = await prisma.user.create(
  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42601"), message: "cannot insert into column \"fullName\"", detail: Some("Column \"fullName\" is a generated column."), hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("rewriteHandler.c"), line: Some(830), routine: Some("rewriteTargetListIU") }) }) })
    at cb (/Users/nikolasburk/Desktop/script/node_modules/@prisma/client/runtime/index.js:38692:17) {
  clientVersion: '3.7.0'
}

This makes sense because the value of this column will always be computed based on the values of the firstName and lastName columns in a specific row.

The following code is going to work though:

const newUser = await prisma.user.create({
  data: {
    firstName: "Jane",
    lastName: "Doe",
  }
})
console.log(newUser)

This prints the following to the terminal:

{ id: 1, firstName: 'Jane', lastName: 'Doe', fullName: 'Jane Doe' }