[FEATURE]: SQLite `.primaryKey().notNull()`

[hyunbinseo]

Describe what you want

Drizzle Kit converts .primaryKey() to PRIMARY KEY NOT NULL.

export const userTable = sqliteTable('user', {
  id: integer('id').primaryKey(),

CREATE TABLE `user` (
  `id` integer PRIMARY KEY NOT NULL,

This is not necessary. integer PRIMARY KEY must be non-NULL.

The value of an INTEGER PRIMARY KEY column must always be a non-NULL integer because the INTEGER PRIMARY KEY is an alias for the ROWID. If you try to insert a NULL into an INTEGER PRIMARY KEY column, SQLite automatically convert the NULL into a unique integer.

https://www.sqlite.org/quirks.html

---

However, there are cases where explicit .notNull() is necessary.

For example, in a one-to-one relationship between two tables:

export const userTable = sqliteTable('user', {
  id: integer('id').primaryKey(),
});

export const profileTable = sqliteTable('profile', {
  userId: integer('user_id')
    .primaryKey()
    .notNull() // This has no effect.
    .references(() => userTable.id),
});

Since user_id is a primary-key that references another table, a value must be provided on insertion.

However, this cannot be enforced currently.

// This does not cause a TypeScript error.
await db.insert(profileTable).values({});

[hyunbinseo]

Since primary keys are not required in SQLite,

The PRIMARY KEY is optional for ordinary tables but is required for WITHOUT ROWID tables.

https://www.sqlite.org/lang_createtable.html#the_primary_key

Using .notNull().unique() could be a workaround as-of-now.

export const profileTable = sqliteTable('profile', {
  userId: integer('user_id')
    .notNull()
    .unique()
    .references(() => userTable.id),

[AndriiSherman]

thanks, will fix this behavior

[hyunbinseo]

Possible workaround using custom type:

import { customType, sqliteTable, text } from 'drizzle-orm/sqlite-core';
import { db } from '../client.js';

const primaryKeyRequired = customType<{
  data: number;
  driverData: number;
  notNull: true;
  default: false; // default value is required
}>({ dataType: () => 'integer PRIMARY KEY NOT NULL' });

export const profileTable = sqliteTable('user', {
  userId: primaryKeyRequired('user_id'),
  name: text('name').notNull()
});

// Error: Property userId is missing
await db.insert(profileTable).values({ name: 'Hyunbin' });

await db.insert(profileTable).values({ name: 'Hyunbin', userId: 1 });

[hyunbinseo]

Note: This only happens in integer().primaryKey().

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

export const tableA = sqliteTable('name', {
  integerId: integer('id').primaryKey()
});

// { integerId?: number | undefined };
type InsertA = typeof tableA.$inferInsert;

export const tableB = sqliteTable('name', {
  textId: text('id').primaryKey()
});

// { textId: string };
type InsertB = typeof tableB.$inferInsert;

export const tableC = sqliteTable('name', {
  textId: text('id').primaryKey().$default(randomUUID)
});

// { textId?: string | undefined; };
type InsertC = typeof tableC.$inferInsert;

For one-to-one relationship between two tables, consider using

• text primary keys
• the workaround above