SQLite support as database

[kaushalyap]

SQLite is appropriate for certain use cases which eliminate the disadvantages of traditional database architectures. There is Pocketbase but it uses REST and maintainer seems to have no intention of supporting GraphQL, so no fine-grained data access / mutations.

[kaushalyap]

@dosco Any plans, ideas?

[adammmiller]

+1 for this- there some projects in the sqlite space for replication/backup (Litestream & litefs) that make Graphjin a great fit for this

[dosco]

I had looked into Sqlite a while ago. I want to support it (without having to enable CGO) maybe have seperate SQlite build or via some socket interface (not sure if that exists). The big issue I can see is that it does not support CROSS JOINS LATERAL which is needed. I'll take another look.

[gasser707]

Hello I could take a shot at trying to add this during my free time from university/work, could you maybe point out to me where I could get started?

[dosco]

@gasser707 that sounds really great I'm happy help you with this. Longer more detailed answer below.

[dosco]

Firstly only focus on queries (for a quick win) and ignore mutations. Secondly model your changes on what I did to add MySQL support. By default all sql generated is as close to the sql standard and postgres compatible which means that you have to only database specific support in places where MySQL or Sqlite has deviated from the standard.

This is not too hard just search for "mysql" in the codebase and made changes according to the 4 points listed below. As I've mentioned below sqlite does not support cross joins which is required this is the only info i've found on recreating this in sqlite https://news.ycombinator.com/item?id=14932719

Feel free to reach out anytime on here or the discord happy to do a zoom and walk you though anything you need.

1. Stick to the core package and sub-packages for adding support and include integrations tests in the tests package
2. First step is db discover core/internal/sdata (not sure if sqlite supports this) if not its ok since we also have support for a schema file which we can use to manually define the db schema if we can't discover it.
3. Next up is the parser core/internal/qcode which builds the qcode object from which we generate the sql. probably not much to do in this package as well
4. Finally the sql generater core/internal/psql this is where the meat of the changes would be. You would have to figure out an alternative to CROSS JOINS LATERAL which sqlite does not support and to add support for sqlite JSON functions where needed.

[c-nv-s]

did this go anywhere?

[dosco]

i'm not an sqlite user but i think this could be possible quick search tells me that maybe sqlite does support lateral joins and json