DT
Drizzle Team•13mo ago
ashishansurkar

how to define postgres schema (other than 'public') while setting up with drizzle-orm/node-postgres

I have this code : import { integer, serial, text, pgTable } from 'drizzle-orm/pg-core'; export const users = pgTable('prod.users', { id: serial('id').primaryKey(), email: text('email'), }); But getting error : relation "prod.users" does not exist
7 Replies
Mykhailo
Mykhailo•13mo ago
Hello, @ashishansurkar! You can find information related to schemas in docs: https://orm.drizzle.team/docs/schemas
Drizzle ORM - next gen TypeScript ORM
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
ashishansurkar
ashishansurkarOP•13mo ago
That worked. Thank you very much @solo
Mykhailo
Mykhailo•13mo ago
you are welcome!
ashishansurkar
ashishansurkarOP•13mo ago
In the doc, we have to set the schema and then set the table. Is there a way we can set the schema after setting the table? I want to have the benefit of types from the table but I want to assign Postgres schema dynamically at runtime based on dev/prod environment.
Mykhailo
Mykhailo•13mo ago
@ashishansurkar probably this will work 
import 'dotenv/config';
import { pgSchema, serial, text } from 'drizzle-orm/pg-core';

export const mySchema = pgSchema(process.env.DB_SCHEMA!);

export const users = mySchema.table('usersTable', {
  id: serial('id').primaryKey(),
  name: text('name'),
});
import 'dotenv/config';
import { pgSchema, serial, text } from 'drizzle-orm/pg-core';

export const mySchema = pgSchema(process.env.DB_SCHEMA!);

export const users = mySchema.table('usersTable', {
  id: serial('id').primaryKey(),
  name: text('name'),
});
CREATE SCHEMA "prod";
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "prod"."usersTable" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" text
);
CREATE SCHEMA "prod";
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "prod"."usersTable" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" text
);
ashishansurkar
ashishansurkarOP•13mo ago
@solo , Thanks again. That worked as well. I tried this earlier but forgot to import dotenv and thought it wouldn't work. Much appreciated.
Mykhailo
Mykhailo•13mo ago
happy to help you

Did you find this page helpful?