Skip to content

Email for professionals and teams

8.2 KB
Raw Blame
import { sql, type SQL } from "drizzle-orm";
import * as sqlite from "drizzle-orm/sqlite-core";
import * as pg from "drizzle-orm/pg-core";
import * as mysql from "drizzle-orm/mysql-core";
import { schemaDialect, type DatabaseDialect } from "../dialect";

/**
 * The column and table constructors the schema is written against. The schema file is written once in
 * terms of this kit; the kit for the active dialect supplies the real drizzle builders.
 *
 * Representation is kept identical across dialects wherever raw SQL touches a column: timestamps are
 * whole seconds in a bigint, plain integers are bigint, booleans are native booleans (tinyint on
 * MySQL). Only SQLite typings are exposed to the rest of the app; the other kits are cast to the same
 * shape, which is safe because query building is dialect-agnostic for everything the app uses.
 */
const sqliteKit = {
	table: sqlite.sqliteTable,
	text: sqlite.text,
	integer: (name: string) => sqlite.integer(name),
	bool: (name: string) => sqlite.integer(name, { mode: "boolean" }),
	timestamp: (name: string) => sqlite.integer(name, { mode: "timestamp" }),
	autoId: (name: string) => sqlite.integer(name).primaryKey({ autoIncrement: true }),
	index: sqlite.index,
	uniqueIndex: sqlite.uniqueIndex,
	desc: (column: unknown): SQL | never => sql`${column} desc` as never,
	nowSeconds: sql`(unixepoch())` as SQL,
};

export type SchemaKit = typeof sqliteKit;

// ---- PostgreSQL ----

const pgTimestamp = pg.customType<{ data: Date; driverData: string | number }>({
	dataType: () => "bigint",
	fromDriver: (value) => new Date(Number(value) * 1000),
	toDriver: (value) => Math.floor(value.getTime() / 1000),
});

const pgKit = {
	table: pg.pgTable,
	text: (name: string, config?: { enum: readonly [string, ...string[]] }) => pg.text(name, config as never),
	integer: (name: string) => pg.bigint(name, { mode: "number" }),
	bool: (name: string) => pg.boolean(name),
	timestamp: (name: string) => pgTimestamp(name),
	autoId: (name: string) => pg.bigserial(name, { mode: "number" }).primaryKey(),
	index: pg.index,
	uniqueIndex: pg.uniqueIndex,
	desc: (column: unknown) => sql`${column} desc`,
	nowSeconds: sql`(extract(epoch from now())::bigint)` as SQL,
};

// ---- MySQL / MariaDB / TiDB ----

const mysqlTimestamp = mysql.customType<{ data: Date; driverData: string | number }>({
	dataType: () => "bigint",
	fromDriver: (value) => new Date(Number(value) * 1000),
	toDriver: (value) => Math.floor(value.getTime() / 1000),
});

/**
 * MySQL cannot index TEXT, so a text column that is a key, unique, referenced or used in an index must be a
 * VARCHAR; every other text column stays LONGTEXT. Which is which is only known once the whole table
 * (including its index callback) is seen, so `text()` returns a recorder that `table()` resolves.
 */
const KEY_LENGTH = 191;
const LONG_KEYS: Record<string, number> = {
	provider_message_id: 512,
	thread_id: 512,
	raw_r2_key: 512,
	r2_key: 512,
	endpoint: 512,
	email: 320,
	recipient: 320,
	name: 255,
	hostname: 255,
};
/** InnoDB allows 3072 bytes per index; utf8mb4 is 4 bytes per character. */
const INDEX_BYTE_BUDGET = 3000;
const KEYED_METHODS = new Set(["unique", "primaryKey", "references"]);

class MysqlTextColumn {
	private readonly calls: Array<[string, unknown[]]> = [];
	keyed = false;

	constructor(
		private readonly name: string,
		private readonly config?: { enum: readonly [string, ...string[]] },
	) {
		return new Proxy(this, {
			get: (target, property, receiver) => {
				if (typeof property !== "string" || property in target) return Reflect.get(target, property, receiver);
				return (...args: unknown[]) => {
					target.calls.push([property, args]);
					if (KEYED_METHODS.has(property)) target.keyed = true;
					return receiver;
				};
			},
		});
	}

	materialize(keyed: boolean) {
		// The builder type changes with each chained call, so it is handled untyped here.
		// eslint-disable-next-line @typescript-eslint/no-explicit-any
		let builder: any;
		if (this.config) {
			builder = mysql.varchar(this.name, { length: 64, enum: this.config.enum as [string, ...string[]] });
		} else if (keyed) {
			builder = mysql.varchar(this.name, { length: LONG_KEYS[this.name] ?? KEY_LENGTH });
		} else {
			// MySQL rejects literal defaults on TEXT columns; an expression default `('x')` is allowed (8.0.13+, MariaDB).
			builder = mysql.longtext(this.name);
			const literal = builder.default.bind(builder);
			builder.default = (value: unknown) => (typeof value === "string" ? literal(sql.raw(`('${value.replace(/'/g, "''")}')`)) : literal(value));
		}
		for (const [method, args] of this.calls) builder = builder[method](...args);
		return builder;
	}
}

const mysqlText = (name: string, config?: { enum: readonly [string, ...string[]] }) => new MysqlTextColumn(name, config);

/** Columns an index callback touches; found by running it once against recording stand-ins. */
function indexedProperties(extra: ((columns: Record<string, unknown>) => unknown[]) | undefined): Set<string> {
	const touched = new Set<string>();
	if (!extra) return touched;
	extra(new Proxy({}, { get: (_, property) => (typeof property === "string" && touched.add(property), { __column: property }) }));
	return touched;
}

function mysqlTable(name: string, columns: Record<string, unknown>, extra?: (columns: Record<string, unknown>) => unknown[]) {
	const indexed = indexedProperties(extra);
	const built = Object.fromEntries(
		Object.entries(columns).map(([property, column]) => [property, column instanceof MysqlTextColumn ? column.materialize(column.keyed || indexed.has(property)) : column]),
	);
	return mysql.mysqlTable(name, built as never, extra as never);
}

function columnBytes(column: unknown): number {
	const value = column as { length?: number; getSQLType?: () => string };
	if (typeof value?.length === "number") return value.length * 4;
	return 8;
}

type MysqlIndexBuilder = ReturnType<typeof mysql.index>;

const isExpression = (column: unknown) => !!column && typeof column === "object" && "queryChunks" in (column as object);

/**
 * MySQL cannot index more than 3072 bytes, which the wide covering indexes on `messages` exceed with
 * utf8mb4 keys. Covering indexes are only an optimisation, so trailing columns are dropped to fit.
 * Expression key parts (`coalesce(thread_id, id)`) are dropped too: drizzle-kit cannot emit MySQL
 * functional index syntax.
 */
function fitColumns(columns: unknown[], keepAll: boolean): unknown[] {
	const parts = columns.filter((column) => !isExpression(column));
	if (keepAll) return parts;
	const kept: unknown[] = [];
	let used = 0;
	for (const part of parts) {
		const bytes = columnBytes(part);
		if (kept.length > 0 && used + bytes > INDEX_BYTE_BUDGET) break;
		kept.push(part);
		used += bytes;
	}
	return kept;
}

function mysqlIndex(name: string) {
	const builder = mysql.index(name);
	return {
		on: (...columns: unknown[]) => builder.on(...(fitColumns(columns, false) as Parameters<MysqlIndexBuilder["on"]>)),
	};
}

function mysqlUniqueIndex(name: string) {
	const builder = mysql.uniqueIndex(name);
	return {
		on: (...columns: unknown[]) => {
			const unique = builder.on(...(fitColumns(columns, true) as Parameters<typeof builder.on>));
			// MySQL has no partial indexes; a filtered unique index cannot be expressed, so it becomes a plain index.
			(unique as unknown as { where: (condition: unknown) => unknown }).where = () => mysql.index(name).on(...(fitColumns(columns, true) as Parameters<MysqlIndexBuilder["on"]>));
			return unique;
		},
	};
}

const mysqlKit = {
	table: mysqlTable,
	text: mysqlText,
	integer: (name: string) => mysql.bigint(name, { mode: "number" }),
	bool: (name: string) => mysql.boolean(name),
	timestamp: (name: string) => mysqlTimestamp(name),
	autoId: (name: string) => mysql.bigint(name, { mode: "number" }).primaryKey().autoincrement(),
	index: mysqlIndex,
	uniqueIndex: mysqlUniqueIndex,
	// Descending key parts are only a sort hint; MySQL builds the index ascending and scans it backwards.
	desc: (column: unknown) => column,
	nowSeconds: sql`(unix_timestamp())` as SQL,
};

export function kitFor(dialect: DatabaseDialect): SchemaKit {
	if (dialect === "postgres") return pgKit as unknown as SchemaKit;
	if (dialect === "mysql") return mysqlKit as unknown as SchemaKit;
	return sqliteKit;
}

export const kit: SchemaKit = kitFor(schemaDialect());