Allow injecting ignore-table-prefixes in database-introspector (Sqlite) #1571
Description
Cloudflare D1 utilizes prefixed tables that hold internal state information, and you cannot introspect them without throwing an error. This causes issues for Kysely in cases like migration because it does any exhaustive search of the space, and hits these tables which causes an error and halts migration progress. This appears to come from the Workerd level, not the database.
Currently, Kysely ignores tables prefixed with sqlite_%. Making a slight tweak to this to include the Cloudflare prefix of _cf_ skips these schema checks and allows execution to continue. Today, there doesn't seem to be any way to add additional ignored table prefixes within Kysely. The only recourse currently is to write an alternative DB migration framework that omits these baseline queries that Kysely performs.
Proposal
Allow passing in of additional tablenames/prefixes to the introspector so behavior like above can be dealt with in a structured manner. With the rise of distributed Sqlite, this may be a situation that will crop up more frequently in the future if other providers adopt similar behaviors. I'm not sure if other distributed databases have similar issues, however there may also be value in skipping over certain tables in other situations that I'm not thinking of.
The downstream kysely-d1 would need to adopt these changes to actually fix the issue, but right now there is no way to alter the upstream behavior to prevent querying of these tables, so no fix is possible AFAIK. (short of re-imlementing the existing introspector with additional table prefix skips)
This line seems to be where the error occurs when interrogating the protected table pragma
The error can be observed by running the following against a local D1 database via wrangler. I believe this is the same query that Kysely makes when looking for an existing migrations table:
const r = await sql`with "table_list" as (select "name", "sql", "type" from "sqlite_master"
where "type" in ("table", "view") and "name" not like "sqlite_%" order by "name")
select "tl"."name" as "table", "p"."cid", "p"."name", "p"."type", "p"."notnull", "p"."dflt_value", "p"."pk"
from "table_list" as "tl", pragma_table_info(tl.name) as "p" order by "tl"."name", "p"."cid"`
.execute(db);
## Error: D1_ERROR: not authorized: SQLITE_AUTH
## at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
## at async D1Connection.executeQuery (file:///C:/Development/start-basic/node_modules/kysely-d1/dist/index.js:77:25)
## at async connection.executeQuery (file:///C:/Development/start-basic/node_modules/kysely/dist/esm/driver/runtime-driver.js:110:24)
## at async file:///C:/Development/start-basic/node_modules/kysely/dist/esm/query-executor/query-executor-base.js:35:28
## at async DefaultConnectionProvider.provideConnection (file:///C:/Development/start-basic/node_modules/kysely/dist/esm/driver/default-connection-provider.js:10:20)
## at async DefaultQueryExecutor.executeQuery (file:///C:/Development/start-basic/node_modules/kysely/dist/esm/query-executor/query-executor-base.js:34:16)
## at async TORCH_DATABASE (C:\Development\start-basic\src\middleware\databaseInitMiddleware.ts:35:5)
## at async next (C:\Development\start-basic\node_modules\@tanstack\start-server-core\src\createStartHandler.ts:465:20)
## at async handleServerRoutes (C:\Development\start-basic\node_modules\@tanstack\start-server-core\src\createStartHandler.ts:423:17)
## at async eval (C:\Development\start-basic\node_modules\@tanstack\start-server-core\src\createStartHandler.ts:238:48) {
## [cause]: Error: not authorized: SQLITE_AUTH
## at D1DatabaseSessionAlwaysPrimary._sendOrThrow (cloudflare-internal:d1-api:128:24)
## at async D1PreparedStatement.all (cloudflare-internal:d1-api:314:46) {
## [cause]: undefined