Jon Koops Posts - Answer Overflow

Jon Koops

•Created by Jon Koops on 6/8/2024 in #help

No type inference when specifying a reference.

Hi, I am trying out Drizzle for my application, and I am having some issues when I have a table that has a one-to-one and one-to-many relation on another table. I have the following schema:

import { relations } from 'drizzle-orm';
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const invitations = sqliteTable('invitations', {
  id: integer('id').primaryKey({ autoIncrement: true }).notNull(),
  status: text('status', { enum: ['ACCEPTED', 'REJECTED'] }).notNull(),
  email: text('email').unique().notNull(),
  notes: text('notes'),
  primaryGuestId: integer('primary_guest_id').notNull(),
});

export const invitationsRelations = relations(invitations, ({ one, many }) => ({
  primaryGuest: one(guests, { relationName: 'primaryGuest', fields: [invitations.primaryGuestId], references: [guests.id] }),
  guests: many(guests),
}));

export const guests = sqliteTable('guests', {
  id: integer('id').primaryKey({ autoIncrement: true }).notNull(),
  firstName: text('first_name').notNull(),
  lastName: text('last_name').notNull(),
  invitationId: integer('invitation_id').notNull().references(() => invitations.id),
});

export const guestsRelations = relations(guests, ({ one }) => ({
  invitation: one(invitations, { fields: [guests.invitationId], references: [invitations.id] }),
}));

import { relations } from 'drizzle-orm';
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const invitations = sqliteTable('invitations', {
  id: integer('id').primaryKey({ autoIncrement: true }).notNull(),
  status: text('status', { enum: ['ACCEPTED', 'REJECTED'] }).notNull(),
  email: text('email').unique().notNull(),
  notes: text('notes'),
  primaryGuestId: integer('primary_guest_id').notNull(),
});

export const invitationsRelations = relations(invitations, ({ one, many }) => ({
  primaryGuest: one(guests, { relationName: 'primaryGuest', fields: [invitations.primaryGuestId], references: [guests.id] }),
  guests: many(guests),
}));

export const guests = sqliteTable('guests', {
  id: integer('id').primaryKey({ autoIncrement: true }).notNull(),
  firstName: text('first_name').notNull(),
  lastName: text('last_name').notNull(),
  invitationId: integer('invitation_id').notNull().references(() => invitations.id),
});

export const guestsRelations = relations(guests, ({ one }) => ({
  invitation: one(invitations, { fields: [guests.invitationId], references: [invitations.id] }),
}));

Which generates the following SQL:

CREATE TABLE `guests` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `first_name` text NOT NULL,
    `last_name` text NOT NULL,
    `invitation_id` integer NOT NULL,
    FOREIGN KEY (`invitation_id`) REFERENCES `invitations`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE TABLE `invitations` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `status` text NOT NULL,
    `email` text NOT NULL,
    `notes` text,
    `primary_guest_id` integer NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `invitations_email_unique` ON `invitations` (`email`);

CREATE TABLE `guests` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `first_name` text NOT NULL,
    `last_name` text NOT NULL,
    `invitation_id` integer NOT NULL,
    FOREIGN KEY (`invitation_id`) REFERENCES `invitations`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE TABLE `invitations` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `status` text NOT NULL,
    `email` text NOT NULL,
    `notes` text,
    `primary_guest_id` integer NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `invitations_email_unique` ON `invitations` (`email`);

5 replies

Gaming

Programming