How to switch the database connection at runtime (Multi-tenancy)

[eborst]

Hi, I just started using SQLC and this seems promising! It really helps in separating the boiler plate code with strong typed methods!

Now I do have a question: How should I implement switching the database connection at runtime?
I do have a map of db pools (one for each tenant) and want to switch based on the tenantID in the JWT.
Do I need to initialise every time again? That feels wrong to me...

func New(db DBTX) *Queries {
	return &Queries{db: db}
}

What are your ideas? A small example would be really helpful.

[kyleconroy]

I do have a map of db pools (one for each tenant)

Each database pool should be associated with a single Queries instance. If you already have a map of pools, I'd change the map struct to be a db pool / queries pair like this:

type Tenant struct {
    db *sql.DB
    q *Queries
}

pools := map[string]Tentant{}

Obviously replace string in this example with the type for your tenant ID.

The other advantage of this approach is that you can use Prepare to create prepared queries for each tenant database.

[nkev]

How would you structure the sharing of a single models package between the Tenants? I would like to be able to allow the choice of Postgres, MySQL or Sqlite via my app's config params, but use a single models package that works with any of these engines.

[andrewmbenton]

I would like to be able to allow the choice of Postgres, MySQL or Sqlite via my app's config params, but use a single models package that works with any of these engines.

I wouldn't expect sqlc's models to be a good fit for this setup. There's currently no way to reuse a model across sql packages within a sqlc config. I would create a higher-level struct and then convert sqlc's structs into that. Or you might be able to do something clever with generics.