Migrations complain about missing relations

I get the following error when running migrations:

PostgresError: relation "public.users" does not exist

PostgresError: relation "public.users" does not exist

The weird thing is that the table gets created in the drizzle schema, so when the migration.sql file references "public.users" it fails. The public schema is also only mentioned once in the migration file.

DO $$ BEGIN
 CREATE TYPE "public"."account_type" AS ENUM('discord');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "accounts" (
    "account_id" text PRIMARY KEY NOT NULL,
    "account_type" "account_type" NOT NULL,
    "access_token" text NOT NULL,
    "refresh_token" text NOT NULL,
    "user_id" integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "session" (
    "id" text PRIMARY KEY NOT NULL,
    "data" json NOT NULL,
    "expires_at" timestamp NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "users" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" varchar(256),
    "email" varchar(256) NOT NULL,
    "avatar_url" text
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "accounts" ADD CONSTRAINT "accounts_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "email_idx" ON "users" USING btree ("email");

DO $$ BEGIN
 CREATE TYPE "public"."account_type" AS ENUM('discord');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "accounts" (
    "account_id" text PRIMARY KEY NOT NULL,
    "account_type" "account_type" NOT NULL,
    "access_token" text NOT NULL,
    "refresh_token" text NOT NULL,
    "user_id" integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "session" (
    "id" text PRIMARY KEY NOT NULL,
    "data" json NOT NULL,
    "expires_at" timestamp NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "users" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" varchar(256),
    "email" varchar(256) NOT NULL,
    "avatar_url" text
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "accounts" ADD CONSTRAINT "accounts_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "email_idx" ON "users" USING btree ("email");

If I remove the "public". infront of "users" the migration runs fine, but every table is created in the drizzle schema. Am I doing something wrong here?

1 Reply

stibaOP•6mo ago

Here are my schema definitions:

import { relations } from "drizzle-orm";
import {
    integer,
    json,
    pgEnum,
    pgTable,
    serial,
    text,
    timestamp,
    uniqueIndex,
    varchar,
} from "drizzle-orm/pg-core";

export const users = pgTable(
    "users",
    {
        id: serial("id").primaryKey(),
        name: varchar("name", { length: 256 }),
        email: varchar("email", { length: 256 }).notNull(),
        avatarUrl: text("avatar_url"),
    },
    (users) => {
        return {
            emailIndex: uniqueIndex("email_idx").on(users.email),
        };
    },
);

export const accountTypeEnum = pgEnum("account_type", ["discord"]);

export const accounts = pgTable("accounts", {
    id: text("account_id").primaryKey(),
    type: accountTypeEnum("account_type").notNull(),
    accessToken: text("access_token").notNull(),
    refreshToken: text("refresh_token").notNull(),
    userId: integer("user_id")
        .references(() => users.id)
        .notNull(),
});

export const session = pgTable("session", {
    id: text("id").primaryKey(),
    data: json("data").notNull(),
    expiresAt: timestamp("expires_at").notNull(),
});

export const userRelations = relations(users, ({ many }) => ({
    accounts: many(accounts),
}));

export const accountsRelations = relations(accounts, ({ one }) => ({
    user: one(users, {
        fields: [accounts.userId],
        references: [users.id],
    }),
}));

import { relations } from "drizzle-orm";
import {
    integer,
    json,
    pgEnum,
    pgTable,
    serial,
    text,
    timestamp,
    uniqueIndex,
    varchar,
} from "drizzle-orm/pg-core";

export const users = pgTable(
    "users",
    {
        id: serial("id").primaryKey(),
        name: varchar("name", { length: 256 }),
        email: varchar("email", { length: 256 }).notNull(),
        avatarUrl: text("avatar_url"),
    },
    (users) => {
        return {
            emailIndex: uniqueIndex("email_idx").on(users.email),
        };
    },
);

export const accountTypeEnum = pgEnum("account_type", ["discord"]);

export const accounts = pgTable("accounts", {
    id: text("account_id").primaryKey(),
    type: accountTypeEnum("account_type").notNull(),
    accessToken: text("access_token").notNull(),
    refreshToken: text("refresh_token").notNull(),
    userId: integer("user_id")
        .references(() => users.id)
        .notNull(),
});

export const session = pgTable("session", {
    id: text("id").primaryKey(),
    data: json("data").notNull(),
    expiresAt: timestamp("expires_at").notNull(),
});

export const userRelations = relations(users, ({ many }) => ({
    accounts: many(accounts),
}));

export const accountsRelations = relations(accounts, ({ one }) => ({
    user: one(users, {
        fields: [accounts.userId],
        references: [users.id],
    }),
}));

Gaming

Programming

Migrations complain about missing relations