Delt•17mo ago

Apps with one database per tenant

I was wondering if projects that require a database per tenant would be possible with Convex. Say for example a whitelabeled app or a school management app where each school has their own DB. It should be possible to update all databases with the same schema changes in one go. There should also be regular database backups. I know that Convex is very new and I'm asking for much 😅 Regardless, would be great to know where the team stands on this and whether there is any possibility of this being supported.

6 Replies

ian•17mo ago

I would personally put them all in one database until you’re sure you need them separate, but having one convex project per school is possible if it’s a small number of schools. Say more about the requirement to have them in separate databases vs. indexed by school? We do not have automatic backups at this time, but you can export a snapshot of the data or stream the data out using airbyte (or fivetran soon)

DeltOP•17mo ago

Thank you for your answer @ian. It would certainly be easiest to put them all in one database as you suggest. However, restoring data for a single tenant becomes complicated and is probably the biggest painpoint. Isolation is also important to prevent a tenants data being exposed to others if a dev writes an incorrect WHERE clause. I suppose a database-per-tenant setup could be built with Convex once it goes open source. Say I start out with a single database per tenant for now and then later migrate tenant data from the single db to individual databases enabled by a Convex fork, built by e.g. me, that supports db-per-tenant. About the backups, I guess it could be possible to automate the snapshot export and/or streaming of data using airbyte?

Michal Srb•17mo ago

Hey @Delt , I think you can definitely achieve this business use case with a single DB. I think so because I worked at FB, which built Workplace, FB for work, and served tens of thousands of businesses from the same DB (as you imagine business have also strict separation requirements like schools). To build this you could use an abstraction on top of the db interface we provide. We have a ton of examples of custom wrappers on https://stack.convex.dev/, for example here: https://stack.convex.dev/row-level-security, here: https://stack.convex.dev/wrappers-as-middleware-authentication and here: https://stack.convex.dev/convex-with-lucia. Say for database queries, it's paramount that your app always fetches data for a given school. To achieve this you can require all indexes to include the school_id field as their first indexed field. Then your wrapper can provide an amended db interface, which will force your developers to write instead of:

const students = await ctx.db
  .query("students")
  .withIndex("by_year", (q) =>
    q.eq("school_id", school_id).eq("year", year)
  )
  .collect();

const students = await ctx.db
  .query("students")
  .withIndex("by_year", (q) =>
    q.eq("school_id", school_id).eq("year", year)
  )
  .collect();

the following:

const students = await ctx.db
  .query("students", school_id, "by_year", (q) =>
    q.eq("year", year)
  )
  .collect();

const students = await ctx.db
  .query("students", school_id, "by_year", (q) =>
    q.eq("year", year)
  )
  .collect();

This enforces that queries always use an index, and that index always has school_id as its first field. You can similarly change the db.insert API to ensure a school_id is always specified. You can lint against the built-in API or wrap convex in your own internal package that only provides what you allow. As we (hopefully) build out our ORM primitives this will become gradually easier to achieve. This should satisfy the tenant requirements unless you're running against a rigid legal regulation. For export, yes, Airbyte should enable automatic backup.

DeltOP•17mo ago

Wonderful response! This was exactly what I needed, thank you. Now I can continue with Convex and continue to sleep well 😀

ian•17mo ago

You could even make the wrapper take in the school_id up front, so all of the exposed queries are already targeting a specific school

DeltOP•17mo ago

Great ideas, I'll try experimenting with this!

Apps with one database per tenant

Did you find this page helpful?