[FEATURE]: Allow for non sequential primary key for id of migrationsTable

[aydrian]

Describe what you want

For at least the pg-core dialect, it would be great if we had the option to set the id column type to something non sequential like a UUID. I have been trying Drizzle with CockroachDB and everything seems to be working okay but it does complain about the SERIAL id type on the migrationsTable. Sequential primary keys in distributed databases can cause hot spotting. CockroachDB recommends using a UUID with a default value of gen_random_uuid(). I don't think this will cause a problem in the long run but it would remove the warning when running a migration.

[AndriiSherman]

You can use uuid field in pg-core, I guess it's just missing in docs, will fix it!

import { pgTable, text, uuid } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
    id: uuid("uuid1").defaultRandom(),
    name: text("name"),
});

Would that work for you?

Also you can use .default for any sql defaults, so you can write like this:

import { pgTable, text, uuid } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
    id: uuid("uuid1").default(sql`gen_random_uuid()`),
    name: text("name"),
});

[AndriiSherman]

We have an example here: https://orm.drizzle.team/docs/column-types/pg#default-value
Just never made it in a list of types, just created an issue in docs repo: drizzle-team/drizzle-orm-docs#120

[aydrian]

I'm not referring to the tables I create in my schema, but rather the table Drizzle creates to hold information about migrations. It uses a serial type for the primary key.

[AndriiSherman]

makes sense, assigning to myself

[aydrian]

Just ran into another issue using push/migration after I added a foreign key to my schema. Looks like we don't yet support anonymous code blocks. We do have a ticket for it.

DO $$ BEGIN
 ALTER TABLE "count_downs" ADD CONSTRAINT "count_downs_id_lists_id_fk" FOREIGN KEY ("id") REFERENCES "lists"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

[FlorentCollin]

The new Aurora DSQL also does not support serial or sequences in general, so this feature will be a great help for this database as well.

[yamatatsu]

In the code below, the id column is not used in select and insert, which means all queries for the table.

drizzle-orm/drizzle-orm/src/pg-core/dialect.ts

Lines 73 to 112 in 866c257

	async migrate(migrations: MigrationMeta[], session: PgSession, config: string | MigrationConfig): Promise<void> {
	const migrationsTable = typeof config === 'string'
	? '__drizzle_migrations'
	: config.migrationsTable ?? '__drizzle_migrations';
	const migrationsSchema = typeof config === 'string' ? 'drizzle' : config.migrationsSchema ?? 'drizzle';
	const migrationTableCreate = sql`
	CREATE TABLE IF NOT EXISTS ${sql.identifier(migrationsSchema)}.${sql.identifier(migrationsTable)} (
	id SERIAL PRIMARY KEY,
	hash text NOT NULL,
	created_at bigint
	)
	`;
	await session.execute(sql`CREATE SCHEMA IF NOT EXISTS ${sql.identifier(migrationsSchema)}`);
	await session.execute(migrationTableCreate);
	const dbMigrations = await session.all<{ id: number; hash: string; created_at: string }>(
	sql`select id, hash, created_at from ${sql.identifier(migrationsSchema)}.${
	sql.identifier(migrationsTable)
	} order by created_at desc limit 1`,
	);
	const lastDbMigration = dbMigrations[0];
	await session.transaction(async (tx) => {
	for await (const migration of migrations) {
	if (
	!lastDbMigration
	|| Number(lastDbMigration.created_at) < migration.folderMillis
	) {
	for (const stmt of migration.sql) {
	await tx.execute(sql.raw(stmt));
	}
	await tx.execute(
	sql`insert into ${sql.identifier(migrationsSchema)}.${
	sql.identifier(migrationsTable)
	} ("hash", "created_at") values(${migration.hash}, ${migration.folderMillis})`,
	);
	}
	}
	});
	}

Therefore, it seems we could swap the definition of the column to UUID PRIMARY KEY DEFAULT gen_random_uuid() from SERIAL PRIMARY KEY when the specified config is passed, and we would get the table without serial, correct?