Error during migration: NeonDbError

Hello guys, I recently started learning drizzle and was implementing drizzle + neon database. So I built my schema, generated a migration file. All was good till then. But when I try to actually implement the migration into my neon database. I am getting this error:

Error during migration: NeonDbError: there is no unique constraint matching given keys for referenced table "tests"
    at execute (backend\node_modules\@neondatabase\serverless\index.js:1546:39)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
.cjs:47:9)
    at async main (backend\drizzle\migrate.js:11:9) {
  code: '42830',
  sourceError: undefined
}

Error during migration: NeonDbError: there is no unique constraint matching given keys for referenced table "tests"
    at execute (backend\node_modules\@neondatabase\serverless\index.js:1546:39)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
.cjs:47:9)
    at async main (backend\drizzle\migrate.js:11:9) {
  code: '42830',
  sourceError: undefined
}

Now I thought this might be because of what it says, but I keep getting the error even after changing the schema. Any help would be appreciated :')

11 Replies

fascinating-indigo•2y ago

Could you post the schema or a small version to replicate the issue?

fascinating-indigoOP•2y ago

const { pgTable, serial, text, integer, timestamp } = require('drizzle-orm/pg-core') const testsTable = pgTable("tests", { test_id: serial("test_id").notNull().unique(), test_name: text("test_name").notNull(), dept: text("dept").notNull() }) const cubesTable = pgTable("cubes", { cube_id: serial("cube_id").notNull().unique(), test_id: integer("test_id").references(() => testsTable.test_id).notNull() }) module.exports = [ testsTable, cubesTable ] Here's a simple schema with the concerned columns. What am I doing wrong?

wise-white•2y ago

I think you need to add unique to the test_id on the cubesTable But I didn’t run or test it yet, that’s just my interpretation of the error I think you probably want to to use primaryKey() instead of unique and not null on the tests table though

wise-white•2y ago

https://orm.drizzle.team/docs/column-types/pg#primary-key

Drizzle ORM - PostgreSQL column types

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

fascinating-indigoOP•2y ago

Yes, I have tried that after reading the docs. But I still get the same error I have considered adding unique to the test_id in cubesTable and other tables as well, however in tables with multiple fkeys, it is not good design to add unique I believe

wise-white•2y ago

OK, so I just tested your code with an empty database and it worked. Have you tried deleting prior migrations/ folder and trying again. I'm using ESM syntax, but that's the only difference

import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';


export const testsTable = pgTable("tests", {
  test_id: serial("test_id").notNull().unique(),
  test_name: text("test_name").notNull(),
  dept: text("dept").notNull()
})

export const cubesTable = pgTable("cubes", {
  cube_id: serial("cube_id").notNull().unique(),
  test_id: integer("test_id").references(() => testsTable.test_id).notNull()
})

import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';


export const testsTable = pgTable("tests", {
  test_id: serial("test_id").notNull().unique(),
  test_name: text("test_name").notNull(),
  dept: text("dept").notNull()
})

export const cubesTable = pgTable("cubes", {
  cube_id: serial("cube_id").notNull().unique(),
  test_id: integer("test_id").references(() => testsTable.test_id).notNull()
})

fascinating-indigoOP•2y ago

Friend, I think we are near the issue. Is this the exact code you tested? I got the exact same error after creating a separate project and trying with this schema which you tried. Only thing I changed is I used the primaryKey() instead of notNull and unique

Error during migration: NeonDbError: there is no unique constraint matching given keys for referenced table "tests"

Error during migration: NeonDbError: there is no unique constraint matching given keys for referenced table "tests"

wise-white•2y ago

This (primaryKey) also works for me, but ONLY IF I drop the tables first. If the prior version of my schemas are there, then this error is given: error: cannot drop constraint tests_test_id_unique on table tests because other objects depend on it

import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';


export const testsTable = pgTable("tests", {
  test_id: serial("test_id"). primaryKey(),
  test_name: text("test_name").notNull(),
  dept: text("dept").notNull()
})

export const cubesTable = pgTable("cubes", {
  cube_id: serial("cube_id").notNull().unique(),
  test_id: integer("test_id").references(() => testsTable.test_id).notNull()
})

export type Cube = typeof cubesTable.$inferSelect;
export type Test = typeof testsTable.$inferSelect;

import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';


export const testsTable = pgTable("tests", {
  test_id: serial("test_id"). primaryKey(),
  test_name: text("test_name").notNull(),
  dept: text("dept").notNull()
})

export const cubesTable = pgTable("cubes", {
  cube_id: serial("cube_id").notNull().unique(),
  test_id: integer("test_id").references(() => testsTable.test_id).notNull()
})

export type Cube = typeof cubesTable.$inferSelect;
export type Test = typeof testsTable.$inferSelect;

I think you're best 1) dropping the tables first, 2) deleting the migrations folder in your project 3) re-run drizzle generate and push Just to start from a clean slate.

fascinating-indigoOP•2y ago

Alright I will try that and report back. Thanks a lot for your help! Yayy it works! I do not know what solved it but, I dropped the entire db in neon, created a new one, migrated to it. I didn't get any error and the migration was successful

wise-white•2y ago

Awesome. My guess is that the DB and schemas/migrations were out of sync and there was a missing intermediary step required in the schema migration. I'd need ot dive deeper to understand how to resolve that issue. Maybe that's where drizzle's pull/introspect command can help Glad it's working!

fascinating-indigoOP•2y ago

You're probably ryt! I didn't know of the pull command till now

Gaming

Programming

Error during migration: NeonDbError

Did you find this page helpful?