Mysterious Postgres Schema Issue

I have the following schema.ts file:

import { pgTable, uuid } from 'drizzle-orm/pg-core';

export const FacultyTable = pgTable(
    'faculty',
    {
        id: uuid('id').primaryKey().defaultRandom()
    }
);

export const CourseTable = pgTable(
    'courses',
    {
        id: uuid('id').primaryKey().defaultRandom(),
        bId: uuid('bId').references(() => FacultyTable.id).notNull()
    }
);

import { pgTable, uuid } from 'drizzle-orm/pg-core';

export const FacultyTable = pgTable(
    'faculty',
    {
        id: uuid('id').primaryKey().defaultRandom()
    }
);

export const CourseTable = pgTable(
    'courses',
    {
        id: uuid('id').primaryKey().defaultRandom(),
        bId: uuid('bId').references(() => FacultyTable.id).notNull()
    }
);

To generate the migration sql I run the following:

> yarn drizzle-kit generate:pg

> yarn drizzle-kit generate:pg

And then to complete the migration I run this:

import "dotenv/config";
import { drizzle } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";
const postgres = require("postgres");

const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

async function main() {
    await migrate(drizzle(migrationClient), {
        migrationsFolder: "./src/db/migrations"
    });

    await migrationClient.end();
}

main();

import "dotenv/config";
import { drizzle } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";
const postgres = require("postgres");

const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

async function main() {
    await migrate(drizzle(migrationClient), {
        migrationsFolder: "./src/db/migrations"
    });

    await migrationClient.end();
}

main();

This all works exactly as I would expect, and opening drizzle studio I see the correct columns. But, for some reason when I switch the uuid from aId to bId it stops working. In fact, everything I've tried changing it to has resulted in the same error:

PostgresError: column "bId" referenced in foreign key constraint does not exist

PostgresError: column "bId" referenced in foreign key constraint does not exist

I don't see any errors in the SQL code generated but i can send that if any of you think it would be useful. Additionally, I can send my dirzzle.config.ts, package.json, and a photo of my file structure. Update: I just created a completely new project and was only partially able to reproduce the issue. It doesn't work with aId either now.

1 Reply

zev•7mo ago

Update 2: I think the issue could be with the order that Drizzle is creating the tables. Here's the generated SQL:

CREATE TABLE IF NOT EXISTS "course" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "aId" uuid NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "faculty" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "course" ADD CONSTRAINT "course_aId_faculty_id_fk" FOREIGN KEY ("aId") REFERENCES "faculty"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS "course" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "aId" uuid NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "faculty" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "course" ADD CONSTRAINT "course_aId_faculty_id_fk" FOREIGN KEY ("aId") REFERENCES "faculty"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

And if I just manually swap the order that the tables are created the code runs without errors. Hmm, I think that what Drizzle is generating should be valid though, right? Maybe this is an issue with my Postgres installation?? Update 3 -- Resolved: Just dropped my database and recreated it, this fixed the issue somehow. If anyone knows why this may have worked please let me know cause I'm curous.

Gaming

Programming

Mysterious Postgres Schema Issue