CREATE|DROP database <dbname> and use <dbname>

[jgmdev]

Searched but didn't found nothing about these so I'm writing to ask if the idea of sqlite databases sharding by implementing create|drop commands isn't that crazy or hard to implement.

The idea is to be able write/read from different database files beside the default bedrock.db which is already used to store jobs,cache, etc... This way an application can be further optimized by been able to write into different database files and preventing over-saturation of main database.

CREATE database <dbname>

Usage: create database sports; This would create a sports.db file on the same directory where bedrock.db resides eg: /var/lib/bedrock/ with all the needed tables (if needed) for synchronizations or fail if the file already exists.

DROP database <dbname>

Usage: drop database sports; Would obviously delete the sports.db file and should fail if someone tries to delete the default bedrock.db file.

USE <dbname>

Usage: use sports; Could be used by the client implementation to send an additional parameter like:

Query
query: select * from sqlite_master;
format: json
db: sports

which would then tell bedrock that instead of using default bedrock.db it should make that query on the specified database.

Another command that could be implemented for read only purpose (since processing syncs for insert/update on these statements can be tedious to implement) could be attach

ATTACH database <dbname> as <alias>

Usage:

use sports;
attach database partners as p;
select * from players left join p.leaders on players.leader = p.leaders.id;

The dbname would be automatically replaced by the full path to the db file for eg: /var/lib/bedrock/partners.db and fail if the file does not exists.

I believe these features would widen up the use cases of bedrock and make it more versatile.

[quinthar]

Hi! The main issue we haven't taken this design is because we use the SQLite "WAL mode", and transactions across ATTACHed databases aren't atomic. It's a bit of a bummer, but in practice there isn't much advantage to having multiple database files (at least, not that we've experienced) so we just keep everything in a single database. Thanks for the detailed suggestion, and I'm sorry that we can't accommodate!

[vfssantos]

Hey! First, found this project amazing!!

Now, I know this has been discussed already, but I'd ask the maintainers (@quinthar ) to reconsider this issue. I mean, this is one of the main differences (in terms of functionality it provides) from Turso's Libsql, and I think it's the main reason for their recent growth. Furthermore, I think that the use case for that is for powering multi-tenant SAAS applications with one db per tenant, where it'd be possible to share the DBs with each client. Business-wise, I see many benefits for this, and I guess other developers see it too. I'd certainly try it out!