Ayb: A multi-tenant database that helps you own your data
blog.marcua.net
blog.marcua.net
It's so helpful to read the headline through your eyes! While I can't change this title on HN, I'll tone down/relegate the multi-tenant bits to the features section in the future. Thank you for this feedback!
How does this actually work in the real world, though? Like, if I run an ayb node somewhere, do I then share out individual tables (or slices of tables, or views) to other apps in an OAuth kind of way? How does that happen? What's the permissions/ACL model, and how do you keep that from getting too confusing for the average person? And even if you can tackle the connectivity and auth problems, how can the "requesting" app know what schema to expect?
And while we're on the subject of the "difficulty floor" here, what makes your CLI wrapper that accepts SQL (or your HTTP API that accepts SQL) any easier for average users to consume than just installing SQLite themselves and running the exact same SQL? It seems like you're trying to target average users in terms of your ambitions, but your approach to get there is _extremely_ developer-oriented, which is one step _beyond_ "power user".
(I ask these questions not because I think you have a stupid idea, but because I'm hoping to learn that you have good answers for them -- I really want to see a world where users own their data and just "permit"/"deny" access to it, rather than having third parties own data about users)
I love your questions because they get to the heart of how to make this stuff easier for more people, and in transparency, I only have some of the answers! :)
> What's the permissions/ACL model, and how do you keep that from getting too confusing for the average person? (You asked a lot more here, but I think this is the root of this question) The admittedly naive permissions/ACL model I'm envisioning/speccing now is at the database level, similar to GitHub at the repository level. If you create a database, you can add read/write and read-only collaborators, one of which is `public`/`world`, which would make the database accessible to unauthenticated users.
Your questions around table slices/views are excellent, and in the model I'm proposing, ayb won't be able to help. The model I'm proposing will be good enough for "here's my dataset, and you can build on it" or "I spun up a project and have a private DB that my webapp is gating" but not "I want user X to have access to row Y." Row-based auth would thus be pushed into the application layer, which seems to come with the territory with SQLite as best I can tell. To contrast, something like Supabase is able to provide both a database and row-based auth because Postgres provides better support natively, and Supabase then made it easy to add common auth providers.
> what makes your CLI wrapper that accepts SQL (or your HTTP API that accepts SQL) any easier for average users to consume than just installing SQLite themselves and running the exact same SQL If the goal is to write/learn SQL, I agree that ayb offers nothing on top of SQLite. As SQLite is the database and ayb is the database management system, the things ayb makes simpler are on the "management system" side --- without ayb, it's hard to create a new one, it's hard to control access, and it's hard to access one from a web application. You're right that by that definition, it's more developer-friendly than power user-friendly, and I hope we can do better with future iterations.
I think it's cool that you're trying to tackle adding simplicity to managing a multi-user (if not actually multi-tenant, given the lack of row-level permissions management) SQL DB. My suggestion then would be to refine your pitch so that it matches your project a bit better -- that way you avoid the problem of people disappointed to find it doesn't do something they expected (but which it was never trying to do).
There’s many things like this that I wish existed that make prototyping easier and faster and infrastructure a little bit nicer to use.
My concern is that because it’s a Nerfed version of the most basic/powerful/flexible implementation that is expected for a RESTful LAMP/SOAP application, it’s not gonna find its way into production applications, and as a result is going to be stuck as kind of prototyping tool.
My question to the author is have you figured out how to prevent that?
Thank you for your kind words and the great question!
To your compliment: I agree ayb can help with easier prototyping today.
To your concern: I would not use ayb in a production setting today. To be explicit, while the roadmap [1] is long, I think that ayb will be useful in production once we've implemented a v1 of auth, permissions, persistence beyond the node, and isolation.
To "how to prevent that," here's a rough outline that I'm open to feedback on:
- Implement the v1 features above
- Host a public instance (and encourage others to, not trying to empire-build)
- Build some fun applications on top of the public instance myself to stress test it
- Encourage others to do the same and/or run some "learn SQL" classes to better understand where beginners get stuck and address those issues
Excited to see where it goes and if there’s an associated space here for integrating some form of environment management to wrap up all those other loose ends around dev.
From the example it seems like it is a CLI that will create SQLite databases, and then runs an HTTP server that passes through queries to the underlying database?
Not trying to be critical but a few questions:
- What is the appeal of a CLI for creating SQLite databases when you can create one by making a new file like "my_db.sqlite3"?
- If it's meant to make the DB setup process easier for students, how does it create and run Postgres or MySQL etc? It seems like you have to already have a running DB server and then configure the credentials, versus something like starting a server using Docker and auto-wiring the credentials.
- For exposing an HTTP API on top of databases, as a heads-up there are tools like Apache Avatica which let you query any DB that has a JDBC driver over HTTP, JDBC, and gRPC. https://calcite.apache.org/avatica/docs/json_reference.html
There is a very long walk-thru in the Demo.md file -
https://github.com/dynamoRando/treaty-mono-repo/blob/main/tr...
Since this has been a hobby project I haven't yet published any public docker images, but there are docker files that you can pull from the repo and build if you wish.
Will keep an eye on this one, could definitely see myself using it once the access control stuff is sorted
we sorely need a go/rust postGREST equiv. for SQLite.
Until then, pocketbase, while perhaps heavyweight, might be the best option
https://github.com/subzerocloud/showcase/tree/main/flyio-sql...
But nope.