Support for multiple databases in one app

[chris-hailstorm]

OBSERVATION

The current RedwoodJS data stack (Prisma schema, scaffold, SDL files, GraphQL, services, Prisma, migrations, DB) works well. Great labor-saver with a lot of intelligent defaults.

However it's not clear how to use this approach if there are multiple, independent DBs.

Not being able to use >1 physical DBs undershoots one of the main reasons for using GraphQL.

SCENARIO

Let's say your app has three distinct data sources:

• main app DB where user data resides (= the current Redwood DB)
• admin DB where topology, licensing, other non-user data resides
• other DBs for other purposes -- history etc. (I'm using AWS QLDB)

It would be ideal to support the same treatment for all DBs.

POSSIBLE SOLUTION

My current approach is to have a separate Redwood app that's 1:1 to each DB (main app, admin app, history app), and expose GraphQL operations for non-main DBs in the main app via custom GraphQL resolvers in the main app. In other words, three deployments and no Redwood-specific glue. Classic GraphQL use case: hide the implementation of the underlying service.

This works fine in terms of segregating the data and the migrations/etc. that support it. Not ideal in terms of having a whole app (build cycle, deployment etc.) -- the effort / build resources used at Netlify are 3x what they would be if all the DBs were addressed in some way by 1 app.

DISCUSSION

This is a tricky situation overall.

In some cases, there SHOULD be a synch between migrations in different DBs -- in my case the user DB and history DB are logically one DB (joint data model) but physically aren't because one is Postgres and the other is AWS QLDB (physical realization / deployment). For other devs and apps, the practical example could be the separation between app data and auth data, or app-aware data sharding, or something else. These cases MIGHT make sense to manage as two DBs in a single Redwood app if there's a practical way to do that.

In other cases, there should NOT be a synch between migrations in different DBs -- in my case, the admin DB has its own timeline -- could migrate the admin DB without migrating the user DB and/or history DB. These probably do NOT make sense to manage in a single Redwood app.

QUESTION

Is there a known-good way to manage >1 DB in a single Redwood app?

Any general recommendations congruent with the overall Redwood approach that support multiple backend services (including but not limited to DBs) via a single GraphQL server?

[thedavidprice]

Very intrigued by this @chris-hailstorm

Looping in @mojombo

[mojombo]

I definitely want to support multiple DBs in a single Redwood app. I'm guessing the way we'd do it would be to allow for multiple Prisma directories (maybe you call them db1 and db2) which you could configure in redwood.toml. Then in your services files, you just import one or both as needed, and use in the normal way. We'd need to scope DB-related CLI commands so you could choose which database to operate on, but that'd be doable.

For other types of databases (not using via Prisma), you can certainly just use them via whatever client you normally would, outside of any Redwood integration magic.

[thedavidprice]

I haven't tested this yet, but having multiple Prisma directories/schema-files looks very possible.

Generate Client for Multiple files

Use prisma generate --schema [pathToFile] to target a file: https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-schema/prisma-schema-file#naming

Specify Location of Generated Client...

The default location of the generated Prisma Client is node_modules/.prisma/client (as of beta4). I just ran a test, and regardless of the given name of a source schema file, it's always generated as schema.prisma in the same directory and overwrites the existing files.

But the generated Client location can be specified by using the output option in schema file, for example:

generator client {
  provider = "prisma-client-js"
  output   = "./prismaClients/dbOne/"
}

https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-client/generating-prisma-client#specifying-the-target-location-for-prisma-client

...and import accordingly in src/lib/db.js

Maybe something like:

import { PrismaClientOne } from '..prisma/clientOne'
import { PrismaClientTwo } from '..prisma/clientTwo'

export const dbOne = new PrismaClientOne()
export const dbTwo = new PrismaClientTwo()

Specify Migration Schema

I couldn't find it in the docs, but CLI help says it's possible to target a schema file with --schema.

 $ yarn prisma migrate --help
...

🏋️  Migrate your database with confidence

Usage

  With an existing schema.prisma:
  $ prisma migrate [command] [options] --experimental

  Or specify a schema:
👉  $ prisma migrate [command] [options] --experimental --schema=./schema.prisma

---

Am I missing any obvious, required step here?

I'm not sure if we can use Redwood config to handle the db.js imports. (TPW, maybe this is where the "Prisma Client wrapper" concept will come in handy?) But it seems doable for the yarn rw db commands to use config options for schema paths.

Woah. This would be rad. Right?

[chris-hailstorm]

This would be pretty rad because it allows the developer to make design changes at the DB level without changing much at the app level -- great for evolving / refactoring designs.

Example, you start out with one DB for everything. Later you decide you want or need a separate DB for auth, and a separate DB (or other service) for history / logging. The client-side app continues to use GraphQL with little or no change, depending on how well you can refactor. Anything on the server side that can use GraphQL is also unchanged. All the changes happen in the service layer.

The "universal interface" is a big reason for using GraphQL. It would be a mistake to have a 1 : 1 : 1 relationship for GraphQL API : service : DB (with or without Prisma). I think this would let you claim Redwood is built with support for app evolution / refactoring with minimal disruption. Big benefit.

[chris-hailstorm]

I suppose one way to do this would be to have an optional --db flag for generators and yarn rw db commands. The default DB ID would be blank and would equate to the current api/prisma folder and db connection variable. If the dev provides a DB ID like --db audit that would equate to an api/prisma.audit folder and a db_audit connection variable or something like that.

Would be invisible to devs who continue to use a single DB -- nothing changes for them.

Would be simple & obvious to devs who need to use >1 DB -- overall pattern is the same.

Probably the biggest challenge is handling migrations. What happens if the db migration succeeds and the db_audit migration fails? I think handling those oddities (and probably having to perform DB migrations separately, not in a 100% automated build) may be the price you have to pay for having >1 DBs. There will be no good way to provide all-or-nothing migrations for separate DBs / separate DB servers -- that becomes the dev's responsibility.

[thedavidprice]

I know we lean heavily into "convention over configuration", but this seems like a great case for redwood.toml settings. Probably a both/and in the end.

Re: migrations --> yep, sounds super hard (not very helpful, am I?)

[thedavidprice]

Relevant new Prisma Issue here: Multiple Connections / Databases #2443