Cloudflare D1 adapter is inefficient - calls "create table if not exists" on instantiation

[treasureseth]

The Cloudflare D1 Adapter has a private constructor and the only way to instantiate it is to call the static create method. That method internally calls init, which makes a "create table if not exists" query.

This means that simply instantiating the session storage driver would trigger this query - so effectively it pollutes the whole app. Instead, we should run a migration on setup and remove this superfluous query on instantiation.

storages/packages/cloudflare/src/d1.ts

Lines 13 to 30 in 216ee4f

	private constructor(db: D1Database, tableName?: string) {
	this.db = db;
	this.tableName = tableName ?? defaultTableName;
	}
	static async create<T>(db: D1Database, tableName?: string): Promise<D1Adapter<T>> {
	const adapter = new D1Adapter<T>(db, tableName);
	await adapter.#init();
	return adapter;
	}
	async #init() {
	await this.db
	.exec(`CREATE TABLE IF NOT EXISTS "${this.tableName}" (key TEXT PRIMARY KEY, value TEXT)`);
	await this.db
	.exec(`CREATE INDEX IF NOT EXISTS "IDX_${this.tableName}" ON ${this.tableName} (key)`);
	}

[Satont]

Is this kind of expensive operation for cloudflare? I mean usually those sql things takes small ms.

But anyways we can drop this thing and instead move those queries into readme to setup section.

Are you wanna make PR?

[treasureseth]

I think the problem is more widespread. Many other adapters follow the same pattern of private constructor and force you to fire a create table if not exists query.

Here's the psql adapter that actually fires 2 extra queries:

storages/packages/psql/src/mod.ts

Lines 22 to 41 in 216ee4f

	private constructor(opts: AdapterConstructor) {
	this.tableName = opts.tableName;
	this.query = opts.query;
	}
	static async create(opts = { tableName: 'sessions' } as Omit<AdapterConstructor, 'query'>) {
	const queryString = `
	CREATE TABLE IF NOT EXISTS "${opts.tableName}" (
	"key" VARCHAR NOT NULL,
	"value" TEXT
	)`;
	const query = buildQueryRunner(opts.client);
	await query(queryString);
	await query(`CREATE UNIQUE INDEX IF NOT EXISTS IDX_${opts.tableName} ON "${opts.tableName}" ("key")`);
	return new PsqlAdapter({
	...opts,
	query,
	});
	}