Drizzle Team•2w ago

drizzle-kit push not working with pgSchema

Hello, this is my first time using Drizzle and am running into some problems pushing my schema out. I have the following docker-compose.yaml that is spinning up a Postgres instance alongside Adminer.

services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: example
    ports:
      - 5432:5432

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: example
    ports:
      - 5432:5432

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

I am using a codebase first approach with Drizzle and have the following schema defined under the following path <project root>/drizzle/schema/auth.ts

import { sql } from "drizzle-orm";
import { boolean, check, pgRole, pgSchema, text, timestamp, uuid } from "drizzle-orm/pg-core";

export const admin = pgRole("admin", { createRole: true, createDb: true, inherit: true });

export const authSchema = pgSchema("auth");

export const user = authSchema.table("user", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: text("name").notNull(),
  username: text("username").notNull().unique(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("email_verified").notNull(),
  image: text("image"),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow(),
});

export const userAccount = authSchema.table(
  "useraccount",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    accountId: text("account_id"),
    providerId: text("provider_id"),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    accessToken: text("access_token"),
    refreshToken: text("refresh_token"),
    idToken: text("id_token"),
    scope: text("scope"),
    password: text("password").notNull(),
    accessTokenExpiresAt: timestamp("access_token_expires_at", { withTimezone: true }),
    refreshTokenExpiresAt: timestamp("refresh_token_expires_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow(),
  },
  (table) => [
    {
      checkConstraint: check("password_check", sql`char_length(${table.password}) >= 8`),
    },
  ],
);

export const session = authSchema.table("session", {
  id: uuid("id").defaultRandom().primaryKey(),
  token: text("token").notNull().unique(),
  ipAddress: text("ip_address"),
  userAgent: text("user_agent"),
  userId: text("user_id")
    .notNull()
    .references(() => user.id, { onDelete: "cascade" }),
  expiresAt: timestamp("expires_at", { withTimezone: true }).default(
    sql`(now() + interval '7 days')`,
  ),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow(),
});

export const verification = authSchema.table("verification", {
  id: uuid("id").defaultRandom().primaryKey(),
  identifier: text("identifier").notNull(),
  value: text("value").notNull(),
  expiresAt: timestamp("expires_at", { withTimezone: true }).default(
    sql`(now() + interval '7 days')`,
  ),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow(),
});

import { sql } from "drizzle-orm";
import { boolean, check, pgRole, pgSchema, text, timestamp, uuid } from "drizzle-orm/pg-core";

export const admin = pgRole("admin", { createRole: true, createDb: true, inherit: true });

export const authSchema = pgSchema("auth");

export const user = authSchema.table("user", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: text("name").notNull(),
  username: text("username").notNull().unique(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("email_verified").notNull(),
  image: text("image"),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow(),
});

export const userAccount = authSchema.table(
  "useraccount",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    accountId: text("account_id"),
    providerId: text("provider_id"),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    accessToken: text("access_token"),
    refreshToken: text("refresh_token"),
    idToken: text("id_token"),
    scope: text("scope"),
    password: text("password").notNull(),
    accessTokenExpiresAt: timestamp("access_token_expires_at", { withTimezone: true }),
    refreshTokenExpiresAt: timestamp("refresh_token_expires_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow(),
  },
  (table) => [
    {
      checkConstraint: check("password_check", sql`char_length(${table.password}) >= 8`),
    },
  ],
);

export const session = authSchema.table("session", {
  id: uuid("id").defaultRandom().primaryKey(),
  token: text("token").notNull().unique(),
  ipAddress: text("ip_address"),
  userAgent: text("user_agent"),
  userId: text("user_id")
    .notNull()
    .references(() => user.id, { onDelete: "cascade" }),
  expiresAt: timestamp("expires_at", { withTimezone: true }).default(
    sql`(now() + interval '7 days')`,
  ),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow(),
});

export const verification = authSchema.table("verification", {
  id: uuid("id").defaultRandom().primaryKey(),
  identifier: text("identifier").notNull(),
  value: text("value").notNull(),
  expiresAt: timestamp("expires_at", { withTimezone: true }).default(
    sql`(now() + interval '7 days')`,
  ),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow(),
});

And this is my drizzle.config.ts:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./drizzle/schema/*",
  out: "./drizzle/migrations/",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./drizzle/schema/*",
  out: "./drizzle/migrations/",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

I was able to successfully do drizzle-kit push and I did this while my databse was running in a container. However, when I open up Adminer to inspect the database, I do not see my auth schema shown in the UI nor any of the tables I have defined. Hoping someone can assist me here. Thanks in advance!

6 Replies

Optio1•2w ago

Not sure why the auth schema isnt showing up, but for me, I had huge issues with postgres and drizzlekit push when defining things inside of an object inside a list. I would change

},
  (table) => [
    {
      checkConstraint: check("password_check", sql`char_length(${table.password}) >= 8`),
    },
  ],
);

},
  (table) => [
    {
      checkConstraint: check("password_check", sql`char_length(${table.password}) >= 8`),
    },
  ],
);

},
  (table) => [
      checkConstraint: check("password_check", sql`char_length(${table.password}) >= 8`),
  ],
);

},
  (table) => [
      checkConstraint: check("password_check", sql`char_length(${table.password}) >= 8`),
  ],
);

this resolved quite a few issues for me. For reference this is the github issue for this https://github.com/drizzle-team/drizzle-orm/issues/3596

GitHub

[BUG]:Composite primary key not added to postgres schema when using...

Report hasn't been filed before. I have verified that the bug I'm about to report hasn't been filed before. What version of drizzle-orm are you using? 0.36.3 What version of drizzle-kit...

Optio1•2w ago

So I just changed my tables from pgTable(tableDefinition) to

export const customSchema = pgSchema('custom');
customshema.table(tableDefinition)

export const customSchema = pgSchema('custom');
customshema.table(tableDefinition)

and am experiencing the same issue. I found this issue with no known workaround. https://github.com/drizzle-team/drizzle-orm/issues/3476

GitHub

[BUG]: Custom schema migrations are not working · Issue #3476 · dri...

Duplicate of this issue. Accidentally pushed it to the wrong repo. What version of drizzle-orm are you using? 0.29.1 What version of drizzle-kit are you using? 0.20.6 Describe the Bug I am new to d...

MaveriX89OP•2w ago

@Optio1 that's interesting! Thanks for looking into that. It's sad that using Postgres schemas/folders doesn't work as expected especially if we're trying to organize our tables properly. @Optio1 I managed to find a workaround to this problem -- or maybe this is actually what is expected that we are supposed to do if we intend to make use of pgSchema in our codebases. I had to update my drizzle.config.ts to the following:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./drizzle/schema/*",
  out: "./drizzle/migrations/",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  schemaFilter: ["public", "auth"],
});

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./drizzle/schema/*",
  out: "./drizzle/migrations/",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  schemaFilter: ["public", "auth"],
});

The key field is schemaFilter . Apparently, Drizzle Kit needs to be told what schemas it can manage in the given database. So for eveny pgSchema created, its name has to be added to that list. The default behavior is it only works on the public schema which is why drizzle-kit push was basically a no-op when I was doing it before.

Optio1•2w ago

Can confirm this worked for me as well. Curious where you found that, would be worth updating the docs to have a link to that.

MaveriX89OP•2w ago

Funny enough, it came up after me going back and forth with ChatGPT haha

Optio1•2w ago

😂 whatever works works. Thanks for figuring it out.

Gaming

Programming

drizzle-kit push not working with pgSchema

Did you find this page helpful?