prisma: Triggers not working properly with Prisma: [ERROR] `Query '' is required to return data, but found no record(s).`

Bug description

Hello

I’m having trouble when trying to insert a new record to my shipment table, I have a trigger that is in charge of creating a custom unique ID. (you can see the sql script in the How to reproduce section.

NOTE: Whenever I drop the trigger from the table, I can create a record without an error.

Couple of notes

The trigger works when I use pgAdmin to execute raw sql scripts.
Whenever I drop the trigger from the table, I can create a record without an error.

This is the error that I get

/Users/gmwill934/Documents/gmwill934/prisma-trigger-error/node_modules/@prisma/client/runtime/index.js:28842
      throw new PrismaClientUnknownRequestError(message, this.client._clientVersion);
            ^
PrismaClientUnknownRequestError: 
Invalid `prisma.shipment.create()` invocation in
/Users/gmwill934/Documents/gmwill934/prisma-trigger-error/prisma.ts:15:34

  12 await prisma.$executeRawUnsafe(setClient("NPS"));
  13 await prisma.$executeRawUnsafe(setWarehouse("01"));
  14 
→ 15 return await prisma.shipment.create(
  Query createOneshipment is required to return data, but found no record(s).
    at RequestHandler.handleRequestError (/Users/gmwill934/Documents/gmwill934/prisma-trigger-error/node_modules/@prisma/client/runtime/index.js:28842:13)
    at RequestHandler.request (/Users/gmwill934/Documents/gmwill934/prisma-trigger-error/node_modules/@prisma/client/runtime/index.js:28820:12)
    at async PrismaClient._request (/Users/gmwill934/Documents/gmwill934/prisma-trigger-error/node_modules/@prisma/client/runtime/index.js:29753:16) {
  clientVersion: '4.2.1'
}

How to reproduce

On pgAdmin (postgres), run the following sql script to create the table.

create table shipment (
	client varchar(16) not null default(current_setting('app.client')::varchar(16)),
	warehouse varchar(16) not null default(current_setting('app.warehouse')::varchar(16)),
	id varchar(64) default(''),
	notes text null,
	is_validated boolean not null default(false),
	constraint "PK_Shipment__id" primary key (id)
);

Run the following sql script to create the trigger function

create or replace function shipment_custom_id_generator() returns trigger as $$
begin
	new.id = (select
		   		current_setting('app.client') || current_setting('app.warehouse') || 'LPN' || LPAD(cast(count(*) + 1 as varchar(64)),8,'0')::varchar(16)
			from 
				shipment
			where
				client = current_setting('app.client')
				and warehouse = current_setting('app.warehouse')
		   );
	return new;
end
$$ language plpgsql

Create the trigger

create trigger shipment_custom_generator_id_trigger
before insert on shipment
for each row
execute procedure shipment_custom_id_generator();

Clone the repo https://github.com/gmwill934/prisma-trigger-error
Install dependencies
Create a .env file with a DATABASE_URL attribute. Make sure it matches your local pg environment. (DATABASE_URL="postgresql://user:password@localhost:port/database-name?schema=schema-name")
Run npm run prisma:pull to introspect the database
Generate prisma client running npm run prisma:generate
Run the app with npm run dev

Expected behavior

Insert a new record on the shipment table without a 500 error code.

Prisma information

This is the my prisma schema

model shipment {
  client       String  @default(dbgenerated("(current_setting('app.client'::text))::character varying(16)")) @db.VarChar(16)
  warehouse    String  @default(dbgenerated("(current_setting('app.warehouse'::text))::character varying(16)")) @db.VarChar(16)
  id           String  @id(map: "PK_Shipment__id") @default("") @db.VarChar(64)
  notes        String?
  is_validated Boolean @default(false)
}

Environment & setup

OS: Mac OS M1
Database: Postgres 14
Node.js version: v16.15.1

Prisma Version

4.2.1

About this issue

Original URL
State: open
Created 2 years ago
Reactions: 2
Comments: 17 (6 by maintainers)

Most upvoted comments

@gmwill934 As createMany doesn’t do any select (it doesn’t return data) it should work well as a workaround. Let us know if this works for you:
 return await prisma.shipment.createMany({
      data: [{ notes: "My super awesome notes!" }],
    });

@Jolg42, this workaround worked for me, thanks.

VladBrok on Jun 27, 2023

Tried workarounds but no luck, data is not unique. Hoping the team could address this at some point.

Thank you very much @janpio

gmwill934 on Oct 19, 2022

For sure @janpio

Clone the repo @ https://github.com/gmwill934/prisma-simple and cd to new created folder
Install dependencies with npm i
Initialize prisma with npx prisma init
Change the DATABASE_URL property on your .env file to your environment
Copy the following schema on your prisma/schema.prisma file (overwrite it)

Prisma Schema

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

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

model shipment {
  id   Int     @id
  data String? @db.VarChar(64)
}

Execute migrations and generate client npx prisma migrate dev and enter a name for the migration
Run SQL script (script.sql) to create simple trigger function and trigger
Run app with npm run dev
See ERROR

A few notes.

Error only shows when NO records exist on the table
Error is shown on the first example that I showed no matter if rows exist (also using interactiveTransactions)

FOR QUICK REFERENCE

Prisma Schema

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

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

model shipment {
  id   Int     @id
  data String? @db.VarChar(64)
}

SQL Script

create or replace function my_trigger() returns trigger as $$
	begin
		new.id = (select count(*) + 1 from shipment);
		return new;
	end
$$ language plpgsql;

create or replace trigger shipment_my_trigger
before insert on shipment
for each row
execute procedure my_trigger();

gmwill934 on Aug 22, 2022

Unfortunately it doesn’t work, I need to be able to select the id for further business logic.

I do understand you! Hopefully this can be fixed at some point.

Thanks @janpio

gmwill934 on Sep 28, 2022