Cross schema reference with foreign key not working in 4.7.0+ #16585
Assignees
Labels
bug/2-confirmed
Bug has been reproduced and confirmed.
kind/bug
A reported bug.
team/schema
Issue for team Schema.
tech/engines/migration engine
Issue in the Migration Engine
topic: multiSchema
multiple schemas
topic: prisma migrate dev
CLI: prisma migrate dev
Milestone
Comments
|
How did you create your schema - hand written or using Introspection? |
eviefp
added
team/schema
|
I'm having this same problem after doing a db pull from the default database for this Next.JS + Supabase starter project. After doing some name mapping to get rid of the It seems clear to me that my I'm very new to Prisma so definitely possible I'm just screwing something up, but I figured this might be helpful for you to know since it should be easily reproducible using that starter project. Here is my |
|
I'd like to offer another reproduction of this bug on Supabase. (Hope it somehow helps) How to reproduce
SQL snippet here for reference
New schema.prisma looks like this
Prisma Version |
Hi! Sorry for late reply but in my case I used both approaches. I first wrote the "User" - model into the schema and ran I can also verify both @firzanarmani and @mjlatty cases, as they are basically same setups as I have. |
janpio
added
the
bug/1-unconfirmed
|
Thanks for providing the information here, I took a look to reproduce:
Full output with debugging enabled: For easier reproduction (without having to create a Supabase database) here is a full SQL dump for the -- DROP SCHEMA auth;
CREATE SCHEMA auth AUTHORIZATION supabase_admin;
-- DROP TYPE auth."aal_level";
CREATE TYPE auth."aal_level" AS ENUM (
'aal1',
'aal2',
'aal3');
-- DROP TYPE auth."factor_status";
CREATE TYPE auth."factor_status" AS ENUM (
'unverified',
'verified');
-- DROP TYPE auth."factor_type";
CREATE TYPE auth."factor_type" AS ENUM (
'totp',
'webauthn');
-- DROP SEQUENCE auth.refresh_tokens_id_seq;
CREATE SEQUENCE auth.refresh_tokens_id_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1
NO CYCLE;-- auth.audit_log_entries definition
-- Drop table
-- DROP TABLE auth.audit_log_entries;
CREATE TABLE auth.audit_log_entries (
instance_id uuid NULL,
id uuid NOT NULL,
payload json NULL,
created_at timestamptz NULL,
ip_address varchar(64) NOT NULL DEFAULT ''::character varying,
CONSTRAINT audit_log_entries_pkey PRIMARY KEY (id)
);
CREATE INDEX audit_logs_instance_id_idx ON auth.audit_log_entries USING btree (instance_id);
-- auth.instances definition
-- Drop table
-- DROP TABLE auth.instances;
CREATE TABLE auth.instances (
id uuid NOT NULL,
uuid uuid NULL,
raw_base_config text NULL,
created_at timestamptz NULL,
updated_at timestamptz NULL,
CONSTRAINT instances_pkey PRIMARY KEY (id)
);
-- auth.schema_migrations definition
-- Drop table
-- DROP TABLE auth.schema_migrations;
CREATE TABLE auth.schema_migrations (
"version" varchar(255) NOT NULL,
CONSTRAINT schema_migrations_pkey PRIMARY KEY (version)
);
-- auth.sso_providers definition
-- Drop table
-- DROP TABLE auth.sso_providers;
CREATE TABLE auth.sso_providers (
id uuid NOT NULL,
resource_id text NULL,
created_at timestamptz NULL,
updated_at timestamptz NULL,
CONSTRAINT "resource_id not empty" CHECK (((resource_id = NULL::text) OR (char_length(resource_id) > 0))),
CONSTRAINT sso_providers_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX sso_providers_resource_id_idx ON auth.sso_providers USING btree (lower(resource_id));
-- auth.users definition
-- Drop table
-- DROP TABLE auth.users;
CREATE TABLE auth.users (
instance_id uuid NULL,
id uuid NOT NULL,
aud varchar(255) NULL,
"role" varchar(255) NULL,
email varchar(255) NULL,
encrypted_password varchar(255) NULL,
email_confirmed_at timestamptz NULL,
invited_at timestamptz NULL,
confirmation_token varchar(255) NULL,
confirmation_sent_at timestamptz NULL,
recovery_token varchar(255) NULL,
recovery_sent_at timestamptz NULL,
email_change_token_new varchar(255) NULL,
email_change varchar(255) NULL,
email_change_sent_at timestamptz NULL,
last_sign_in_at timestamptz NULL,
raw_app_meta_data jsonb NULL,
raw_user_meta_data jsonb NULL,
is_super_admin bool NULL,
created_at timestamptz NULL,
updated_at timestamptz NULL,
phone varchar(15) NULL DEFAULT NULL::character varying,
phone_confirmed_at timestamptz NULL,
phone_change varchar(15) NULL DEFAULT ''::character varying,
phone_change_token varchar(255) NULL DEFAULT ''::character varying,
phone_change_sent_at timestamptz NULL,
confirmed_at timestamptz NULL GENERATED ALWAYS AS (LEAST(email_confirmed_at, phone_confirmed_at)) STORED,
email_change_token_current varchar(255) NULL DEFAULT ''::character varying,
email_change_confirm_status int2 NULL DEFAULT 0,
banned_until timestamptz NULL,
reauthentication_token varchar(255) NULL DEFAULT ''::character varying,
reauthentication_sent_at timestamptz NULL,
CONSTRAINT users_email_change_confirm_status_check CHECK (((email_change_confirm_status >= 0) AND (email_change_confirm_status <= 2))),
CONSTRAINT users_email_key UNIQUE (email),
CONSTRAINT users_phone_key UNIQUE (phone),
CONSTRAINT users_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX confirmation_token_idx ON auth.users USING btree (confirmation_token) WHERE ((confirmation_token)::text !~ '^[0-9 ]*$'::text);
CREATE UNIQUE INDEX email_change_token_current_idx ON auth.users USING btree (email_change_token_current) WHERE ((email_change_token_current)::text !~ '^[0-9 ]*$'::text);
CREATE UNIQUE INDEX email_change_token_new_idx ON auth.users USING btree (email_change_token_new) WHERE ((email_change_token_new)::text !~ '^[0-9 ]*$'::text);
CREATE UNIQUE INDEX reauthentication_token_idx ON auth.users USING btree (reauthentication_token) WHERE ((reauthentication_token)::text !~ '^[0-9 ]*$'::text);
CREATE UNIQUE INDEX recovery_token_idx ON auth.users USING btree (recovery_token) WHERE ((recovery_token)::text !~ '^[0-9 ]*$'::text);
CREATE INDEX users_instance_id_email_idx ON auth.users USING btree (instance_id, lower((email)::text));
CREATE INDEX users_instance_id_idx ON auth.users USING btree (instance_id);
-- Table Triggers
create trigger on_auth_user_created after
insert
on
auth.users for each row execute function handle_new_user();
-- auth.identities definition
-- Drop table
-- DROP TABLE auth.identities;
CREATE TABLE auth.identities (
id text NOT NULL,
user_id uuid NOT NULL,
identity_data jsonb NOT NULL,
provider text NOT NULL,
last_sign_in_at timestamptz NULL,
created_at timestamptz NULL,
updated_at timestamptz NULL,
CONSTRAINT identities_pkey PRIMARY KEY (provider, id),
CONSTRAINT identities_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE
);
CREATE INDEX identities_user_id_idx ON auth.identities USING btree (user_id);
-- auth.mfa_factors definition
-- Drop table
-- DROP TABLE auth.mfa_factors;
CREATE TABLE auth.mfa_factors (
id uuid NOT NULL,
user_id uuid NOT NULL,
friendly_name text NULL,
"factor_type" auth."factor_type" NOT NULL,
status auth."factor_status" NOT NULL,
created_at timestamptz NOT NULL,
updated_at timestamptz NOT NULL,
secret text NULL,
CONSTRAINT mfa_factors_pkey PRIMARY KEY (id),
CONSTRAINT mfa_factors_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE
);
CREATE INDEX factor_id_created_at_idx ON auth.mfa_factors USING btree (user_id, created_at);
CREATE UNIQUE INDEX mfa_factors_user_friendly_name_unique ON auth.mfa_factors USING btree (friendly_name, user_id) WHERE (TRIM(BOTH FROM friendly_name) <> ''::text);
-- auth.saml_providers definition
-- Drop table
-- DROP TABLE auth.saml_providers;
CREATE TABLE auth.saml_providers (
id uuid NOT NULL,
sso_provider_id uuid NOT NULL,
entity_id text NOT NULL,
metadata_xml text NOT NULL,
metadata_url text NULL,
attribute_mapping jsonb NULL,
created_at timestamptz NULL,
updated_at timestamptz NULL,
CONSTRAINT "entity_id not empty" CHECK ((char_length(entity_id) > 0)),
CONSTRAINT "metadata_url not empty" CHECK (((metadata_url = NULL::text) OR (char_length(metadata_url) > 0))),
CONSTRAINT "metadata_xml not empty" CHECK ((char_length(metadata_xml) > 0)),
CONSTRAINT saml_providers_entity_id_key UNIQUE (entity_id),
CONSTRAINT saml_providers_pkey PRIMARY KEY (id),
CONSTRAINT saml_providers_sso_provider_id_fkey FOREIGN KEY (sso_provider_id) REFERENCES auth.sso_providers(id) ON DELETE CASCADE
);
CREATE INDEX saml_providers_sso_provider_id_idx ON auth.saml_providers USING btree (sso_provider_id);
-- auth.saml_relay_states definition
-- Drop table
-- DROP TABLE auth.saml_relay_states;
CREATE TABLE auth.saml_relay_states (
id uuid NOT NULL,
sso_provider_id uuid NOT NULL,
request_id text NOT NULL,
for_email text NULL,
redirect_to text NULL,
from_ip_address inet NULL,
created_at timestamptz NULL,
updated_at timestamptz NULL,
CONSTRAINT "request_id not empty" CHECK ((char_length(request_id) > 0)),
CONSTRAINT saml_relay_states_pkey PRIMARY KEY (id),
CONSTRAINT saml_relay_states_sso_provider_id_fkey FOREIGN KEY (sso_provider_id) REFERENCES auth.sso_providers(id) ON DELETE CASCADE
);
CREATE INDEX saml_relay_states_for_email_idx ON auth.saml_relay_states USING btree (for_email);
CREATE INDEX saml_relay_states_sso_provider_id_idx ON auth.saml_relay_states USING btree (sso_provider_id);
-- auth.sessions definition
-- Drop table
-- DROP TABLE auth.sessions;
CREATE TABLE auth.sessions (
id uuid NOT NULL,
user_id uuid NOT NULL,
created_at timestamptz NULL,
updated_at timestamptz NULL,
factor_id uuid NULL,
aal auth."aal_level" NULL,
not_after timestamptz NULL,
CONSTRAINT sessions_pkey PRIMARY KEY (id),
CONSTRAINT sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE
);
CREATE INDEX sessions_user_id_idx ON auth.sessions USING btree (user_id);
CREATE INDEX user_id_created_at_idx ON auth.sessions USING btree (user_id, created_at);
-- auth.sso_domains definition
-- Drop table
-- DROP TABLE auth.sso_domains;
CREATE TABLE auth.sso_domains (
id uuid NOT NULL,
sso_provider_id uuid NOT NULL,
"domain" text NOT NULL,
created_at timestamptz NULL,
updated_at timestamptz NULL,
CONSTRAINT "domain not empty" CHECK ((char_length(domain) > 0)),
CONSTRAINT sso_domains_pkey PRIMARY KEY (id),
CONSTRAINT sso_domains_sso_provider_id_fkey FOREIGN KEY (sso_provider_id) REFERENCES auth.sso_providers(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX sso_domains_domain_idx ON auth.sso_domains USING btree (lower(domain));
CREATE INDEX sso_domains_sso_provider_id_idx ON auth.sso_domains USING btree (sso_provider_id);
-- auth.sso_sessions definition
-- Drop table
-- DROP TABLE auth.sso_sessions;
CREATE TABLE auth.sso_sessions (
id uuid NOT NULL,
session_id uuid NOT NULL,
sso_provider_id uuid NULL,
not_before timestamptz NULL,
not_after timestamptz NULL,
idp_initiated bool NULL DEFAULT false,
created_at timestamptz NULL,
updated_at timestamptz NULL,
CONSTRAINT sso_sessions_pkey PRIMARY KEY (id),
CONSTRAINT sso_sessions_session_id_fkey FOREIGN KEY (session_id) REFERENCES auth.sessions(id) ON DELETE CASCADE,
CONSTRAINT sso_sessions_sso_provider_id_fkey FOREIGN KEY (sso_provider_id) REFERENCES auth.sso_providers(id) ON DELETE CASCADE
);
CREATE INDEX sso_sessions_session_id_idx ON auth.sso_sessions USING btree (session_id);
CREATE INDEX sso_sessions_sso_provider_id_idx ON auth.sso_sessions USING btree (sso_provider_id);
-- auth.mfa_amr_claims definition
-- Drop table
-- DROP TABLE auth.mfa_amr_claims;
CREATE TABLE auth.mfa_amr_claims (
session_id uuid NOT NULL,
created_at timestamptz NOT NULL,
updated_at timestamptz NOT NULL,
authentication_method text NOT NULL,
id uuid NOT NULL,
CONSTRAINT amr_id_pk PRIMARY KEY (id),
CONSTRAINT mfa_amr_claims_session_id_authentication_method_pkey UNIQUE (session_id, authentication_method),
CONSTRAINT mfa_amr_claims_session_id_fkey FOREIGN KEY (session_id) REFERENCES auth.sessions(id) ON DELETE CASCADE
);
-- auth.mfa_challenges definition
-- Drop table
-- DROP TABLE auth.mfa_challenges;
CREATE TABLE auth.mfa_challenges (
id uuid NOT NULL,
factor_id uuid NOT NULL,
created_at timestamptz NOT NULL,
verified_at timestamptz NULL,
ip_address inet NOT NULL,
CONSTRAINT mfa_challenges_pkey PRIMARY KEY (id),
CONSTRAINT mfa_challenges_auth_factor_id_fkey FOREIGN KEY (factor_id) REFERENCES auth.mfa_factors(id) ON DELETE CASCADE
);
-- auth.refresh_tokens definition
-- Drop table
-- DROP TABLE auth.refresh_tokens;
CREATE TABLE auth.refresh_tokens (
instance_id uuid NULL,
id bigserial NOT NULL,
"token" varchar(255) NULL,
user_id varchar(255) NULL,
revoked bool NULL,
created_at timestamptz NULL,
updated_at timestamptz NULL,
parent varchar(255) NULL,
session_id uuid NULL,
CONSTRAINT refresh_tokens_pkey PRIMARY KEY (id),
CONSTRAINT refresh_tokens_token_unique UNIQUE (token),
CONSTRAINT refresh_tokens_session_id_fkey FOREIGN KEY (session_id) REFERENCES auth.sessions(id) ON DELETE CASCADE
);
CREATE INDEX refresh_token_session_id ON auth.refresh_tokens USING btree (session_id);
CREATE INDEX refresh_tokens_instance_id_idx ON auth.refresh_tokens USING btree (instance_id);
CREATE INDEX refresh_tokens_instance_id_user_id_idx ON auth.refresh_tokens USING btree (instance_id, user_id);
CREATE INDEX refresh_tokens_parent_idx ON auth.refresh_tokens USING btree (parent);
CREATE INDEX refresh_tokens_session_id_revoked_idx ON auth.refresh_tokens USING btree (session_id, revoked);
CREATE INDEX refresh_tokens_token_idx ON auth.refresh_tokens USING btree (token);
CREATE OR REPLACE FUNCTION auth.email()
RETURNS text
LANGUAGE sql
STABLE
AS $function$
select
coalesce(
nullif(current_setting('request.jwt.claim.email', true), ''),
(nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'email')
)::text
$function$
;
CREATE OR REPLACE FUNCTION auth.jwt()
RETURNS jsonb
LANGUAGE sql
STABLE
AS $function$
select
coalesce(
nullif(current_setting('request.jwt.claim', true), ''),
nullif(current_setting('request.jwt.claims', true), '')
)::jsonb
$function$
;
CREATE OR REPLACE FUNCTION auth.role()
RETURNS text
LANGUAGE sql
STABLE
AS $function$
select
coalesce(
nullif(current_setting('request.jwt.claim.role', true), ''),
(nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'role')
)::text
$function$
;
CREATE OR REPLACE FUNCTION auth.uid()
RETURNS uuid
LANGUAGE sql
STABLE
AS $function$
select
coalesce(
nullif(current_setting('request.jwt.claim.sub', true), ''),
(nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
)::uuid
$function$
;
-- DROP SCHEMA public;
CREATE SCHEMA public AUTHORIZATION postgres;
COMMENT ON SCHEMA public IS 'standard public schema';
-- public.profiles definition
-- Drop table
-- DROP TABLE public.profiles;
CREATE TABLE public.profiles (
id uuid NOT NULL,
updated_at timestamptz NULL,
username text NULL,
full_name text NULL,
avatar_url text NULL,
website text NULL,
CONSTRAINT profiles_pkey PRIMARY KEY (id),
CONSTRAINT profiles_username_key UNIQUE (username),
CONSTRAINT username_length CHECK ((char_length(username) >= 3))
);
-- public.profiles foreign keys
ALTER TABLE public.profiles ADD CONSTRAINT profiles_id_fkey FOREIGN KEY (id) REFERENCES auth.users(id);
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
begin
insert into public.profiles (id, full_name, avatar_url)
values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
return new;
end;
$function$
;
CREATE OR REPLACE FUNCTION public.install_available_extensions_and_test()
RETURNS boolean
LANGUAGE plpgsql
AS $function$
DECLARE extension_name TEXT;
allowed_extentions TEXT[] := string_to_array(current_setting('supautils.privileged_extensions'), ',');
BEGIN
FOREACH extension_name IN ARRAY allowed_extentions
LOOP
SELECT trim(extension_name) INTO extension_name;
/* skip below extensions check for now */
CONTINUE WHEN extension_name = 'pgroonga' OR extension_name = 'pgroonga_database' OR extension_name = 'pgsodium';
CONTINUE WHEN extension_name = 'plpgsql' OR extension_name = 'plpgsql_check' OR extension_name = 'pgtap';
CONTINUE WHEN extension_name = 'supabase_vault' OR extension_name = 'wrappers';
RAISE notice 'START TEST FOR: %', extension_name;
EXECUTE format('DROP EXTENSION IF EXISTS %s CASCADE', quote_ident(extension_name));
EXECUTE format('CREATE EXTENSION %s CASCADE', quote_ident(extension_name));
RAISE notice 'END TEST FOR: %', extension_name;
END LOOP;
RAISE notice 'EXTENSION TESTS COMPLETED..';
return true;
END;
$function$
;
(Executing this will have a few errors, but you can just ignore them as it does not have influence on the result) |
janpio
added
bug/2-confirmed
|
(The observation along the way lead me to create an improvement issue as well: #16615) |
|
Here is a simplified reproduction.
|
|
Hey @firzanarmani @mjlatty and @afcode123, what exactly does your connection string look like? We have a theory that somehow, sometimes |
|
I'm using the connection string as given in the 'Database Settings' in Supabase, i.e. The full output I get from running Not too sure if there is a way to increase the verbosity of the output from the command. Hope this helps. Feel free to correct me if I'm doing anything wrong. |
|
Using With the following schema
With
|
|
No, that is great @firzanarmani. What does you Prisma schema look like before you run |
|
Before running Worth noting that, similar to your reproduction attempt:
I find these to be true as well, which led me to using the schema as above. |
|
@janpio here's the full output of |
tomhoule
added a commit
to prisma/prisma-engines
that referenced
this issue
Dec 8, 2022
The namespaces are from the schema the engine was started with This should address prisma/prisma#16634 and prisma/prisma#16585
tomhoule
added a commit
to prisma/prisma-engines
that referenced
this issue
Dec 8, 2022
The namespaces are from the schema the engine was started with This should address prisma/prisma#16634 and prisma/prisma#16585
tomhoule
added a commit
to prisma/prisma-engines
that referenced
this issue
Dec 9, 2022
#3481) The namespaces are from the schema the engine was started with This should address prisma/prisma#16634 and prisma/prisma#16585
|
Should be fixed in prisma/prisma-engines#3481 |
|
Note: it's going in tomorrow's, 4.8.0 release |
|
unfortunately I am still getting the error in 4.8.0 (version below) |
|
I can unfortunately reproduce the problem with the schema you sent over @chrskerr, when creating and applying a migration with it |
|
Sorry @janpio I got mixed up between dev and deploy 🤦♂️ re-running now with |
|
I localized this issue finally. What's going on here is that first, if you define the schemas: datasource db {
schemas = ["foo", "bar"]
}And you either A quick workaround for now is to include all the schemas visible in the error message to the
|
janpio
changed the title
|
(I am not sure if @pimey's information above is correct - but please take a look if you can make it work via that somehow) |
|
I can't seem to figure out why but here are some findings on our end. failed!: changing failed!: With all that, changing the default schema via |
|
Yes, I think that matches what Julius found out later as well. We'll have a fix for this hopefully soon to test. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Bug description
Running
prisma migrate devgives me following error:I am using postgres + supabase auth and cross reference is used from public schema to auth schema.
How to reproduce
Expected behavior
No response
Prisma information
Prisma & prisma-client 4.7.0
Environment & setup
Prisma Version
The text was updated successfully, but these errors were encountered: