[FEATURE]: drizzle-kit introspect should generate relation in generated schema.ts

[conanak99]

Describe what you want

Currently, running drizzle-kit introspect:{dialect} command lets you pull DDL from an existing database and generate schema.ts.

However, the generated schema.ts only contains the declaration for each table. Example:

import { pgTable, serial, text, integer, jsonb } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    name: text('name'),
});

export const profileInfo = pgTable('profile_info', {
    id: serial('id').primaryKey(),
    userId: integer("user_id").references(() => users.id),
    metadata: jsonb("metadata"),
});

// Missing the relation between users and profileInfo. 
// Even though drizzle generated the reference correctly.

It would be useful if drizzle-kit could infer and generate the relations based on foreign key references. This approach is used by other frameworks https://www.prisma.io/docs/concepts/components/introspection#relations

The generated schema.ts should look like this.

import { pgTable, serial, text, integer, jsonb } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    name: text('name'),
});

export const profileInfo = pgTable('profile_info', {
    id: serial('id').primaryKey(),
    userId: integer("user_id").references(() => users.id),
    metadata: jsonb("metadata"),
});

// Auto generated the relation
export const usersProfileInfoRelations = relations(users, ({ one }) => ({
    profileInfo: one(profileInfo, {
        fields: [users.id],
        references: [profileInfo.userId],
    }),
}));

[conanak99]

I think we can use the same approach as Prisma.

• By default, add a [one-to-many] relation to data model for a foreign key it finds in your database schema:
• Adds a [one-to-one] relation to data model when the foreign key on a table has a UNIQUE constraint
• For [many-to-many], is it hard to detect the junction tables? Then we can define and store associations between related tables?

[algora-pbc]

💎 $200 bounty created by @john-griffin 👉 To claim this bounty, submit your pull request on Algora 📝 Before proceeding, please make sure you can receive payouts in your country 💵 Payment arrives in your account 2-5 days after the bounty is rewarded 💯 You keep 100% of the bounty award 🙏 Thank you for contributing to drizzle-team/drizzle-kit-mirror!

[Kartik1397]

/attempt drizzle-team/drizzle-orm#3358

[evoxf1]

/attempt drizzle-team/drizzle-orm#3358

[Kartik1397]

Hi @AndriiSherman, is drizzle-kit closed source? If it is closed source then is there any way I contribute to this issue?

[xsjcTony]

+1. Surprisingly this is not included in the introspect by default. Relations is some kinds of primitives of relational DBs, that they should be included in introspecting.

[smblee]

+1

[gmotta-dev]

• 2

[gmwill934]

+1

[Scalahansolo]

Y'all. The Drizzle team is actively working on this and will be in the upcoming release from what they've said in Discord. Just gotta be patient.

[terion-name]

However, the generated schema.ts only contains the declaration for each table. Example:

import { pgTable, serial, text, integer, jsonb } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
});

export const profileInfo = pgTable('profile_info', {
  id: serial('id').primaryKey(),
  userId: integer("user_id").references(() => users.id),
  metadata: jsonb("metadata"),
});

// Missing the relation between users and profileInfo. 
// Even though drizzle generated the reference correctly.

Currently it doesn't even add .references() in table schema.

For example I have in db:

create table "public"."test2" (
    "id" bigint generated by default as identity not null,
    "created_at" timestamp with time zone not null default now(),
    "test_id" bigint
);

alter table "public"."test2" enable row level security;

CREATE UNIQUE INDEX test2_pkey ON public.test2 USING btree (id);

alter table "public"."test2" add constraint "test2_pkey" PRIMARY KEY using index "test2_pkey";

# foreign key!
alter table "public"."test2" add constraint "public_test2_test_id_fkey" FOREIGN KEY (test_id) REFERENCES test(id) not valid;

alter table "public"."test2" validate constraint "public_test2_test_id_fkey";

But introspected result:

export const test2 = pgTable("test2", {
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    id: bigint("id", { mode: "number" }).notNull(),
    created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    test_id: bigint("test_id", { mode: "number" }), // <-------- has no reference !!!!
});

and log:

Using 'postgres' driver for database querying
[✓] 2  tables fetched
[✓] 8  columns fetched
[✓] 10 enums fetched
[✓] 0  indexes fetched
[✓] 0  foreign keys fetched

0 indexes fetched and 0 foreign keys fetched while they are definetely there

[AlexandroMtzG]

I found a workaround for my use case:

import { drizzle } from "drizzle-orm/node-postgres";
import pg from "pg";
import * as schema from "./schema";
+ import * as relations from "./relations";

const connectionString = process.env.NODE_ENV === "test" ? process.env.DATABASE_URL_TEST : process.env.DATABASE_URL;

export const client = new pg.Pool({
  connectionString: connectionString,
  ssl: process.env.DB_SSL === "true" ? true : false,
  max: 20,
});

export const drizzleDb = drizzle(client, {
- schema,
+  schema: { ...schema, ...relations },
});

[Niharika0104]

@conanak99 I see some engagement on the issue, but it's still open on GitHub. Could you please confirm if it's been resolved or if a fix is required?

[aadarsh-nagrath]

Whats update on this issue ?