CODEWITHSUNDEEP
typescriptpostgresqlormnode-postgresdrizzle
eon37
eon37

Reputation: 93

How to declare self-referencing foreign key with Drizzle ORM

In a Typescript project, declaring a table using Drizzle on postgres-node as follows:


const contractsTable = pgTable("contracts", {
    id: serial("id").primaryKey(),
    underlyingId: integer("underlying_id").references(() => contractsTable.id),
    //...
})

results in the following Typescript error:

'contractsTable' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer.

Maintaining a separate type would be impractical because the schema is massive and is subject to change.

Is there a way to get Typescript to infer the correct type? I failed to do this through aliasing or casting the PgIntegerBuilderInitial type.

We also define a relationship as follows:

const contractsRelations = relations(
    contractsTable,
    ({ one, many }) => ({
        underlying: one(contractsTable, {
            fields: [contractsTable.underlyingId],
            references: [contractsTable.id],
        }),
        //...
    })
);

but I do need the database level constraint. Any ideas?

Upvotes: 7

Views: 6107

Answers (3)

Rupam
Rupam

Reputation: 1672

In case anyone wants a reference from the official docs, here it is:

https://orm.drizzle.team/docs/indexes-constraints#foreign-key

If you want to do a self reference, due to a TypeScript limitations you will have to either explicitly set return type for reference callback or use a standalone foreignKey operator.

import { serial, text, integer, foreignKey, pgTable, AnyPgColumn } from "drizzle-orm/pg-core";

export const user = pgTable("user", {
  id: serial("id"),
  name: text("name"),
  parentId: integer("parent_id").references((): AnyPgColumn => user.id)
});

// or
export const user = pgTable("user", {
  id: serial("id"),
  name: text("name"),
  parentId: integer("parent_id"),
}, (table) => [
  foreignKey({
    columns: [table.parentId],
    foreignColumns: [table.id],
    name: "custom_fk"
  })
]);

Upvotes: 3

Patricio Marrone
Patricio Marrone

Reputation: 1452

You can create a self reference in the table being created by doing

export const contractsTable = pgTable(
  "contracts",
  {
    id: serial("id").primaryKey(),
    underlyingId: integer("underlying_id"),
  },
  (table) => {
    return {
      parentReference: foreignKey({
        columns: [table.underlyingId],
        foreignColumns: [table.id],
        name: "contracts_underlying_id_fkey",
      }),
    };
  }
);

Correctly generated the foreign key.

Taken from Discord conversations and tested on

   drizzle-kit 0.21.4
   drizzle-orm 0.30.10

Upvotes: 7

Paco Navarrete
Paco Navarrete

Reputation: 121

According to the [BUG]: Self reference foreign key break relational query types #1607, you can solved like this:

export const contractsTable = pgTable("contracts", {
    id: serial("id").primaryKey(),
    underlyingId: integer("underlying_id").references((): AnyPgColumn => contractsTable.id),
})

export const contractsRelations = relations(
    contractsTable,
    ({ one, many }) => ({
        underlying: one(contractsTable, {
            fields: [contractsTable.underlyingId],
            references: [contractsTable.id],
        }),
    })
);

Upvotes: 12

Related Questions