Drizzle Team•11mo ago

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•11mo 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.

ashishansurkarOP•11mo ago

That worked. Thank you very much @solo

Mykhailo•11mo ago

you are welcome!

ashishansurkarOP•11mo 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•11mo 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
);

ashishansurkarOP•11mo 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•11mo ago

happy to help you

Gaming

Programming

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