error - unhandledRejection: error: no schema has been selected to create in

[paulwongx]

Using pg-core with "drizzle-orm": "^0.25.2", and my following db.ts looks like the following:

// db.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.POSTGRES_DB_URL,
});

const db = drizzle(pool, { logger: true });

(async () => {
  await migrate(db, { migrationsFolder: "./drizzle" });
})();

I run the migration so my drizzle/ folder has a .sql file. My database doesn't have any tables though. I assume during the first route query, the db.ts file will run and create the tables needed. However, I'm getting this error:

Query: CREATE SCHEMA IF NOT EXISTS "drizzle"
Query: 
                        CREATE TABLE IF NOT EXISTS "drizzle"."__drizzle_migrations" (
                                id SERIAL PRIMARY KEY,
                                hash text NOT NULL,
                                created_at bigint
                        )

Query: select id, hash, created_at from "drizzle"."__drizzle_migrations" order by created_at desc limit 1
Query: begin
Query: CREATE TABLE IF NOT EXISTS "accounts" (
        "user_id" integer,
        "access_token" text,
        "expires_at" integer,
        "id_token" text,
        "provider" text NOT NULL,
        "provider_account_id" text NOT NULL,
        "refresh_token" text,
        "scope" text,
        "session_state" text,
        "token_type" text,
        "type" text NOT NULL
);

Query: rollback
error - unhandledRejection: error: no schema has been selected to create in

[GorlikItsMe]

I added

CREATE SCHEMA IF NOT EXISTS "public";

on top of migration file (.sql file in /migrations folder)

[othman2408]

that's the solution, but why do I need to add it manually?

[nicholasdly]

It looks like you forgot to add your schema to your db instance. Try the following:

// db.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { Pool } from "pg";
import * as schema from "./schema"; // Import all tables

const pool = new Pool({
  connectionString: process.env.POSTGRES_DB_URL,
});

const db = drizzle(pool, { schema, logger: true }); // Add schema here

// ...