Hmmm thanks for the reply. Would that be true even if the database is just a single table each? In my mind I shouldn't need to do any schema migrations because I'm really just a file server and a UI. But I guess with adding more tables (akin to sheets in excel world) I would need to keep that in mind.
I may try this when I get some free time, thanks for the feedback!
The problem appears less when creating new tables, creating tables is relatively safe. But let's say in your one table per db you keep track of a few things (columns). For example you have a column for the name of the sheet and another column for the time it was created and some other miscellaneous columns. Later on once you have a hundred users or so you get a persistent feature request: keep track of the time it was last updated. You write up a simple migration, it looks something like
ALTER TABLE your_table ADD updated_at TEXT;
This is also relatively harmless, but now you have to figure out how to run this migration across your 100 databases, and deal with the case where maybe some fail, what do you do then? Rollback everywhere? But what if some users have already have rows that use the new column? Do you delete their data? Etc. Now consider a more complicated migration, maybe it renames a column, drops a column, adds a default value, drops an index, etc. You can see where problems might come from.
That said I do really like your idea, I've gotten into sqlite a lot lately (I work on the database team at Fly.io), I'm just responding to you questions specifically about migrations.
Thanks for the detailed answer. I was thinking of a more hands-of approach in terms of what I do vs what the user does. So in terms of this feature request, it would actually be implemented client side. That is, I would update the client to ask the user if they want this new feature, then use IF EXISTS to see if the table already exists, and if it does, tell the user to fix it themselves. Or could we keep all the metadata in a seperate table and JOIN it on the ID? In any event, I'm literally just a reliable file server and a UI in this equation (plus backups).
For context, this would really be aimed at developers and admins to manage data for their clients or co-workers. Stuff that currently is an excel sheet, but needs to leave excel world to do some mundane task, such as filling a pdf and uplading it somewhere. You could do this is word mailmerge + excel, but now you (the admin) are in charge of performing this mundane task every week. And belive me this kind of thing is very common. In fact, the one special thing I may do is somehow write the data into an excel file and somehow allow them to sync to each other (not the logic part from either side though).
Not even sure if I would try to sell or just make it for free. It wouldn't be something streamlined like airtable, although if it got interest maybe I would take your approach.
If you like my idea btw, feel free to use it! I have nothing but respect for fly.io, I used it breifly for a side project and it was realy simple to set up. You saying you like my idea actually gives me confidence in trying it out, thank you!
Hah I've got my own backlog of side projects, don't tempt me I'm like a year behind on it :). If you ever get around to building it or something like it I'd love to hear about it. You should check out tools like https://github.com/nocodb/nocodb which expose a spreadsheet like interface on top of a database.
I may try this when I get some free time, thanks for the feedback!