export DB types (#10374)

* export DB types

* refactor: move schemas to separate file

* chore: changeset

* chore: add typesVersions

---------

Co-authored-by: bholmesdev <hey@bholmes.dev>

9 files changed, 236 insertions, 206 deletions

diff --git a/.changeset/silver-foxes-protect.md b/.changeset/silver-foxes-protect.md
new file mode 100644
index 000000000..a7191eec2
--- /dev/null
+++ b/.changeset/silver-foxes-protect.md
@@ -0,0 +1,5 @@
+---
+"@astrojs/db": patch
+---
+
+Expose DB utility types from @astrojs/db/types
diff --git a/packages/db/package.json b/packages/db/package.json
index 071e51247..a99379d34 100644
--- a/packages/db/package.json
+++ b/packages/db/package.json
@@ -23,6 +23,10 @@
     "./dist/runtime/config.js": {
       "import": "./dist/runtime/config.js"
     },
+    "./types": {
+      "types": "./dist/core/types.d.ts",
+      "import": "./dist/core/types.js"
+    },
     "./package.json": "./package.json"
   },
   "typesVersions": {
@@ -30,6 +34,9 @@
       ".": [
         "./index.d.ts"
       ],
+      "types": [
+        "./dist/types.d.ts"
+      ],
       "utils": [
         "./dist/utils.d.ts"
       ],
diff --git a/packages/db/src/core/cli/migration-queries.ts b/packages/db/src/core/cli/migration-queries.ts
index 3ff80d009..3d7793597 100644
--- a/packages/db/src/core/cli/migration-queries.ts
+++ b/packages/db/src/core/cli/migration-queries.ts
@@ -30,9 +30,9 @@ import {
 	type JsonColumn,
 	type NumberColumn,
 	type TextColumn,
-	columnSchema,
 } from '../types.js';
 import { getRemoteDatabaseUrl } from '../utils.js';
+import { columnSchema } from '../schemas.js';
 
 const sqlite = new SQLiteAsyncDialect();
 const genTempTableName = customAlphabet('abcdefghijklmnopqrstuvwxyz', 10);
diff --git a/packages/db/src/core/load-file.ts b/packages/db/src/core/load-file.ts
index 66a5e27c7..dca49be33 100644
--- a/packages/db/src/core/load-file.ts
+++ b/packages/db/src/core/load-file.ts
@@ -8,7 +8,8 @@ import { CONFIG_FILE_NAMES, VIRTUAL_MODULE_ID } from './consts.js';
 import { INTEGRATION_TABLE_CONFLICT_ERROR } from './errors.js';
 import { errorMap } from './integration/error-map.js';
 import { getConfigVirtualModContents } from './integration/vite-plugin-db.js';
-import { type AstroDbIntegration, dbConfigSchema } from './types.js';
+import { type AstroDbIntegration } from './types.js';
+import { dbConfigSchema } from './schemas.js';
 import { getDbDirectoryUrl } from './utils.js';
 
 const isDbIntegration = (integration: AstroIntegration): integration is AstroDbIntegration =>
diff --git a/packages/db/src/core/schemas.ts b/packages/db/src/core/schemas.ts
new file mode 100644
index 000000000..1305016cd
--- /dev/null
+++ b/packages/db/src/core/schemas.ts
@@ -0,0 +1,206 @@
+import { SQL } from 'drizzle-orm';
+import { SQLiteAsyncDialect } from 'drizzle-orm/sqlite-core';
+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';
+
+export type MaybeArray<T> = T | T[];
+
+// Transform to serializable object for migration files
+const sqlite = new SQLiteAsyncDialect();
+
+const sqlSchema = z.instanceof(SQL<any>).transform(
+	(sqlObj): SerializedSQL => ({
+		[SERIALIZED_SQL_KEY]: true,
+		sql: sqlite.sqlToQuery(sqlObj).sql,
+	})
+);
+
+const baseColumnSchema = z.object({
+	label: z.string().optional(),
+	optional: z.boolean().optional().default(false),
+	unique: z.boolean().optional().default(false),
+	deprecated: z.boolean().optional().default(false),
+
+	// Defined when `defineReadableTable()` is called
+	name: z.string().optional(),
+	// TODO: rename to `tableName`. Breaking schema change
+	collection: z.string().optional(),
+});
+
+export const booleanColumnSchema = z.object({
+	type: z.literal('boolean'),
+	schema: baseColumnSchema.extend({
+		default: z.union([z.boolean(), sqlSchema]).optional(),
+	}),
+});
+
+const numberColumnBaseSchema = baseColumnSchema.omit({ optional: true }).and(
+	z.union([
+		z.object({
+			primaryKey: z.literal(false).optional().default(false),
+			optional: baseColumnSchema.shape.optional,
+			default: z.union([z.number(), sqlSchema]).optional(),
+		}),
+		z.object({
+			// `integer primary key` uses ROWID as the default value.
+			// `optional` and `default` do not have an effect,
+			// so disable these config options for primary keys.
+			primaryKey: z.literal(true),
+			optional: z.literal(false).optional(),
+			default: z.literal(undefined).optional(),
+		}),
+	])
+);
+
+export const numberColumnOptsSchema: z.ZodType<
+	z.infer<typeof numberColumnBaseSchema> & {
+		// ReferenceableColumn creates a circular type. Define ZodType to resolve.
+		references?: NumberColumn;
+	},
+	ZodTypeDef,
+	z.input<typeof numberColumnBaseSchema> & {
+		references?: () => z.input<typeof numberColumnSchema>;
+	}
+> = numberColumnBaseSchema.and(
+	z.object({
+		references: z
+			.function()
+			.returns(z.lazy(() => numberColumnSchema))
+			.optional()
+			.transform((fn) => fn?.()),
+	})
+);
+
+export const numberColumnSchema = z.object({
+	type: z.literal('number'),
+	schema: numberColumnOptsSchema,
+});
+
+const textColumnBaseSchema = baseColumnSchema
+	.omit({ optional: true })
+	.extend({
+		default: z.union([z.string(), sqlSchema]).optional(),
+		multiline: z.boolean().optional(),
+	})
+	.and(
+		z.union([
+			z.object({
+				primaryKey: z.literal(false).optional().default(false),
+				optional: baseColumnSchema.shape.optional,
+			}),
+			z.object({
+				// text primary key allows NULL values.
+				// NULL values bypass unique checks, which could
+				// lead to duplicate URLs per record in Astro Studio.
+				// disable `optional` for primary keys.
+				primaryKey: z.literal(true),
+				optional: z.literal(false).optional(),
+			}),
+		])
+	);
+
+export const textColumnOptsSchema: z.ZodType<
+	z.infer<typeof textColumnBaseSchema> & {
+		// ReferenceableColumn creates a circular type. Define ZodType to resolve.
+		references?: TextColumn;
+	},
+	ZodTypeDef,
+	z.input<typeof textColumnBaseSchema> & {
+		references?: () => z.input<typeof textColumnSchema>;
+	}
+> = textColumnBaseSchema.and(
+	z.object({
+		references: z
+			.function()
+			.returns(z.lazy(() => textColumnSchema))
+			.optional()
+			.transform((fn) => fn?.()),
+	})
+);
+
+export const textColumnSchema = z.object({
+	type: z.literal('text'),
+	schema: textColumnOptsSchema,
+});
+
+export const dateColumnSchema = z.object({
+	type: z.literal('date'),
+	schema: baseColumnSchema.extend({
+		default: z
+			.union([
+				sqlSchema,
+				// transform to ISO string for serialization
+				z.date().transform((d) => d.toISOString()),
+			])
+			.optional(),
+	}),
+});
+
+export const jsonColumnSchema = z.object({
+	type: z.literal('json'),
+	schema: baseColumnSchema.extend({
+		default: z.unknown().optional(),
+	}),
+});
+
+export const columnSchema = z.union([
+	booleanColumnSchema,
+	numberColumnSchema,
+	textColumnSchema,
+	dateColumnSchema,
+	jsonColumnSchema,
+]);
+export const referenceableColumnSchema = z.union([textColumnSchema, numberColumnSchema]);
+
+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'>>;
+};
+
+type ForeignKeysOutput = Omit<ForeignKeysInput, 'references'> & {
+	// reference fn called in `transform`. Ensures output is JSON serializable.
+	references: MaybeArray<Omit<z.output<typeof referenceableColumnSchema>, 'references'>>;
+};
+
+const foreignKeysSchema: z.ZodType<ForeignKeysOutput, ZodTypeDef, ForeignKeysInput> = z.object({
+	columns: z.string().or(z.array(z.string())),
+	references: z
+		.function()
+		.returns(z.lazy(() => referenceableColumnSchema.or(z.array(referenceableColumnSchema))))
+		.transform((fn) => fn()),
+});
+
+export const tableSchema = z.object({
+	columns: columnsSchema,
+	indexes: z.record(indexSchema).optional(),
+	foreignKeys: z.array(foreignKeysSchema).optional(),
+	deprecated: z.boolean().optional().default(false),
+});
+
+export const tablesSchema = z.preprocess((rawTables) => {
+	// Use `z.any()` to avoid breaking object references
+	const tables = z.record(z.any()).parse(rawTables, { errorMap });
+	for (const [tableName, table] of Object.entries(tables)) {
+		// Append table and column names to columns.
+		// Used to track table info for references.
+		const { columns } = z.object({ columns: z.record(z.any()) }).parse(table, { errorMap });
+		for (const [columnName, column] of Object.entries(columns)) {
+			column.schema.name = columnName;
+			column.schema.collection = tableName;
+		}
+	}
+	return rawTables;
+}, z.record(tableSchema));
+
+export const dbConfigSchema = z.object({
+	tables: tablesSchema.optional(),
+});
diff --git a/packages/db/src/core/types.ts b/packages/db/src/core/types.ts
index cff08c4a5..dc23ee509 100644
--- a/packages/db/src/core/types.ts
+++ b/packages/db/src/core/types.ts
@@ -1,209 +1,24 @@
 import type { AstroIntegration } from 'astro';
-import { SQL } from 'drizzle-orm';
-import { SQLiteAsyncDialect } from 'drizzle-orm/sqlite-core';
-import { type ZodTypeDef, z } from 'zod';
-import { SERIALIZED_SQL_KEY, type SerializedSQL } from '../runtime/types.js';
-import { errorMap } from './integration/error-map.js';
-
-export type MaybePromise<T> = T | Promise<T>;
-export type MaybeArray<T> = T | T[];
-
-// Transform to serializable object for migration files
-const sqlite = new SQLiteAsyncDialect();
-
-const sqlSchema = z.instanceof(SQL<any>).transform(
-	(sqlObj): SerializedSQL => ({
-		[SERIALIZED_SQL_KEY]: true,
-		sql: sqlite.sqlToQuery(sqlObj).sql,
-	})
-);
-
-const baseColumnSchema = z.object({
-	label: z.string().optional(),
-	optional: z.boolean().optional().default(false),
-	unique: z.boolean().optional().default(false),
-	deprecated: z.boolean().optional().default(false),
-
-	// Defined when `defineReadableTable()` is called
-	name: z.string().optional(),
-	// TODO: rename to `tableName`. Breaking schema change
-	collection: z.string().optional(),
-});
-
-const booleanColumnSchema = z.object({
-	type: z.literal('boolean'),
-	schema: baseColumnSchema.extend({
-		default: z.union([z.boolean(), sqlSchema]).optional(),
-	}),
-});
-
-const numberColumnBaseSchema = baseColumnSchema.omit({ optional: true }).and(
-	z.union([
-		z.object({
-			primaryKey: z.literal(false).optional().default(false),
-			optional: baseColumnSchema.shape.optional,
-			default: z.union([z.number(), sqlSchema]).optional(),
-		}),
-		z.object({
-			// `integer primary key` uses ROWID as the default value.
-			// `optional` and `default` do not have an effect,
-			// so disable these config options for primary keys.
-			primaryKey: z.literal(true),
-			optional: z.literal(false).optional(),
-			default: z.literal(undefined).optional(),
-		}),
-	])
-);
-
-const numberColumnOptsSchema: z.ZodType<
-	z.infer<typeof numberColumnBaseSchema> & {
-		// ReferenceableColumn creates a circular type. Define ZodType to resolve.
-		references?: NumberColumn;
-	},
-	ZodTypeDef,
-	z.input<typeof numberColumnBaseSchema> & {
-		references?: () => z.input<typeof numberColumnSchema>;
-	}
-> = numberColumnBaseSchema.and(
-	z.object({
-		references: z
-			.function()
-			.returns(z.lazy(() => numberColumnSchema))
-			.optional()
-			.transform((fn) => fn?.()),
-	})
-);
-
-const numberColumnSchema = z.object({
-	type: z.literal('number'),
-	schema: numberColumnOptsSchema,
-});
-
-const textColumnBaseSchema = baseColumnSchema
-	.omit({ optional: true })
-	.extend({
-		default: z.union([z.string(), sqlSchema]).optional(),
-		multiline: z.boolean().optional(),
-	})
-	.and(
-		z.union([
-			z.object({
-				primaryKey: z.literal(false).optional().default(false),
-				optional: baseColumnSchema.shape.optional,
-			}),
-			z.object({
-				// text primary key allows NULL values.
-				// NULL values bypass unique checks, which could
-				// lead to duplicate URLs per record in Astro Studio.
-				// disable `optional` for primary keys.
-				primaryKey: z.literal(true),
-				optional: z.literal(false).optional(),
-			}),
-		])
-	);
-
-const textColumnOptsSchema: z.ZodType<
-	z.infer<typeof textColumnBaseSchema> & {
-		// ReferenceableColumn creates a circular type. Define ZodType to resolve.
-		references?: TextColumn;
-	},
-	ZodTypeDef,
-	z.input<typeof textColumnBaseSchema> & {
-		references?: () => z.input<typeof textColumnSchema>;
-	}
-> = textColumnBaseSchema.and(
-	z.object({
-		references: z
-			.function()
-			.returns(z.lazy(() => textColumnSchema))
-			.optional()
-			.transform((fn) => fn?.()),
-	})
-);
-
-const textColumnSchema = z.object({
-	type: z.literal('text'),
-	schema: textColumnOptsSchema,
-});
-
-const dateColumnSchema = z.object({
-	type: z.literal('date'),
-	schema: baseColumnSchema.extend({
-		default: z
-			.union([
-				sqlSchema,
-				// transform to ISO string for serialization
-				z.date().transform((d) => d.toISOString()),
-			])
-			.optional(),
-	}),
-});
-
-const jsonColumnSchema = z.object({
-	type: z.literal('json'),
-	schema: baseColumnSchema.extend({
-		default: z.unknown().optional(),
-	}),
-});
-
-export const columnSchema = z.union([
+import type { z } from 'zod';
+import type {
 	booleanColumnSchema,
 	numberColumnSchema,
 	textColumnSchema,
 	dateColumnSchema,
 	jsonColumnSchema,
-]);
-export const referenceableColumnSchema = z.union([textColumnSchema, numberColumnSchema]);
-
-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'>>;
-};
-
-type ForeignKeysOutput = Omit<ForeignKeysInput, 'references'> & {
-	// reference fn called in `transform`. Ensures output is JSON serializable.
-	references: MaybeArray<Omit<z.output<typeof referenceableColumnSchema>, 'references'>>;
-};
-
-const foreignKeysSchema: z.ZodType<ForeignKeysOutput, ZodTypeDef, ForeignKeysInput> = z.object({
-	columns: z.string().or(z.array(z.string())),
-	references: z
-		.function()
-		.returns(z.lazy(() => referenceableColumnSchema.or(z.array(referenceableColumnSchema))))
-		.transform((fn) => fn()),
-});
+	columnSchema,
+	tableSchema,
+	referenceableColumnSchema,
+	indexSchema,
+	numberColumnOptsSchema,
+	textColumnOptsSchema,
+	columnsSchema,
+	MaybeArray,
+	dbConfigSchema,
+} from './schemas.js';
 
 export type Indexes = Record<string, z.infer<typeof indexSchema>>;
 
-export const tableSchema = z.object({
-	columns: columnsSchema,
-	indexes: z.record(indexSchema).optional(),
-	foreignKeys: z.array(foreignKeysSchema).optional(),
-	deprecated: z.boolean().optional().default(false),
-});
-
-export const tablesSchema = z.preprocess((rawTables) => {
-	// Use `z.any()` to avoid breaking object references
-	const tables = z.record(z.any()).parse(rawTables, { errorMap });
-	for (const [tableName, table] of Object.entries(tables)) {
-		// Append table and column names to columns.
-		// Used to track table info for references.
-		const { columns } = z.object({ columns: z.record(z.any()) }).parse(table, { errorMap });
-		for (const [columnName, column] of Object.entries(columns)) {
-			column.schema.name = columnName;
-			column.schema.collection = tableName;
-		}
-	}
-	return rawTables;
-}, z.record(tableSchema));
-
 export type BooleanColumn = z.infer<typeof booleanColumnSchema>;
 export type BooleanColumnInput = z.input<typeof booleanColumnSchema>;
 export type NumberColumn = z.infer<typeof numberColumnSchema>;
@@ -237,10 +52,6 @@ export type DBSnapshot = {
 	version: string;
 };
 
-export const dbConfigSchema = z.object({
-	tables: tablesSchema.optional(),
-});
-
 export type DBConfigInput = z.input<typeof dbConfigSchema>;
 export type DBConfig = z.infer<typeof dbConfigSchema>;
 
diff --git a/packages/db/test/unit/column-queries.test.js b/packages/db/test/unit/column-queries.test.js
index 8ba6552f8..c4b07a493 100644
--- a/packages/db/test/unit/column-queries.test.js
+++ b/packages/db/test/unit/column-queries.test.js
@@ -5,7 +5,7 @@ import {
 	getMigrationQueries,
 } from '../../dist/core/cli/migration-queries.js';
 import { MIGRATION_VERSION } from '../../dist/core/consts.js';
-import { tableSchema } from '../../dist/core/types.js';
+import { tableSchema } from '../../dist/core/schemas.js';
 import { column, defineTable } from '../../dist/runtime/config.js';
 import { NOW } from '../../dist/runtime/index.js';
 
diff --git a/packages/db/test/unit/index-queries.test.js b/packages/db/test/unit/index-queries.test.js
index ad588959d..f5bde70e8 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 { getCollectionChangeQueries } from '../../dist/core/cli/migration-queries.js';
-import { tableSchema } from '../../dist/core/types.js';
+import { tableSchema } from '../../dist/core/schemas.js';
 import { column } from '../../dist/runtime/config.js';
 
 const userInitial = tableSchema.parse({
diff --git a/packages/db/test/unit/reference-queries.test.js b/packages/db/test/unit/reference-queries.test.js
index a4b0bdd2d..49d816f73 100644
--- a/packages/db/test/unit/reference-queries.test.js
+++ b/packages/db/test/unit/reference-queries.test.js
@@ -1,7 +1,7 @@
 import { expect } from 'chai';
 import { describe, it } from 'mocha';
 import { getCollectionChangeQueries } from '../../dist/core/cli/migration-queries.js';
-import { tablesSchema } from '../../dist/core/types.js';
+import { tablesSchema } from '../../dist/core/schemas.js';
 import { column, defineTable } from '../../dist/runtime/config.js';
 
 const BaseUser = defineTable({