db: Rework index config with generated index names (#10589)

* feat: add indexes array config with name gen

* fix: add _idx suffix, remove name from output

* feat(test): new index config

* chore: remove unused type

* chore: changeset

* chore: add sort() for consistent names

* feat(test): consistent column ordering

* feat(test): ensure no queries when migrating legacy to new

6 files changed, 344 insertions, 68 deletions

diff --git a/.changeset/blue-ghosts-rule.md b/.changeset/blue-ghosts-rule.md
new file mode 100644
index 000000000..ea06089c6
--- /dev/null
+++ b/.changeset/blue-ghosts-rule.md
@@ -0,0 +1,31 @@
+---
+"@astrojs/db": patch
+---
+
+Update the table indexes configuration to allow generated index names. The `indexes` object syntax is now deprecated in favor of an array.
+
+## Migration
+
+You can update your `indexes` configuration object to an array like so:
+
+```diff
+import { defineDb, defineTable, column } from 'astro:db';
+
+const Comment = defineTable({
+  columns: {
+    postId: column.number(),
+    author: column.text(),
+    body: column.text(),
+  },
+- indexes: {
+-   postIdIdx: { on: 'postId' },
+-   authorPostIdIdx: { on: ['author, postId'], unique: true },
+- },
++ indexes: [
++   { on: 'postId' /* 'name' is optional */ },
++   { on: ['author, postId'], unique: true },
++ ]
+})
+```
+
+This example will generate indexes with the names `Comment_postId_idx` and `Comment_author_postId_idx`, respectively. You can specify a name manually by adding the `name` attribute to a given object. This name will be **global,** so ensure index names do not conflict between tables.
diff --git a/packages/db/src/core/cli/migration-queries.ts b/packages/db/src/core/cli/migration-queries.ts
index 89359bd9b..dea731994 100644
--- a/packages/db/src/core/cli/migration-queries.ts
+++ b/packages/db/src/core/cli/migration-queries.ts
@@ -25,13 +25,13 @@ import {
 	type DBColumns,
 	type DBConfig,
 	type DBSnapshot,
-	type DBTable,
-	type DBTables,
+	type ResolvedDBTables,
 	type DateColumn,
-	type Indexes,
 	type JsonColumn,
 	type NumberColumn,
+	type ResolvedDBTable,
 	type TextColumn,
+	type ResolvedIndexes,
 } from '../types.js';
 import { type Result, getRemoteDatabaseUrl } from '../utils.js';
 
@@ -112,8 +112,8 @@ export async function getTableChangeQueries({
 	newTable,
 }: {
 	tableName: string;
-	oldTable: DBTable;
-	newTable: DBTable;
+	oldTable: ResolvedDBTable;
+	newTable: ResolvedDBTable;
 }): Promise<{ queries: string[]; confirmations: string[] }> {
 	const queries: string[] = [];
 	const confirmations: string[] = [];
@@ -187,8 +187,8 @@ function getChangeIndexQueries({
 	newIndexes = {},
 }: {
 	tableName: string;
-	oldIndexes?: Indexes;
-	newIndexes?: Indexes;
+	oldIndexes?: ResolvedIndexes;
+	newIndexes?: ResolvedIndexes;
 }) {
 	const added = getAdded(oldIndexes, newIndexes);
 	const dropped = getDropped(oldIndexes, newIndexes);
@@ -206,16 +206,16 @@ function getChangeIndexQueries({
 	return queries;
 }
 
-function getAddedTables(oldTables: DBSnapshot, newTables: DBSnapshot): DBTables {
-	const added: DBTables = {};
+function getAddedTables(oldTables: DBSnapshot, newTables: DBSnapshot): ResolvedDBTables {
+	const added: ResolvedDBTables = {};
 	for (const [key, newTable] of Object.entries(newTables.schema)) {
 		if (!(key in oldTables.schema)) added[key] = newTable;
 	}
 	return added;
 }
 
-function getDroppedTables(oldTables: DBSnapshot, newTables: DBSnapshot): DBTables {
-	const dropped: DBTables = {};
+function getDroppedTables(oldTables: DBSnapshot, newTables: DBSnapshot): ResolvedDBTables {
+	const dropped: ResolvedDBTables = {};
 	for (const [key, oldTable] of Object.entries(oldTables.schema)) {
 		if (!(key in newTables.schema)) dropped[key] = oldTable;
 	}
@@ -261,7 +261,7 @@ function getRecreateTableQueries({
 	migrateHiddenPrimaryKey,
 }: {
 	tableName: string;
-	newTable: DBTable;
+	newTable: ResolvedDBTable;
 	added: Record<string, DBColumn>;
 	hasDataLoss: boolean;
 	migrateHiddenPrimaryKey: boolean;
diff --git a/packages/db/src/core/schemas.ts b/packages/db/src/core/schemas.ts
index 7b4e7edb1..4dff9039a 100644
--- a/packages/db/src/core/schemas.ts
+++ b/packages/db/src/core/schemas.ts
@@ -4,6 +4,7 @@ import { type ZodTypeDef, z } from 'zod';
 import { SERIALIZED_SQL_KEY, type SerializedSQL } from '../runtime/types.js';
 import { errorMap } from './integration/error-map.js';
 import type { NumberColumn, TextColumn } from './types.js';
+import { mapObject } from './utils.js';
 
 export type MaybeArray<T> = T | T[];
 
@@ -156,11 +157,6 @@ export const referenceableColumnSchema = z.union([textColumnSchema, numberColumn
 
 export const columnsSchema = z.record(columnSchema);
 
-export const indexSchema = z.object({
-	on: z.string().or(z.array(z.string())),
-	unique: z.boolean().optional(),
-});
-
 type ForeignKeysInput = {
 	columns: MaybeArray<string>;
 	references: () => MaybeArray<Omit<z.input<typeof referenceableColumnSchema>, 'references'>>;
@@ -179,9 +175,23 @@ const foreignKeysSchema: z.ZodType<ForeignKeysOutput, ZodTypeDef, ForeignKeysInp
 		.transform((fn) => fn()),
 });
 
+export const resolvedIndexSchema = z.object({
+	on: z.string().or(z.array(z.string())),
+	unique: z.boolean().optional(),
+});
+/** @deprecated */
+const legacyIndexesSchema = z.record(resolvedIndexSchema);
+
+export const indexSchema = z.object({
+	on: z.string().or(z.array(z.string())),
+	unique: z.boolean().optional(),
+	name: z.string().optional(),
+});
+const indexesSchema = z.array(indexSchema);
+
 export const tableSchema = z.object({
 	columns: columnsSchema,
-	indexes: z.record(indexSchema).optional(),
+	indexes: indexesSchema.or(legacyIndexesSchema).optional(),
 	foreignKeys: z.array(foreignKeysSchema).optional(),
 	deprecated: z.boolean().optional().default(false),
 });
@@ -192,6 +202,7 @@ export const tablesSchema = z.preprocess((rawTables) => {
 	for (const [tableName, table] of Object.entries(tables)) {
 		// Append table and column names to columns.
 		// Used to track table info for references.
+		table.getName = () => tableName;
 		const { columns } = z.object({ columns: z.record(z.any()) }).parse(table, { errorMap });
 		for (const [columnName, column] of Object.entries(columns)) {
 			column.schema.name = columnName;
@@ -201,6 +212,34 @@ export const tablesSchema = z.preprocess((rawTables) => {
 	return rawTables;
 }, z.record(tableSchema));
 
-export const dbConfigSchema = z.object({
-	tables: tablesSchema.optional(),
-});
+export const dbConfigSchema = z
+	.object({
+		tables: tablesSchema.optional(),
+	})
+	.transform(({ tables = {}, ...config }) => {
+		return {
+			...config,
+			tables: mapObject(tables, (tableName, table) => {
+				const { indexes = {} } = table;
+				if (!Array.isArray(indexes)) {
+					return { ...table, indexes };
+				}
+				const resolvedIndexes: Record<string, z.infer<typeof resolvedIndexSchema>> = {};
+				for (const index of indexes) {
+					if (index.name) {
+						const { name, ...rest } = index;
+						resolvedIndexes[index.name] = rest;
+						continue;
+					}
+					// Sort index columns to ensure consistent index names
+					const indexOn = Array.isArray(index.on) ? index.on.sort().join('_') : index.on;
+					const name = tableName + '_' + indexOn + '_idx';
+					resolvedIndexes[name] = index;
+				}
+				return {
+					...table,
+					indexes: resolvedIndexes,
+				};
+			}),
+		};
+	});
diff --git a/packages/db/src/core/types.ts b/packages/db/src/core/types.ts
index 26c316cd7..6c7480086 100644
--- a/packages/db/src/core/types.ts
+++ b/packages/db/src/core/types.ts
@@ -8,6 +8,7 @@ import type {
 	dateColumnSchema,
 	dbConfigSchema,
 	indexSchema,
+	resolvedIndexSchema,
 	jsonColumnSchema,
 	numberColumnOptsSchema,
 	numberColumnSchema,
@@ -17,8 +18,7 @@ import type {
 	textColumnSchema,
 } from './schemas.js';
 
-export type Indexes = Record<string, z.infer<typeof indexSchema>>;
-
+export type ResolvedIndexes = z.output<typeof dbConfigSchema>['tables'][string]['indexes'];
 export type BooleanColumn = z.infer<typeof booleanColumnSchema>;
 export type BooleanColumnInput = z.input<typeof booleanColumnSchema>;
 export type NumberColumn = z.infer<typeof numberColumnSchema>;
@@ -47,8 +47,10 @@ export type DBColumnInput =
 export type DBColumns = z.infer<typeof columnsSchema>;
 export type DBTable = z.infer<typeof tableSchema>;
 export type DBTables = Record<string, DBTable>;
+export type ResolvedDBTables = z.output<typeof dbConfigSchema>['tables'];
+export type ResolvedDBTable = z.output<typeof dbConfigSchema>['tables'][string];
 export type DBSnapshot = {
-	schema: Record<string, DBTable>;
+	schema: Record<string, ResolvedDBTable>;
 	version: string;
 };
 
@@ -67,7 +69,7 @@ export interface TableConfig<TColumns extends ColumnsConfig = ColumnsConfig>
 		columns: MaybeArray<Extract<keyof TColumns, string>>;
 		references: () => MaybeArray<z.input<typeof referenceableColumnSchema>>;
 	}>;
-	indexes?: Record<string, IndexConfig<TColumns>>;
+	indexes?: Array<IndexConfig<TColumns>> | Record<string, LegacyIndexConfig<TColumns>>;
 	deprecated?: boolean;
 }
 
@@ -75,6 +77,12 @@ interface IndexConfig<TColumns extends ColumnsConfig> extends z.input<typeof ind
 	on: MaybeArray<Extract<keyof TColumns, string>>;
 }
 
+/** @deprecated */
+interface LegacyIndexConfig<TColumns extends ColumnsConfig>
+	extends z.input<typeof resolvedIndexSchema> {
+	on: MaybeArray<Extract<keyof TColumns, string>>;
+}
+
 // We cannot use `Omit<NumberColumn | TextColumn, 'type'>`,
 // since Omit collapses our union type on primary key.
 export type NumberColumnOpts = z.input<typeof numberColumnOptsSchema>;
diff --git a/packages/db/src/core/utils.ts b/packages/db/src/core/utils.ts
index 784f60aa7..ebc2547b3 100644
--- a/packages/db/src/core/utils.ts
+++ b/packages/db/src/core/utils.ts
@@ -28,3 +28,16 @@ export function defineDbIntegration(integration: AstroDbIntegration): AstroInteg
 }
 
 export type Result<T> = { success: true; data: T } | { success: false; data: unknown };
+
+/**
+ * Map an object's values to a new set of values
+ * while preserving types.
+ */
+export function mapObject<T, U = T>(
+	item: Record<string, T>,
+	callback: (key: string, value: T) => U
+): Record<string, U> {
+	return Object.fromEntries(
+		Object.entries(item).map(([key, value]) => [key, callback(key, value)])
+	);
+}
diff --git a/packages/db/test/unit/index-queries.test.js b/packages/db/test/unit/index-queries.test.js
index b26815ecf..5af1b8489 100644
--- a/packages/db/test/unit/index-queries.test.js
+++ b/packages/db/test/unit/index-queries.test.js
@@ -1,7 +1,7 @@
 import { expect } from 'chai';
 import { describe, it } from 'mocha';
 import { getTableChangeQueries } from '../../dist/core/cli/migration-queries.js';
-import { tableSchema } from '../../dist/core/schemas.js';
+import { dbConfigSchema, tableSchema } from '../../dist/core/schemas.js';
 import { column } from '../../dist/runtime/config.js';
 
 const userInitial = tableSchema.parse({
@@ -16,20 +16,121 @@ const userInitial = tableSchema.parse({
 });
 
 describe('index queries', () => {
+	it('generates index names by table and combined column names', async () => {
+		// Use dbConfigSchema.parse to resolve generated idx names
+		const dbConfig = dbConfigSchema.parse({
+			tables: {
+				oldTable: userInitial,
+				newTable: {
+					...userInitial,
+					indexes: [
+						{ on: ['name', 'age'], unique: false },
+						{ on: ['email'], unique: true },
+					],
+				},
+			},
+		});
+
+		const { queries } = await getTableChangeQueries({
+			tableName: 'user',
+			oldTable: dbConfig.tables.oldTable,
+			newTable: dbConfig.tables.newTable,
+		});
+
+		expect(queries).to.deep.equal([
+			'CREATE INDEX "newTable_age_name_idx" ON "user" ("age", "name")',
+			'CREATE UNIQUE INDEX "newTable_email_idx" ON "user" ("email")',
+		]);
+	});
+
+	it('generates index names with consistent column ordering', async () => {
+		const initial = dbConfigSchema.parse({
+			tables: {
+				user: {
+					...userInitial,
+					indexes: [
+						{ on: ['email'], unique: true },
+						{ on: ['name', 'age'], unique: false },
+					],
+				},
+			},
+		});
+
+		const final = dbConfigSchema.parse({
+			tables: {
+				user: {
+					...userInitial,
+					indexes: [
+						// flip columns
+						{ on: ['age', 'name'], unique: false },
+						// flip index order
+						{ on: ['email'], unique: true },
+					],
+				},
+			},
+		});
+
+		const { queries } = await getTableChangeQueries({
+			tableName: 'user',
+			oldTable: initial.tables.user,
+			newTable: final.tables.user,
+		});
+
+		expect(queries).to.be.empty;
+	});
+
+	it('does not trigger queries when changing from legacy to new format', async () => {
+		const initial = dbConfigSchema.parse({
+			tables: {
+				user: {
+					...userInitial,
+					indexes: {
+						emailIdx: { on: ['email'], unique: true },
+						nameAgeIdx: { on: ['name', 'age'], unique: false },
+					},
+				},
+			},
+		});
+
+		const final = dbConfigSchema.parse({
+			tables: {
+				user: {
+					...userInitial,
+					indexes: [
+						{ on: ['email'], unique: true, name: 'emailIdx' },
+						{ on: ['name', 'age'], unique: false, name: 'nameAgeIdx' },
+					],
+				},
+			},
+		});
+
+		const { queries } = await getTableChangeQueries({
+			tableName: 'user',
+			oldTable: initial.tables.user,
+			newTable: final.tables.user,
+		});
+
+		expect(queries).to.be.empty;
+	});
+
 	it('adds indexes', async () => {
-		/** @type {import('../../dist/types.js').DBTable} */
-		const userFinal = {
-			...userInitial,
-			indexes: {
-				nameIdx: { on: ['name'], unique: false },
-				emailIdx: { on: ['email'], unique: true },
+		const dbConfig = dbConfigSchema.parse({
+			tables: {
+				oldTable: userInitial,
+				newTable: {
+					...userInitial,
+					indexes: [
+						{ on: ['name'], unique: false, name: 'nameIdx' },
+						{ on: ['email'], unique: true, name: 'emailIdx' },
+					],
+				},
 			},
-		};
+		});
 
 		const { queries } = await getTableChangeQueries({
 			tableName: 'user',
-			oldTable: userInitial,
-			newTable: userFinal,
+			oldTable: dbConfig.tables.oldTable,
+			newTable: dbConfig.tables.newTable,
 		});
 
 		expect(queries).to.deep.equal([
@@ -39,53 +140,55 @@ describe('index queries', () => {
 	});
 
 	it('drops indexes', async () => {
-		/** @type {import('../../dist/types.js').DBTable} */
-		const initial = {
-			...userInitial,
-			indexes: {
-				nameIdx: { on: ['name'], unique: false },
-				emailIdx: { on: ['email'], unique: true },
+		const dbConfig = dbConfigSchema.parse({
+			tables: {
+				oldTable: {
+					...userInitial,
+					indexes: [
+						{ on: ['name'], unique: false, name: 'nameIdx' },
+						{ on: ['email'], unique: true, name: 'emailIdx' },
+					],
+				},
+				newTable: {
+					...userInitial,
+					indexes: {},
+				},
 			},
-		};
-
-		/** @type {import('../../dist/types.js').DBTable} */
-		const final = {
-			...userInitial,
-			indexes: {},
-		};
+		});
 
 		const { queries } = await getTableChangeQueries({
 			tableName: 'user',
-			oldTable: initial,
-			newTable: final,
+			oldTable: dbConfig.tables.oldTable,
+			newTable: dbConfig.tables.newTable,
 		});
 
 		expect(queries).to.deep.equal(['DROP INDEX "nameIdx"', 'DROP INDEX "emailIdx"']);
 	});
 
 	it('drops and recreates modified indexes', async () => {
-		/** @type {import('../../dist/types.js').DBTable} */
-		const initial = {
-			...userInitial,
-			indexes: {
-				nameIdx: { on: ['name'], unique: false },
-				emailIdx: { on: ['email'], unique: true },
-			},
-		};
-
-		/** @type {import('../../dist/types.js').DBTable} */
-		const final = {
-			...userInitial,
-			indexes: {
-				nameIdx: { on: ['name'], unique: true },
-				emailIdx: { on: ['email'] },
+		const dbConfig = dbConfigSchema.parse({
+			tables: {
+				oldTable: {
+					...userInitial,
+					indexes: [
+						{ unique: false, on: ['name'], name: 'nameIdx' },
+						{ unique: true, on: ['email'], name: 'emailIdx' },
+					],
+				},
+				newTable: {
+					...userInitial,
+					indexes: [
+						{ unique: true, on: ['name'], name: 'nameIdx' },
+						{ on: ['email'], name: 'emailIdx' },
+					],
+				},
 			},
-		};
+		});
 
 		const { queries } = await getTableChangeQueries({
 			tableName: 'user',
-			oldTable: initial,
-			newTable: final,
+			oldTable: dbConfig.tables.oldTable,
+			newTable: dbConfig.tables.newTable,
 		});
 
 		expect(queries).to.deep.equal([
@@ -95,4 +198,86 @@ describe('index queries', () => {
 			'CREATE INDEX "emailIdx" ON "user" ("email")',
 		]);
 	});
+
+	describe('legacy object config', () => {
+		it('adds indexes', async () => {
+			/** @type {import('../../dist/core/types.js').DBTable} */
+			const userFinal = {
+				...userInitial,
+				indexes: {
+					nameIdx: { on: ['name'], unique: false },
+					emailIdx: { on: ['email'], unique: true },
+				},
+			};
+
+			const { queries } = await getTableChangeQueries({
+				tableName: 'user',
+				oldTable: userInitial,
+				newTable: userFinal,
+			});
+
+			expect(queries).to.deep.equal([
+				'CREATE INDEX "nameIdx" ON "user" ("name")',
+				'CREATE UNIQUE INDEX "emailIdx" ON "user" ("email")',
+			]);
+		});
+
+		it('drops indexes', async () => {
+			/** @type {import('../../dist/core/types.js').DBTable} */
+			const initial = {
+				...userInitial,
+				indexes: {
+					nameIdx: { on: ['name'], unique: false },
+					emailIdx: { on: ['email'], unique: true },
+				},
+			};
+
+			/** @type {import('../../dist/core/types.js').DBTable} */
+			const final = {
+				...userInitial,
+				indexes: {},
+			};
+
+			const { queries } = await getTableChangeQueries({
+				tableName: 'user',
+				oldTable: initial,
+				newTable: final,
+			});
+
+			expect(queries).to.deep.equal(['DROP INDEX "nameIdx"', 'DROP INDEX "emailIdx"']);
+		});
+
+		it('drops and recreates modified indexes', async () => {
+			/** @type {import('../../dist/core/types.js').DBTable} */
+			const initial = {
+				...userInitial,
+				indexes: {
+					nameIdx: { on: ['name'], unique: false },
+					emailIdx: { on: ['email'], unique: true },
+				},
+			};
+
+			/** @type {import('../../dist/core/types.js').DBTable} */
+			const final = {
+				...userInitial,
+				indexes: {
+					nameIdx: { on: ['name'], unique: true },
+					emailIdx: { on: ['email'] },
+				},
+			};
+
+			const { queries } = await getTableChangeQueries({
+				tableName: 'user',
+				oldTable: initial,
+				newTable: final,
+			});
+
+			expect(queries).to.deep.equal([
+				'DROP INDEX "nameIdx"',
+				'DROP INDEX "emailIdx"',
+				'CREATE UNIQUE INDEX "nameIdx" ON "user" ("name")',
+				'CREATE INDEX "emailIdx" ON "user" ("email")',
+			]);
+		});
+	});
 });