[SOLVED] How to declare self-referencing foreign key with Drizzle ORM

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?

Solution

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],
        }),
    })
);