publish cloudrun should deploy a more recent SQLite version

[fgregg]

I recently changed from deploying a datasette using datasette publish heroku to datasette publish cloudrun. A query that ran on the heroku site, now throws a syntax error on the cloudrun site.

I suspect this is because they are running different versions of sqlite3.

• Heroku: sqlite3 3.31.1 (-/versions)
• Cloudrun: sqlite3 3.27.2 (-/versions)

If so, it would be great to

1. harmonize the sqlite3 versions across platforms
2. update the docker files so as to update the sqlite3 version for cloudrun

[fgregg]

yes, filter clause on aggregate queries were added to sqlite3 in 3.30

[fgregg]

there's a lot of complexity here, that's probably not worth addressing. i got what i needed by patching the dockerfile that cloudrun uses to install a newer version of sqlite.

[simonw]

I ran into this exact same problem today! I only just learned how to use filter on aggregates: https://til.simonwillison.net/sqlite/sqlite-aggregate-filter-clauses

A workaround I used is to add this to the deploy command:

datasette publish cloudrun ... --install=pysqlite3-binary

This will install the https://pypi.org/project/pysqlite3-binary for package which bundles a more recent SQLite version.