[BUG]: Custom schema migrations are not working

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 drizzle. I tried to create tables in separate, custom schema, using the examples from official github and custom schema usage from official documentation. Generation of the schema works fine but when I try to migrate a postgres database I get:

npm run migrate

> pg-proxy@1.0.0 migrate
> drizzle-kit push:pg

drizzle-kit: v0.20.6
drizzle-orm: v0.29.1

No config path provided, using default path
Reading config file '/Users/<USER>/repos/opensource/pg-proxy/drizzle.config.ts'
[i] No changes detected

Schema is: schema.ts

import { pgSchema, serial, text } from "drizzle-orm/pg-core";

const mySchema = pgSchema('test');

export const users = mySchema.table("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull(),
  cityId: serial("city_id").references(() => cities.id),
});

export const cities = mySchema.table("cities", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
});

Notice that the database is empty, just created using docker-compose. The drizzle-kit generate:pg --schema src/schema.ts command produces following schemas: 0000_futuristic_devos.sql

CREATE TABLE IF NOT EXISTS "test"."cities" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" text NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "test"."users" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" text NOT NULL,
    "email" text NOT NULL,
    "city_id" serial NOT NULL
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "test"."users" ADD CONSTRAINT "users_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "test"."cities"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

Notice how the produced SQL does not have any schema-related creations. it just tries to create a table in the test schema. It is strange why does generate command does not create a test schema first and only then creates tables inside of this schema. Maybe this is an expected behaviour and custom schema should be created beforehand in a separate schema or something? If I change schema.ts to this: schema.ts

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

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull(),
  cityId: serial("city_id").references(() => cities.id),
});

export const cities = pgTable("cities", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
});

Then delete migration files, regenerate them and migrate everything works as expected and migrations are applied.

Expected behavior

I expect drizzle-kit push:pg command to create a custom schema, tables inside this schema or at least tell me what is the issue and why after running this command I don't see any changes in the DB.

Environment & setup

Hardware: MacBook Pro (m1) System Version: macOS 14.1.1 (23B81) Postgres version: 15.5 Runtime: npm 9.8.1, node v18.18.2

drizzle.config.ts

import { defineConfig, type Config } from "drizzle-kit";
require("dotenv").config();

// DB_URL is postgres://postgres:postgres@localhost:5431/bug?search_path=test
export default defineConfig({
  schema: "src/schema.ts",
  out: "drizzle",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env["DB_URL"]!,
  },
  verbose: true,
  strict: true,
}) satisfies Config;

drizzle-team / drizzle-orm