Theo's Typesafe Cult•2w ago

Multi-Tenant Architecture

Hello good folks. The purpose of this question is to get insight from the more experienced community. I'm trying to build a B2B system with data isolation concerns. For now, I chose to go with a schema-per-tenant data partition strategy. All tenants will share a common set of tables attached to their schema. But, the problem with this approach is the schema managment. To change even a single column across all schemas when you have many tenants is a challenge. I've tried an approach where I use drizzleORM to generate the "template" migration scripts. Then when a request comes in, I read the sql content from the file and replace the placeholder with the tenant's schema name. This works fine for creating new tenants. But the real problem now is global updates. I've not found a reliable way to do this so far. It's a greenfield project, so I'm trying to avoid tech debt to the best of my ability. How would you go about building such a system?

29 Replies

Neto•2w ago

you can check how atlas does it

Neto•2w ago

https://atlasgo.io/guides/database-per-tenant/intro

Database-per-Tenant Architectures with Atlas | Atlas | Manage your ...

Intro

DeezyOP•2w ago

Thanks @Neto for this. I am kind of glad you dropped a link to the atlas docs because in fact it's atlas I have been experimenting with all this while for this purpose

Neto•2w ago

as long as you aren't deeep deeep deeep into messing with atlas should work just fine

DeezyOP•2w ago

In fact, deep sums it up 😅 I have actually tried it

Neto•2w ago

deep as in, using it as a plain lib

DeezyOP•2w ago

It hasn't been great to say the least

Neto•2w ago

docs are rough....

DeezyOP•2w ago

I'm sorry I'm not sure I caught this

Neto•2w ago

atlas is both a cli tool and a library the cli part is quite good but the library part is awful you most likely want to build something like this

Neto•2w ago

https://github.com/tursodatabase/examples/blob/master/app-turso-crm/app/lib/utils.ts#L30-L77

GitHub

examples/app-turso-crm/app/lib/utils.ts at master · tursodatabase/e...

A compilation of quickstart & sample projects involving Turso and popular frameworks - tursodatabase/examples

Neto•2w ago

ignore the drizzle part

DeezyOP•2w ago

Yes I reckon. That's what I've been using. I tried the library and it just doesn't work Checking it out now!

Neto•2w ago

in the end i had two options

env "dev" {
  url = "..."
  dev = "docker://postgres/17/dev"
}

data "sql" "tenants" {
  url = "..."
  query = <<EOS
SELECT ref FROM companies
EOS
  args  = []
}

env "node" {
  for_each = toset(data.sql.tenants.values)
  url      = "sqlite://tenants/${each.value}.db"
  dev      = "sqlite://file?mode=memory"
}

variable "ref" {
  type        = string
  description = "Database to be created"
  default = "default value"
}

env "newnode" {
  url      = "sqlite://tenants/${var.ref}.db"
  dev      = "sqlite://file?mode=memory"
}

env "dev" {
  url = "..."
  dev = "docker://postgres/17/dev"
}

data "sql" "tenants" {
  url = "..."
  query = <<EOS
SELECT ref FROM companies
EOS
  args  = []
}

env "node" {
  for_each = toset(data.sql.tenants.values)
  url      = "sqlite://tenants/${each.value}.db"
  dev      = "sqlite://file?mode=memory"
}

variable "ref" {
  type        = string
  description = "Database to be created"
  default = "default value"
}

env "newnode" {
  url      = "sqlite://tenants/${var.ref}.db"
  dev      = "sqlite://file?mode=memory"
}

node is for general migration newnode is to create a new tenant and calling via bash

cmd := exec.Command("sh", "-c", "atlas schema apply --env newnode --file ./db/node-schema.sql --auto-approve --var ref="+_uuid.String())

DeezyOP•2w ago

Okay so you then run this with the CLI?

Neto•2w ago

this config is via the hcl file a solution i found was calling the cli via os commands probably not the best, but it's an option

Neto•2w ago

go has migrate as a library https://github.com/golang-migrate/migrate?tab=readme-ov-file#use-in-your-go-project

GitHub

GitHub - golang-migrate/migrate: Database migrations. CLI and Golan...

Database migrations. CLI and Golang library. Contribute to golang-migrate/migrate development by creating an account on GitHub.

Neto•2w ago

multi-tenant is a mess and people avoid for this exact reason its a shit show

DeezyOP•2w ago

My word! I cannot tell you how comforting that sounds to me 😅 I am kind of behind time with the schedule of the project which is why I'm just all out looking for any solution now. I've been trying to get it working with atlas but it's been like I've been fighting the library all this while

Neto•2w ago

drizzle is quite laid back in terms of multi-tenancy

DeezyOP•2w ago

I totally agree. It's been feeling as if I'm trying to hack the capability into it the past few days. I also looked into TypeORM

Neto•2w ago

avoid typeorm for any reason

DeezyOP•2w ago

But I don't know if I like TypeORM Gotcha 😭 From what I saw from the docs, you don't get typesafe queries like drizzle. For all the decorators it will add to the codebase, I don't know man

Neto•2w ago

/migrations
  /master
    /[...]
    /[...]
    /[...]
  /tenants
    /[...]

/migrations
  /master
    /[...]
    /[...]
    /[...]
  /tenants
    /[...]

when creating a new tenant, run drizzle migrate on migrations/tenants for the schema create the script for migrating all tenants you will need like 2? drizzle config files one for the master and one for the tenants basically "two" drizzle projects in the same repo

DeezyOP•2w ago

Got it! master would be for the global tables yeah?

Neto•2w ago

yeah

DeezyOP•2w ago

Thank you! I actually have a similar setup as it stands. But the script for migrating the tenants, I'd have to opt-out for drizzle for that one So it seems Thanks for the insight nyx! Do you mind if I drop a text should I have questions on the topic?

Neto•2w ago

sure if i can help

DeezyOP•2w ago

Copy that! Thank you! I'll try to make it work!

Gaming

Programming

Multi-Tenant Architecture