Feature request: Support for per-user database(s)

[threepointone]

This is probably a subset of #31, but I'm making a fresh issue because maaaybe it's a simple enough subset that I'll be able to do it myself.

I'm building a Todo app. Every user gets their own Todo list (in their own Todo Durable Object, backed by their own DO sqlite database).

As an admin, only I will be able to use the studio to look into every user's database.

All database access will via bindings inside the Worker, there won't be any http access (except I suppose by the studio?)

What's the least number of modifications I'd have to make to make this work for me? Correct me if I'm wrong)

• I'd have to read a database id dynamically (probably via cookie that I've authed the user on the app) and pass it here

  starbasedb/src/index.ts

  Line 145 in 460bc21

  const id: DurableObjectId = env.DATABASE_DURABLE_OBJECT.idFromName(DURABLE_OBJECT_ID);

• I could pass the user id into the url for /studio/userId, and add it as a header to every request made here

  starbasedb/src/studio/index.ts

  Line 41 in 460bc21

  "Authorization": "Bearer ${apiToken}"

But every user's database must have the same schema too. How do you suggest I go about that, should I manually create tables here?

starbasedb/src/do.ts

Line 55 in 460bc21

What do you think I'm missing? happy to do some legwork here. Super impressed with this, and would love it to become my daily driver for my projects.

[Brayden]

Thanks for the issue @threepointone!

I think you might be right that this could be a simple ask (and maybe some of its already in flight – check my work). If I'm interpreting this correctly you want to be able to have a single Todo app with N number of SQLite databases associated with it, one per user. The goal would be to deploy a StarbaseDB instance per user and have a way for you to view their database using Studio & ideally each deployment could automatically execute a SQL migration to prop it up how you want.

Assuming any of my above points are correct... here are some thoughts so let me know if this aligns and we can make something happen for you.

I'd have to read a database id dynamically (probably via cookie that I've authed the user on the app) and pass it here

Check this currently open PR here where it's proposed that you can either base the Durable Object off of the environment reference, or if you want to bypass the initial /src/index.ts file altogether you can construct your own version of this config and pass in any DO reference you wish.

I mention this in case you want to deploy a new StarbaseDB instance automatically with a script and just hold reference to its hosted URL in your centralized database instance rather than bindings? Curious if the path of a central database to hold onto each users deployed StarbaseDB URL as well as whatever username/password you construct for their auth would be the best approach. If there's any easier way that you have in mind I'm all ears because likely a use cases many people have (or will have) with Durable Objects.

But every user's database must have the same schema too. How do you suggest I go about that, should I manually create tables here?

This is something I have written on my whiteboard behind me to implement, is the ability for someone to pass in a .sql file for example that is used to execute on the first initial run so users can setup whatever tables and/or seed any rows they might want to. I'd imagine this would be defined in the install.sh setup script I currently have or maybe a value you can pass into the Config object as well. Might have to noodle on the cleanest way this happens but open to ideas if you have any.

For now though you would probably want to add it right where you referenced though.

What do you think I'm missing? happy to do some legwork here.

Any and all contributions are certainly welcome if you think anything above sparks action items you want to take on... I'm all for it! Let me know how I can be the best resource for you.

We can keep this an open conversation in case I missed something or should talk through improving any of the areas more to fit these types of, likely common, use cases with DO's!

[Brayden]

There is also this endpoint available for importing SQL into a deployed instance too so you don’t have to modify the source to do it for you:

https://starbasedb.hashnode.space/default-guide/import-export/sql-dump