One db per user

[andreas-cederved]

Hi Ben,

thanks for sharing your expertise and know-how in Go in this form. I'm new to Go, and the way you have structured your code and your concepts resonates well with my experiences from other languages. It's beautiful - and it just fast tracks my ability to build a first application in Go that is not total garbage.

Ok, so now to my weird question. Let's say you want to achieve data isolation by having one sqlite db per user (already garbage warning hehe). How would you implement that in your code? For the sake of the argument, let's say you wanted to maybe switch db connection based on user api key in the context. How would you do that in main and in the underlying service interfaces?

Kind regards,
Andreas

[benbjohnson]

Let's say you want to achieve data isolation by having one sqlite db per user (already garbage warning hehe). How would you implement that in your code?

hey @andreas-cederved, good question. I don't think separating by one DB per user is a bad approach at all unless you have a ton of users. I think you can mostly isolate the separation within your SQLite implementation so the rest of your application doesn't need to know about it.

First, I'd make a map of accounts to *sql.DB objects here instead of just having one DB: https://github.com/benbjohnson/wtf/blob/main/sqlite/sqlite.go#L45

You'll also need to either have some way of separating accounts at the front end (e.g. subdomains like myaccount.slack.com) or by having a single database that holds all authentication information. That way you don't have to search each individual account database for users and a single database will allow you to use a UNIQUE constraint across all account names. I personally like the subdomain approach if you are able to use a wildcard SSL certificate but it does make local development a little more complicated.

Once you have the account name (e.g. "myaccount"), I'd probably use context.Context to pass it around as most methods will probably need it. You can create wrapper functions for building and retrieving the context info like this: https://github.com/benbjohnson/wtf/blob/main/context.go#L24-L33

As long as your account names are just alphanumeric characters, you can use it as the database file name so you don't need an additional lookup (e.g. /var/lib/myapp/data/myaccount.db)

Does that make sense?

[andreas-cederved]

Hi,

thanks for the quick and thoughtful reply. Yeah, I think that makes a lot of sense!

Best regards,
Andreas