Connect to local SQLite Database

[nryberg]

With SQLite connectivity to S3 and other bucket platforms, it's possible to use it as a local production database. It's fast to setup and easy to use, and nearly universal in the tools that connect to it. LiteStream provides the connectivity.

I'd love to see Malloy connect to a local SQLite instance.

[anikaks]

Hey @nryberg thanks for the request! Unfortunately with the limitations of SQLite it wouldn't be possible for us to support now, as Malloy heavily uses and requires support for arrays.

[jackpal]

Would it be possible to use SQLite's json support with JSON arrays as a substitute for regular SQL arrays?

It seems like you could use json_group_array to create array values and json_each to UNNEST them for querying them.

[lloydtabb]

Hi Jack!!!

Looks like it might work. The Postgres implementation is pretty close to this.

Here is what the dialect implementation is for Postgres.

https://github.com/looker-open-source/malloy/blob/main/packages/malloy/src/dialect/postgres.ts

And how we talk to the Postgres database.

https://github.com/looker-open-source/malloy/blob/main/packages/malloy-db-postgres/src/postgres_connection.ts

The SQL dialect needs to support MD5 hashing, large decimal numbers, pretty sophisticated window functioning (being able to partition on any expression in the current query), SUM(DISTINCT xx) just to name a few things.

I'm usually surprised by something that is missing.

If you want to take a shot at it, I'd be glad to help.

lloyd

[jackpal]

Wow, tempting! But I am not ready to take on that big of a project right now. I don't know enough SQL to do it easily. And unfortunately I don't currently have a use case for malloy-on-sqlite, I am just interested in it as an intellectual puzzle.