Support for direct SQL interfaces

[paulfitz]

Individual Grist documents are stored in SQLite and should support SQL, especially for querying, and ideally also for updating.

[jared-hardy]

As a cloud service provider, I highly recommend you move up to a more reliable network-distributed database backend (which can still utilize an SQLite file backing store, such as Dqlite or BedrockDB), and this in turn should give you access to a known network wire protocol for SQL (and potentially GraphQL) transactions "for free" as a result of distributed service integration. BedrockDB for example provides the option of a MySQL emulated wire protocol. Other more advanced distributed databases like Yugabyte-DB offer the option of PostgreSQL compatible wire protocols, with many connection pooler and proxy options available to help control customer access.

[paulfitz]

Thanks for the advice @jared-hardy! I've modified the issue to be clear that it is talking about how individual Grist documents are stored - which doesn't affect your point, I just realized the issue could be read to apply to the full service.

In the world of spreadsheets, it helps for Grist documents to have a complete, standalone, downloadable, email-able representation, and SQLite works very well for that. But as you point out there are ways we could retain that representation as a backing store. Worth thinking about.

[almereyda]

For a static export of an interactive grist, there is an SQLite version that compiles down to WebAssembly and is called sql.js-httpvfs and described in the article Hosting SQLite databases on Github Pages - (or any static file hoster) - phiresky's blog, with more peeks on Twitter.

And as the storage interface already builds on the SQLite dialect 1 and TypeORM https://github.com/gristlabs/grist-core/blob/7f1f8fc9e60a2e131419301161992c8c6bc80dd4/package.json#L119 there is already plenty Connection Options available one could implement for the server, too, if I am not mistaken..

---

This is another direct SQL interface one could think of, not only as an interface in the grist UI, but as swappable persistence layer for the application itself. And also for retroactive analysis and visualisation with software like:

• vega/voyager: Visualization Tool for Data Exploration
• vega/lyra: An interactive, graphical Visualization Design Environment (VDE)
• constellation-app/constellation: A graph-focused data visualisation and interactive analysis application.
• finos/perspective: Streaming pivot visualization via WebAssembly
• rawgraphs/rawgraphs-app: A web interface to create custom vector-based visualizations on top of RAWGraphs core
• getredash/redash: Make Your Company Data Driven. Connect to any data source, easily visualize, dashboard and share your data.

---

Next to the prior aspects, an SQL dump/static SQLite export from a given user's point of view of the database also opens the door for selected exports, e.g. staticly packed, interactively visualised datasets in 'grist-to-go'.

[johncant]

Hi - I've recently hacked together this POC. I plan to use this to run SQL on my Grist docs: https://github.com/johncant/gristfdw

[anaisconce]

There's an endpoint for SQL queries. Documentation: https://support.getgrist.com/api/#tag/sql