Cloudflare D1: too many terms in compound SELECT

[konsumer]

It would be cool to be able to store in cloudflare D1 (distributed sqlite, that is built-in to CF) directly.

I made a very simple example project here.

You should be able to do it like this, in a request-handler:

import { MarkdownDB } from 'mddb'
import ClientD1 from 'knex-cloudflare-d1'

// this needs to be called in a fetch-callback, so you have env
const client = new MarkdownDB({
  client: ClientD1,
  connection: {
    database: env.DB
  }
});

Additionally, you should be able to do offline indexing (like outside of a request, directly to D1, in a separate build-step or whatever.)

import { MarkdownDB } from 'mddb'
import ClientD1 from 'knex-cloudflare-d1'
import { getPlatformProxy } from 'wrangler'

// you can access env in scripts like this
const { env } = await getPlatformProxy()

const client = new MarkdownDB({
  client: ClientD1,
  connection: {
    database: env.DB
  }
});

I think it might be handy to be able to setup whatever db client you want in markdowndb.config.js, so I could put the custom code there, and use it in mddb, but I am not sure if that currently works, so I put this in tools/indexer.js.

Currently, it gives me this error:

Error: insert into `files` (`_id`, `extension`, `file_path`, `filetype`, `metadata`, `tasks`, `url_path`) select '1fb8a8f02c48f1d3e31905c83db632e998b0c403' as `_id`, 'md' as `extension`, 'blog/data-cli.md' as `file_path`, NULL as `filetype`, '{"name":"data-cli","description":"data - command line tool for working with data, Data Packages and the DataHub","stars":63,"forks":8,"tags":[],"tasks":[]}' as `metadata`, '[]' as `tasks`, 'data-cli' as `url_path` union all select 'd9d6837868536b6cdc1b5673f221804965e4332e' as `_id`, 'md' as `extension`, 'blog/datapipes.md' as `file_path`, NULL as `filetype`, '{"name":"datapipes","description":"Data Pipes for CSV","stars":118,"forks":17,"tags":[],"tasks":[]}' as `metadata`, '[]' as `tasks`, 'datapipes' as `url_path` union all select '31069a719dff371169be6320d2255389c05148ca' as `_id`, 'md' as `extension`, 'blog/flowershow.md' as `file_path`, NULL as `filetype`, '{"name":"flowershow","description":"💐 Publish your digital garden or any markdown site easily and elegantly.","stars":289,"forks":27,"tags":[],"tasks":[]}' as `metadata`, '[]' as `tasks`, 'flowershow' as `url_path` union all select '7f9499317e0bbcf1b7e03ca006995357cd8218d8' as `_id`, 'md' as `extension`, 'blog/giftless.md' as `file_path`, NULL as `filetype`, '{"name":"giftless","description":"🎁 A pluggable Git LFS server written in Python. Highly customizable and easy to extend.","stars":76,"forks":22,"tags":[],"tasks":[]}' as `metadata`, '[]' as `tasks`, 'giftless' as `url_path` union all select 'e1be0a5ae4171bb3174264377efd8b51b9deb8a0' as `_id`, 'md' as `extension`, 'blog/markdowndb.md' as `file_path`, NULL as `filetype`, '{"name":"markdowndb","description":"Javascript library for treating markdown files as a database","stars":6,"forks":0,"tags":[],"tasks":[]}' as `metadata`, '[]' as `tasks`, 'markdowndb' as `url_path` union all select '64ec9986181f5bc10732f262c4ad8f8639be7ea8' as `_id`, 'md' as `extension`, 'blog/portaljs.md' as `file_path`, NULL as `filetype`, '{"name":"portaljs","description":"🌀 Rapidly build rich data portals using a modern frontend framework.","stars":2000,"forks":317,"tags":[],"tasks":[]}' as `metadata`, '[]' as `tasks`, 'portaljs' as `url_path` - D1_ERROR: too many terms in compound SELECT: SQLITE_ERROR
    at process.processTicksAndRejections (node:internal/process/task_queues:103:5)
    at async Client_D1._query (/Users/konsumer/Desktop/example-markdowndb-d1/node_modules/knex-cloudflare-d1/src/client.js:73:25)
    at async Runner.ensureConnection (/Users/konsumer/Desktop/example-markdowndb-d1/node_modules/knex/lib/execution/runner.js:318:14)
    at async Runner.run (/Users/konsumer/Desktop/example-markdowndb-d1/node_modules/knex/lib/execution/runner.js:30:19)
    at async /Users/konsumer/Desktop/example-markdowndb-d1/node_modules/mddb/node_modules/knex/lib/execution/batch-insert.js:31:30 {
  [cause]: Error: too many terms in compound SELECT: SQLITE_ERROR
      at D1DatabaseSessionAlwaysPrimary._sendOrThrow (cloudflare-internal:d1-api:140:24)
      at async cloudflare-internal:d1-api:348:41
      at async #fetch (file:///Users/konsumer/Desktop/example-markdowndb-d1/node_modules/miniflare/dist/src/workers/core/entry.worker.js:4259:18)
      at async ProxyServer.fetch (file:///Users/konsumer/Desktop/example-markdowndb-d1/node_modules/miniflare/dist/src/workers/core/entry.worker.js:4163:14) {
    [cause]: undefined
  }
}

Currently, it seems to build the tables ok, and generates .markdowndb/files.json so I think it might have to do with how batch inserts on indexFolder, but I am not sure.

Also, it works fine with this:

const mddb = new MarkdownDB({
  client: 'sqlite3',
  connection: {
    filename: '.markdowndb/example.db'
  }
})

so it might be a problem with knex-cloudflare-d1, or some specific issue that D1 has.

[konsumer]

I can get a bit further by monkey-patching batchInsert, but then I get a FOREIGN KEY constraint error.

mddb.db.context.batchInsert = async (table, records = []) => {
  if (!Array.isArray(records)) {
    return []
  }
  return await Promise.all(records.map((chunk) => mddb.db(table).insert(chunk)))
}

Error: insert into `links` (`from`, `link_type`, `to`) values ('1fb8a8f02c48f1d3e31905c83db632e998b0c403', 'normal', 'https://datahub.io/docs/about') - D1_ERROR: FOREIGN KEY constraint failed: SQLITE_CONSTRAINT
    at process.processTicksAndRejections (node:internal/process/task_queues:103:5)
    at async Client_D1._query (/Users/konsumer/Desktop/example-markdowndb-d1/node_modules/knex-cloudflare-d1/src/client.js:73:25)
    at async Runner.ensureConnection (/Users/konsumer/Desktop/example-markdowndb-d1/node_modules/knex/lib/execution/runner.js:318:14)
    at async Runner.run (/Users/konsumer/Desktop/example-markdowndb-d1/node_modules/knex/lib/execution/runner.js:30:19)
    at async Promise.all (index 0)
    at async mddb.db.context.batchInsert (file:///Users/konsumer/Desktop/example-markdowndb-d1/tools/indexer.js:35:10)
    at async MarkdownDB.saveDataToDisk (file:///Users/konsumer/Desktop/example-markdowndb-d1/node_modules/mddb/dist/src/lib/markdowndb.js:124:9)
    at async MarkdownDB.indexFolder (file:///Users/konsumer/Desktop/example-markdowndb-d1/node_modules/mddb/dist/src/lib/markdowndb.js:63:9)
    at async file:///Users/konsumer/Desktop/example-markdowndb-d1/tools/indexer.js:38:1 {
  [cause]: Error: FOREIGN KEY constraint failed: SQLITE_CONSTRAINT
      at D1DatabaseSessionAlwaysPrimary._sendOrThrow (cloudflare-internal:d1-api:140:24)
      at async cloudflare-internal:d1-api:348:41
      at async #fetch (file:///Users/konsumer/Desktop/example-markdowndb-d1/node_modules/miniflare/dist/src/workers/core/entry.worker.js:4259:18)
      at async ProxyServer.fetch (file:///Users/konsumer/Desktop/example-markdowndb-d1/node_modules/miniflare/dist/src/workers/core/entry.worker.js:4163:14) {
    [cause]: undefined
  }
}

[rufuspollock]

@konsumer working on this - thanks for asking 🙂

[rufuspollock]

@konsumer i guess i was imagining in cloudflare it might be more normal to index a file at a time - i've just fixed #127 which has better API support for that.

I'll try to get a repo here of your issue. Let me know if you get any further 🙂

[konsumer]

I am away from computer on holiday right now, but I am wondering if maybe knex-cloudflare-d1 is wonky. 1 at a time should work as a fallback, but in my tests it failed. Maybe we need to pre-calc the foreign key stuff, or just disable those constraints for d1.

I made a cms on cf that has an editor, and I needed to import all my old content which is in markdown files, before I found your project. It works pretty well, but the structure is a bit different. One thing I often avoid (with d1 especially if I do bulk stuff) is foreign keys. In their docs they mention pragmas to temporarily disable them, but I think it has to be per-session, which fails on the knex adapter. It works well without foreign keys, but I'd rather use some standardized setup like you've got.

I bet we could fix the first problem by not doing compound selects (use multiple queries) and the other stuff would work ok.

[konsumer]

@rufuspollock more notes, now that I am back home.

@konsumer i guess i was imagining in cloudflare it might be more normal to index a file at a time - i've just fixed #127 which has better API support for that.

Yeh, that's an idea, and this feature seems generally useful anyway, but I think we could probably also do the indexing the same way, on a directory, as long as the queries are ok for D1. This idea of processing files into db records at worker build-time is pretty common (for example pages work this way for asset files into KV.)

I will try to make a little importer that works with processMarkdown to see if I can isolate/circumvent the issue, but I have a feeling it will also need the queries simplified.

[rufuspollock]

@konsumer how did you get on?

[konsumer]

@rufuspollock I ended up dropping knex, and just using D1, directly.