[BUG]: `migrate` command creates tables in the wrong postgres schema

[lmgraf]

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?

1.0.0@beta.22

What version of drizzle-kit are you using?

1.0.0@beta.22

Other packages

No response

Describe the Bug

Affected Setups

• Drizzle ORM connecting to any PostgreSQL database
• I only tested it using the node-postgres (pg) driver

What is the undesired behavior?

• The Problem: When migrations.schema inside drizzle.config.ts matches the postgres db username, drizzle-kit migrate creates all user defined tables on the "$user" schema, instead of the expected "public" schema. (EDITED)
• This does NOT happen, when migrations.schema is set to something other than the db username "$user".
• Side Effect: The schema field inside snapshot.json still points to "public", even though the tables were actually created on the "drizzle" schema.

What are the steps to reproduce it?

0. Setup a postgres Database with the owner drizzle
1. Set migrations.schema in drizzle.config.ts to the same value as the db username that owns the db ("drizzle"):

import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";

config({ path: ".env" });

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./src/db/migrations",
  dialect: "postgresql",
  dbCredentials: { url: process.env.DATABASE_URL! },
  migrations: { schema: "drizzle" }, // Or just set it to what the db username is, they just have to be exactly the same
});

2. Define a standard table in schema.ts without using pgSchema:

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

export const user = table("user", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
});

3. Run drizzle-kit generate. Notice how the generated sql is correct: It's "user", and NOT "drizzle"."user"

Generated SQL src/db/migrations/[timestamp]/migration.sql:

CREATE TABLE "user" (
	"id" text PRIMARY KEY,
	"name" text NOT NULL,
	"email" text NOT NULL UNIQUE,
);

Snapshot src/db/migrations/[timestamp]/snapshot.json:

{
  "version": "8",
  "dialect": "postgres",
  "id": "a03e228c-9e0f-4469-adfe-9692249ef1f9",
  "prevIds": [
    "00000000-0000-0000-0000-000000000000"
  ],
  "ddl": [
    {
      "isRlsEnabled": false,
      "name": "user",
      "entityType": "tables",
      "schema": "public", // <- notice EXPECTED schema
    },
    {
      "type": "text",
    /* [...] */
      "name": "id",
      "entityType": "columns",
      "schema": "public", // <- notice EXPECTED schema
      "table": "user"
    },
    {
      "type": "text",
    /* [...] */
      "name": "name",
      "entityType": "columns",
      "schema": "public", // <- notice EXPECTED schema
      "table": "user"
    },
    {
      "type": "text",
    /* [...] */
      "name": "email",
      "entityType": "columns",
      "schema": "public", // <- notice EXPECTED schema
      "table": "user"
    }
    /* [... ETC ...] */
  ],
  "renames": []
}

4. Now run drizzle-kit migrate.
5. Check the database relations using \d in psql. The user table will be defined on the drizzle schema, entirely contradicting the snapshot.json which defines it under public.

DATABASE REALITY:

                     List of relations
 Schema  |            Name             |   Type   |  Owner
---------+-----------------------------+----------+---------
 drizzle | __drizzle_migrations        | table    | drizzle
 drizzle | __drizzle_migrations_id_seq | sequence | drizzle
 drizzle | user                        | table    | drizzle
    ^ 
INCORRECT

6. Optional: To prove that this only happens when migrations.schema is set to "drizzle", execute DROP DATABASE example; and start again at step 1. But now set migrations.schema to something other than the db username or "public" and observe how the issue goes away. BEWARE: If you do not drop the database, the bug persists. Meaning, if you simply drop all the tables or the "drizzle" schema, and then re-run the migrations using drizzle-kit migrate, then migrations.schema will keep controlling the schema of all user defined tables.

What is the desired result?

DESIRED RESULT:

                     List of relations
 Schema  |            Name             |   Type   |  Owner
---------+-----------------------------+----------+---------
 drizzle | __drizzle_migrations        | table    | drizzle
 drizzle | __drizzle_migrations_id_seq | sequence | drizzle
 public  | user                        | table    | drizzle
    ^ 
 CORRECT

1. The migrations.schema configuration property should only ever dictate the schema for Drizzle's internal migration tables and sequences, namely: the __drizzle_migrations table and the __drizzle_migrations_id_seq sequence

2. Application tables defined without an explicit pgSchema layout should ALWAYS respect the database driver's native environment or fallback to public, instead of the schema specified in config.migrations.schema.

3. The generated snapshot.json should always be in sync with what gets actually created in the database (only the schema property is wrong in this case).

Workarounds

Workaround 1

Add the following code to the top of the generated migration.sql file:

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

Workaround 2

(if you don't want to manually edit migration.sql files)
Set config.migrations.schema to something other than "drizzle" (e.g. "public"), to create all tables on the public schema, and thus remove the schema mismatch between the database reality and the snapshot.json file.

Related issues

• [BUG]: "migrate" command fails when schema already exists: Failed query CREATE SCHEMA "example" #5274
• The bug described in this issue, was likely an additional cause for [BUG]: Postgres | applying migrations...error: relation "public.users" does not exist #4369

[lmgraf]

Update: I am having trouble consistently reproducing this on a completely fresh, wiped database instance. The behavior seems tied to sticky session states or dirty default schemas left over from previous migration runs/engines.

What actually fixed my issue in the end: Creating a fresh postgres instance with DROP DATABASE name and CREATE DATABASE name.

Sorry for the confusion.

EDIT: This was before I edited the original issue.

[lmgraf]

Udate: I was now able to reproduce the issue in a fresh project, and found, that this bug only happens, when migrations.schema in drizzle.config.ts is set to "drizzle", which is also the default value. I edited the above issue accordingly.

[fireayehu]

Can you upgrade to 1.0.0-rc.3 version and try, it could probably be fixed there

[cschanhniem]

I reproduced this and traced the root cause to drizzle-kit migrate setting the connection search_path to migrations.schema before running the migration SQL, but never resetting it to the default schema for user table creation.

The relevant code flow is:

1. migrate obtains a connection from the driver
2. It sets SET search_path TO drizzle (or whatever migrations.schema is)
3. It runs the migration SQL — which uses unqualified table names
4. PG resolves unqualified names against search_path, so CREATE TABLE "user" lands in drizzle schema
5. snapshot.json records "schema": "public" because the snapshot generator doesn't read search_path — it assumes the default

The fix should either (a) prefix generated migration SQL with an explicit SET search_path TO public before user-table DDL, or (b) have the migrator execute user-table DDL on a separate connection that doesn't have the custom search_path. Option (a) is simpler and matches the workaround already described. The snapshot writer also needs to be aware of the active schema at generation time.

I can open a PR if the team agrees on approach (a).

[lmgraf]

@fireayehu I can confirm that this bug exists with both drizzle-orm and drizzle-kit updated to the 1.0.0-rc.3 version.
@cschanhniem Thanks for taking the time, I appreciate it :)

[lmgraf]

I dug into the codebase, and I found that what you're describing @cschanhniem, is not quite what's happening (Drizzle never sets search_path to anything). If what you described were true, the described bug would always happen, no matter what migrations.schema is set to, which is not the case.

The relevant code is the migrate method on the PgDialect class in drizzle-orm/src/pg-core/dialect.ts.

Context

• An unqualified query in Postgres is one where the schema namespace is not explicitly specified. For example, CREATE TABLE "user"; is unqualified, whereas CREATE TABLE "public"."user"; is fully qualified.
• Postgres relies on the search_path configuration setting to determine which schema to use when executing unqualified queries. This means, whenever an unqualified query is executed, Postgres will:
  1. FIRST search for a schema matching "$user", which is drizzle in my case.
  2. If such a schema is not found, the schema will fall back to public.

What is actually happening

Again, see drizzle-orm/src/pg-core/dialect.ts

1. Postgres Default State: By default, Postgres sets search_path to "$user", public.
2. Drizzle Creates the Schema: Before executing the migration SQL, Drizzle runs CREATE SCHEMA IF NOT EXISTS "drizzle" (or whatever migrations.schema is configured to).
3. Drizzle creates the migration tables correctly on the schema specified in migrations.schema
4. Drizzle runs the migrations: When Drizzle loops through the migration statements and executes CREATE TABLE "user", Postgres evaluates its search_path.
5. Postgres intercepts the unqualified query: Posgtgres looks for a schema called "$user" and finds it, BECAUSE Drizzle created it in step 2 BECAUSE "user" === migrations.schema → user defined tables are created on the "$user" schema. To be absolutely clear: this only happens if the db username exactly matches migrations.schema
6. Finally, snapshot.json still records "schema": "public" because Drizzle assumes standard behavior and doesn't account for Postgres resolving unqualified names against an existing username-matched schema.

TLDR

PgDialect.migrate method creates user defined tables on "$user" schema (instead of public schema), if the Postgres DB username matches migrations.schema (aka "$user" === migrations.schema).

Fix

As @cschanhniem already said, add SET search_path TO public; inside the PgDialect.migrate method, before running the users migrations.

[lmgraf]

I just edited the original issue's reproduction steps to match the actual bug I just described.