Is it an anti-pattern to create ORM entities based on existing database schema?

Question

I maintain a large legacy app working with SQL database. The app uses raw SQL queries to query the database. Together with app rewrite I plan to introduce ORM to work with the database.

The simplest solution would be to keep the database in current state and build ORM mappings based on the existing database. It seems to be quite common approach, and many tools exist on different platforms to automatically reverse-engineer the database into the ORM (e.g. Django - inspectdb, MikroORM - Entity Generator, Entity Framework Scaffolding).

Another opinion however says that one should always design ORM entities first and create database schema based on them and that the reverse engineering idea is perverted and leads to enormous problems. I don't quite understand this. When the existing database is logically designed and normalized, how would it conceptually differ from ORM entities?

Some ideas that come to my mind:

• ORM may use its own naming conventions in the db. schema
• ORM may implement table relations (1:N, M:N) differently
• ORM may offer some functionality for which the existing schema isn't designed (e.g. entities inheritance)
• existing schema relies on database functionalities that ORM doesn't support (unusual data types, stored functions, triggers...)
• existing database tables aren't designed with OOP in mind, i.e. database entities don't correspond to programming language objects and their behaviour (but how? this is still vague for me)
• design of the existing database may assume that the data will be queried using raw SQL queries rather than much simpler ORM manipulation

I understand that ORM creates db. schema automatically and that the resulting schema is often a "black-box" for the developer. However building ORM on top of existing schema is still possible (the complications mentioned above aren't very common) and it seems to be better solution than break the whole database structure of existing app.

Is preserving the existing database worth even when doing complete app rewrite?

_{(The question is platform independent, however I am personally interested mainly in Node.js and PHP.)}

Answer 1

Reverse engineering was created for precisely your kind of situation. It was meant to be a time-saving tool when using an ORM with an existing database. The tool can do in a few minutes what a human (or team of humans) can do in hours or days, depending on the number of tables being mapped.

I prefer the database-first approach (reverse-engineering) to get that initial blob of data mapping code for existing databases. I will sometimes attempt retrofitting some model and mapping changes after the initial import for certain entities, but only if it would make the rest of my job easier.

I don't typically prefer continuing to reverse-engineer after the initial import. Existing databases tend to have their own idioms and patterns that don't necessarily fit the conventions that most ORMs come with. You can decide to spend time customizing the mappings or conventions. For example, FluentNHibernate in the .NET ecosystem provides a means to customize table and column names, types, etc. so you don't need to hard code "foo_id" in your primary key mappings when the ORM assumes the primary key is named simply id. As the database evolves, I prefer changing the mappings by hand, but that is just personal preference.

Ultimately the code-first or database-first approaches are just tools. You are free to weigh the benefits and drawbacks of using each one and decide for yourself on a database-by-database basis.

---

To be honest, I don't like code-first approaches, because the database tends to reflect an OO behavioral model rather than a relational data model. If I use the reverse-engineer tool, I tend to add new mappings by hand as the database structure evolves, but I'm a SQL guy. I speak just as fluently in SQL as I do in C#. Your experience might be different, though.

Answer 2

Both patterns are supported. Code first versus Model first. If you have an opiniated database administrator(s) or strong SQL skills one may choose Model first. If one has less SQL expertise, usually Code First is preferred since there is less interest in the database side of things.

If one already has a database, an analysis on the existing database schema is needed to see if it modeled properly from an entity standpoint as well as current business requirement support. This would be necessary before throwing it away. If it was not modeled properly to begin with, then starting over may be the best course of action.

As @JimmyJames has pointed out, most of these ORMs work best when the entities are defined in code and then the schema is "auto-magically" generated. Depending on how complex your existing model is, one may run into issues with your existing schema.

Answer 3

Neither is an anti pattern, they are just two different mindsets regarding database integration:

The application centric: The database is just a service for persisting application state. The role of the ORM is to make this persistence as transparent as possible for the application. The database schema reflect tha data structures of the application. The database is only accessed through the ORM.

The database centric: The database is the core of the business and multiple applications are built on top of this database. The database tend to live longer than any individual application. Each application may have a different data access strategy, and multiple different ORM's may be in use by different applications.

In the application-centric mindset, you adapt the database to the application. In the database-centric mindset, you adapt the application to the database.

ORM's tend fall into one of the mindsets. The ORM's which have grown out of web application frameworks tend to be application-centric, while the ORM's coming from the enterprise world tend to be database-centric.

Answer 4

Who "owns" the database? Is it your app? Or some other business? Do you have a continuity plan to support the old app while you develop new one?

Unless the database is for your app only, and you have easy way to migrate, do not touch the database schema.
Use an ORM, and decouple from database using Repository pattern.
What you return from repository is "your" app objects that should be oblivious to database existence. They may reflect database rows 1:1 or be totally different. It is a job of repository to persist them to database and populate them from database.
Usually, these objects will be saved to database 1:1, but sometimes you may need to do some 'magic' under the hood to make things easier, like gluing data together from 3 tables into one object, then persisting said object into three tables back.
The rest of your application should not be aware of the ORM - it should be contained in repository.

Answer 5

Starting with an implementation is an anti-pattern for software engineering.

Engineer your software to solve the business problem. If you are redesigning, redesign by starting with user stories and so on. Build your business objects from a pure data modelling standpoint. When you're done, choose how to implement that solution.

An ORM may be a very good way to do this (implement, that is), but the time to decide that is after you've determined the project specifications. Selecting the implementation before defining the model is premature optimization.

If you are simply retrofitting an ORM into an existing, working DB-based solution, that's not a rewrite. That's a retrofit and you may want to consider why you'd do that. In real-world (engineering) terms, that will cost a lot of money with relatively little return.

As for your related point about re-use of the existing data model, you absolutely should start with the existing data model in requirements gathering. Unless the business process that you're modelling has changed significantly, it will provide the purest source of truth of the actual object model. It is essentially, the best single source of truth for how a system supports the business.