DT
Drizzle Team•3mo ago
xhudaman

Types don't infer correctly for tables with`references` method?

I'm using Drizzle with Expo SQLite in an Expo managed app. On my schema all of the tables that contain simple column data, i.e. primitive types like text or int, get typed correctly. Any time I create an association with the references method the generated type becomes { [x: string]: any }. I'm using: - Expo SDK: 51 - Expo SQLite: 14.0.6 - drizzle-orm: 0.44.2 - drizzle-kit: 0.31.1 I'll give an example directly from my schema. In my schema I have the table movementTypes and a join table that connects it with another table called characters.
// schema.ts
export const movementTypes = sqliteTable('movementTypes', {
  id: int().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
  label: text().notNull()
});

// Generated type for movementTypes as returned via InferSelectModel<typeof movementTypes>
// {
//   id: number;
//   name: string;
//   label: string;
// }

export const characterMovementTypes = sqliteTable('characterMovementTypes', {
  id: int().primaryKey({ autoIncrement: true }),
  movementTypeId: int()
    .notNull()
    .references(() => movementTypes.id),
  characterId: int()
    .notNull()
    .references(() => characters.id),
  speed: int().notNull()
});

// Generated type for movementTypes as returned via InferSelectModel<typeof characterMovementTypes>
// { [x: string]: any }
// schema.ts
export const movementTypes = sqliteTable('movementTypes', {
  id: int().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
  label: text().notNull()
});

// Generated type for movementTypes as returned via InferSelectModel<typeof movementTypes>
// {
//   id: number;
//   name: string;
//   label: string;
// }

export const characterMovementTypes = sqliteTable('characterMovementTypes', {
  id: int().primaryKey({ autoIncrement: true }),
  movementTypeId: int()
    .notNull()
    .references(() => movementTypes.id),
  characterId: int()
    .notNull()
    .references(() => characters.id),
  speed: int().notNull()
});

// Generated type for movementTypes as returned via InferSelectModel<typeof characterMovementTypes>
// { [x: string]: any }
Even if I try to specify the type of the column with the $type<>() method, I'm still not getting the correct type returned for the table's model. Is there a way to get this to generate correctly? I'd be happy with a solution that requires passing types as args or generated by the drizzle api. Thanks in advance for any help!
2 Replies
xhudaman
xhudamanOP•2mo ago
Since no answer's been given yet, I thought I'd update with what I found when adding a new join table so others may find the solution. It's buried in the docs in the Foreign Key constraint docs. In the 2nd example for self references the anonymous function passed as a callback to the references call is typed with the AnySQLiteColumn type. This seems to make the types generate correctly, as far as I can tell. An updated example of my new model is as follows: 
// schema.ts
export const movementTypes = sqliteTable('movementTypes', {
  id: int().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
  label: text().notNull()
});

// Generated type for movementTypes as returned via InferSelectModel<typeof movementTypes>
// {
//   id: number;
//   name: string;
//   label: string;
// }

export const characterMovementTypes = sqliteTable('characterMovementTypes', {
  id: int().primaryKey({ autoIncrement: true }),
  movementTypeId: int()
    .notNull()
    .references((): AnySQLiteColumn => movementTypes.id),
  characterId: int()
    .notNull()
    .references((): AnySQLiteColumn => characters.id),
  speed: int().notNull()
});

// Generated type for movementTypes as returned via InferSelectModel<typeof characterMovementTypes>
// {
//   id: number;
//   characterId: number;
//   movementTypeId: number;
//   speed: number;
// }
// schema.ts
export const movementTypes = sqliteTable('movementTypes', {
  id: int().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
  label: text().notNull()
});

// Generated type for movementTypes as returned via InferSelectModel<typeof movementTypes>
// {
//   id: number;
//   name: string;
//   label: string;
// }

export const characterMovementTypes = sqliteTable('characterMovementTypes', {
  id: int().primaryKey({ autoIncrement: true }),
  movementTypeId: int()
    .notNull()
    .references((): AnySQLiteColumn => movementTypes.id),
  characterId: int()
    .notNull()
    .references((): AnySQLiteColumn => characters.id),
  speed: int().notNull()
});

// Generated type for movementTypes as returned via InferSelectModel<typeof characterMovementTypes>
// {
//   id: number;
//   characterId: number;
//   movementTypeId: number;
//   speed: number;
// }
Drizzle ORM - Indexes & Constraints
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
xhudaman
xhudamanOP•2mo ago
It did require removing the explicit foreign key constraints, however. To demonstrate this, here is an example of both with and without the explicit foreign key constraints and the resulting types generated for both examples. 
// schema.ts

// With explicit constraints
export const characterMovementTypes = sqliteTable(
  'characterMovementTypes',
  {
    id: int().primaryKey({ autoIncrement: true }),
    movementTypeId: int()
      .notNull()
      .references((): AnySQLiteColumn => movementTypes.id),
    characterId: int()
      .notNull()
      .references((): AnySQLiteColumn => characters.id),
    speed: int().notNull()
  },
  table => [
    foreignKey({
      name: 'characterId',
      columns: [table.characterId],
      foreignColumns: [characters.id]
    }).onDelete('cascade'),
    foreignKey({
      name: 'movementTypeId',
      columns: [table.movementTypeId],
      foreignColumns: [movementTypes.id]
    }).onDelete('cascade')
  ]
);
// Generated type for movementTypes as returned via InferSelectModel<typeof characterMovementTypes>
// { [x: string]: any }

// Without explicit constraints
export const characterMovementTypes = sqliteTable('characterMovementTypes', {
  id: int().primaryKey({ autoIncrement: true }),
  movementTypeId: int()
    .notNull()
    .references((): AnySQLiteColumn => movementTypes.id),
  characterId: int()
    .notNull()
    .references((): AnySQLiteColumn => characters.id),
  speed: int().notNull()
});
// Generated type for movementTypes as returned via InferSelectModel<typeof characterMovementTypes>
// {
//   id: number;
//   characterId: number;
//   movementTypeId: number;
//   speed: number;
// }
// schema.ts

// With explicit constraints
export const characterMovementTypes = sqliteTable(
  'characterMovementTypes',
  {
    id: int().primaryKey({ autoIncrement: true }),
    movementTypeId: int()
      .notNull()
      .references((): AnySQLiteColumn => movementTypes.id),
    characterId: int()
      .notNull()
      .references((): AnySQLiteColumn => characters.id),
    speed: int().notNull()
  },
  table => [
    foreignKey({
      name: 'characterId',
      columns: [table.characterId],
      foreignColumns: [characters.id]
    }).onDelete('cascade'),
    foreignKey({
      name: 'movementTypeId',
      columns: [table.movementTypeId],
      foreignColumns: [movementTypes.id]
    }).onDelete('cascade')
  ]
);
// Generated type for movementTypes as returned via InferSelectModel<typeof characterMovementTypes>
// { [x: string]: any }

// Without explicit constraints
export const characterMovementTypes = sqliteTable('characterMovementTypes', {
  id: int().primaryKey({ autoIncrement: true }),
  movementTypeId: int()
    .notNull()
    .references((): AnySQLiteColumn => movementTypes.id),
  characterId: int()
    .notNull()
    .references((): AnySQLiteColumn => characters.id),
  speed: int().notNull()
});
// Generated type for movementTypes as returned via InferSelectModel<typeof characterMovementTypes>
// {
//   id: number;
//   characterId: number;
//   movementTypeId: number;
//   speed: number;
// }
This stuff is never explicitly called out in the docs, which I think would be a huge improvement for them. It's also not mentioned in the section pertaining to relations as all of those examples are given with PGSql examples only.

Did you find this page helpful?