If by “database” you mean “storage backend” (either in whole or in part), then the answer is oracle_fdw.
If you mean the API implementation, then, just as if you wanted to use a different language/platform when it wasn't implemented via Postgres originally, it's a complete reimplementation of that layer.
But, really, changing DBs isn't a root need, it's a solution, and unless we know the actual problem, we can't determine a solution (and “switch DBs to Oracle” likely isn't the best solution.
> How about complex logic that needs to be done in a declarative language such as SQL?
Complex logic is often more clearly expressed in a declarative language.
OTOH, to the extent there is a need for procedural/imperative logic, Postgres supports a variety of procedural languages, including Python.
> How about version control and updating views, with traceability?
There are a number of variations on approaches for this with database schemas in devops pipelines. Whether your schema is just base tables or includes views/triggers/etc. doesn't really make any difference here.
> Do you think SQL is more readable for logic code than say Python?
I think most appropriate of SQL, pl/pgSQL, and Python for each component is more readable than just-Python
> You're trying to use a tool (views), not for its intented purpose. It was not meant for sticking your company's entire domain model.
That’s...exactly what views were designed for. For a long time the implementations in most RDBMSs weren't fairly limited, but that's not really the case now.
> This is a completely wrong approach especially in enterprise environment. Might be ok with a small project.
Honestly, LOB apps in an enterprise environment is probably where this approach is most valuable. It might not be right for your core application in a startup where you are aiming for hockey stick growth. At least, from the complaints I've heard about horizontally scaling Postgres, I'd assume that.
I love this book and it explicitly addresses a lot of painpoints in designing complex systems. For example, business counterparts might request writing to CSV instead of database. You want to decouple domain models from RDBMS.
Let me raise a few questions for you:
- Let's say we want to change the database from postgres to Oracle in the future. How do I go about doing it?
- How about complex logic that needs to be done in a declarative language such as SQL? SQL was not developed to write logic. You could do a lot of things in it. It is turing complete but doesn't mean you should.
- How do you debug SQL views?
- How about version control and updating views, with traceability?
- Do you think SQL is more readable for logic code than say Python? Surely, basic logic can be represented in SQL. But IMO it suffers readability.
- How about CPU/memory consumption and how do you manage to vertically scale?
You're trying to use a tool (views), not for its intented purpose. It was not meant for sticking your company's entire domain model.
This is a completely wrong approach especially in enterprise environment. Might be ok with a small project.