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

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.

1. infrastructure - This layer is where different infrastructural applications live. Our repo lives in this layer. An event bus. Etc.
2. 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.
3. 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.

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.

I mean one database where each application has its own Postgresql schema.

Don’t you think that’s is a little different? You can query and join in between schemas, maintain foreign keys, and avoid having to duplicate data.

I can reach into the identity schema and grab a user, or join on its id column, instead of having to make an API request between two apps, grab the user data, and store it in a second db.

Do you declare the schema for each application using @schema_prefix? As far as I know, you can’t do:

  use Ecto.Repo,
    otp_app: :foo,
    schema_prefix: "some_app",
    adapter: Ecto.Adapters.Postgres

So how do you declare the schema within each app? Query time, or module attribute? I’m wondering it it’s worth opening an issue in Ecto to request a schema_prefix option in the Ecto.Repo macro.

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.

That’s true. I alluded to that in my original post. There would have to be some mechanism to ensure that these changes in coupled apps don’t create problems.

My problem with using an umbrella app is that we would have to build several web projects into the same application. We have to worry about assets, whether shared at the umbrella level or for each app, deploying apps on different ports, websockets, and other complexities that could arise, etc…

Another issue I have is that, while undoubtedly some of the data is shared, much of it is only relevant to the context of a single application. For example, we have a shipping api that needs user management, but none of its data is in any way relevant to the other applications. It receives weight, distance, and store, and spits out delivery methods with prices.

Great point, though.

In an umbrella project you can build releases with just a subset of applications included. So you can have all your different projects and their shared dependencies in an umbrella and still have their releases only include the parts they actually need.

The repo is shared between all applications so the prefix is declared in each schema.

We share the repo so we can use the same connection pool between each application.

How do you handle joins between schemas in different applications then? Or do you just not do that? It’s easy to couple everything with has_many and has_one and belongs_to.

We essentially pretend that each schema is a separate physical database. So we don’t do joins between schemas.