abitdodgy

Different apps, shared database, different schema... a good idea?

We would like to use a shared database for a number of separate applications. These apps are completely separate (separate repo, deploys, etc…). Many of these apps have interdependencies between their data. For example, an application for creating and sharing project estimates would need access to product data in a catalog app that furnishes an API. Also, all of these apps would need authentication, which would be handled by an identity app that keeps user data.

There are several approaches we can take:

Integrate the apps over APIs. I don’t like this idea because it might mean recreating tables and schemas in two separate databases and then worrying about keeping data in sync. For example, we might need to create a user and product tables and their schemas in both apps. Although, an API serves as a contract and could make the integrations more robust.
Keep all apps in an umbrella or monolith. I don’t like this option because these apps deal with different business concerns, and while they might share underlying data, they implement this data in completely different ways.
Separate apps, shared database. I don’t like this because, unless you are mad, you would need a dedicated app to manage your repo and migrations.
Separate apps, shared database, separate database schemas. This is my preferred option.

I prefer option four because it’s easier to work on and maintain separate applications. Separate schemas ensure that each app can manage its own database. But a shared database makes it easier reuse data, setup joins, and database constraints or maintain referential integrity. It’s as easy as joining a different prefix.

For example, an estimates.cart (schema.table) could have an owner_id foreign key that points to identity.user. And estimates.line_items can have a product_id column that could point to catalog.products.

Does this sound like a good idea? Has anyone done this type of architecture? What disadvantages (other than some schema data being shared between migrations) do you see?

Would you go down this route, or would you recreate data where needed? For example, multiple user schemas and so on…

14 comments

#ecto #architecture #schema

1 5761 14

2019-10-21 06:14:43 UTC

Most Liked

anthonator

We’ve done a combination of 2 and 4.

We have an umbrella app that uses a shared database and the data layer of each app is separated by Postgres schemas.

Our approach used the onion architecture. We have 3 different layers within our application.

infrastructure - This layer is where different infrastructural applications live. Our repo lives in this layer. An event bus. Etc.
context - We keep all of our business logic in this layer. These applications expose their functionality through a public interfaces that encapsulate the business processes for each application.
service - These applications are how the outside world interacts with our application. This is where our REST and GraphQL API’s live.

The dependency flow within our application looks like this.

[service] —> [context] —> [infrastructure]

This means that our service applications can directly interact with our context and infrastructure applications but they cannot directly interact with other service applications. Context applications can only directly interact with infrastructure applications and infrastructure applications don’t directly interact with anything. If information needs to be be passed to applications in the same layer or the layer above then we dispatch events using a bus. Applications in the same layer or the layer above will never depend on each other. This helps make the dependency chain and information flow between the various applications easier to reason about.

The context layer is where all the database querying and mutating happens. Each context application’s data is “isolated” from the other. I put isolated in quotes because they are separated by Postgres schemas. So it’s possible to query between the schemas but we have strict rules preventing that. Each application is responsible for it’s own data. Hard stop. If some piece of logic needs information that typically comes from another context application’s database then it’s queried for in a service application and passed in as an argument to a function. We essentially pretend that each Postgres schema is a separate physical database.

We’ve been using this approach for about 2 years and it’s worked very well for us. It’s made it easier to manage the application as it’s grown. It also promotes loose coupling which makes the application easier to change as well.

Post #2

al2o3cr

These two statements are in direct conflict: if you’re pulling records from a schema that belongs to another “separate app” you’ve created a coupling between the code that reads that data and the code that writes it. For instance, renaming a column in identity will have to be handled with coordinated deploys of every caller that uses the renamed field.

IMO this is a good use case for an umbrella app containing multiple Ecto.Repo applications.

Post #6

hauleth

What do you mean by the “shared database”? Is this “shared DB installation” or “shared DB within Postgres with separate Postgres Schemas”? If first one then I would say that this is almost no different from different DB instances on different machines.

Post #3

Last Post!

chouzar

Sounds like a current design I’m dealing with (just a toy project), basically I have an app that prints documents and a CRUD app which manages users. I need some of the user information to “stamp” the document and print it, so the document app contains both document and user schemas.

User App		Document App
		Document schema
User schema	—>	Sign Schema

Yes, this means that data is duplicated and inconsistent between apps; this is fine, the document app will retrieve user info and transform it into a “sign schema” which is later stored into its own document database.

For practical purposes all schema relationships will be kept exclusively to the bounds of the app, my only SQL joins will be between the “document” and “sign” tables. It may be a naive strategy for the medium or long term but I like how this design is settling down.

Post #15