zev Posts - Answer Overflow

zev

•Created by zev on 4/12/2024 in #help

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.

4 replies

Gaming

Programming