To ORM or not to ORM

Object Relational Mapping (ORM) is a layer that sits between a service and the database used by the service. ORM follows its own dialect for operations – both DDL (Data Definition Language) as well as DML (Data Manipulation Language).?

DDLs are usually managed and referenced by the term migrations in context of an ORM. However, using an ORM requires certain design patterns to be followed across services in an organization, to avoid boilerplate of model definitions and ease of version control. At Agridence, when we segregated or developed a service, oftentimes, the models used were same, which caused us to emphasize and re-think our ORM strategy for a while. This article highlights some of the methods followed, their outcomes, and scope for future development.

Before we dive into detail, here are some highlights on the pros and cons of using an ORM from our use cases.?

Pros?

1. Versatility – Allows switching to other database engines without requiring major code changes.?
2. Ease of code maintenance – Since all models and their operations are defined in a standard manner, code maintenance is easier.?
3. Abstraction – By creating an abstraction layer between the database and service, a developer can focus entirely on service development.?
4. Ease of understanding – Unless one uses some LLM to explain SQL queries, their meaning and purpose can become quickly lost in translation. SQL queries with complex inner and outer joins require a steep learning curve for developers to collaborate and contribute.?

Cons?

1. Increasing migrations – While migrations are good to store database changelogs, they can also easily become a blocking factor if not handled carefully over time. This becomes particularly complex to handle when multiple feature branches are being independently developed touching common tables.?
2. Optimization – Most ORMs handle index and metadata table creations automatically for models created. However, without knowledge of underlying SQL, it is easy to reach performance bottlenecks arising from missing/incorrect indexes, incorrectly handled data volume considerations (for example geospatial or time-series data).?
3. Compatibility – Many ORMs deal with SQL or SQL like (those that accept a SQL dialect even if they are not SQL based) databases. In special cases where a No-SQL database is used, this is an extra overhead and not required.?
4. Model definitions – In an organization, as number of services are scaled up, each service may have requirements to perform operation to a common table. Doing this through an ORM means that model files need to be duplicated.?

?

Next, we highlight how we dealt with the above challenges and various scenarios encountered.?

1. More than one service requiring access to the same database?

Solution 1 – Repository controlled?

If both services are part of same source-controlled repository, models can be described at the top layer allowing both programs to inherit these definitions. This solution works well for coupled services such as including those including but not limited to sidecar patterns.?

Solution 2 – Decouple using an SDK pattern

Define models at a common place and import them as a library. This solution requires careful segregation of business logic and definition files. In this strategy, no service specific business logic should be embedded as the goal is to decouple model definitions from their behaviors. Depending on the programming language used and level of abstraction required when sharing implementations, this distribution can be an easy or slightly complex task to maintain.?

Solution 3 – Service responsibility segregation?

As much as possible, each service must own its own database and other services must interact with the master service to perform data operations instead of using an ORM to directly handle data. This segregation of responsibility results in cleaner code and data ownership. However proper versioning must be implemented by master service so that any changes do not cause immediate disruption in dependent nodes.?

2. Migrations management?

Solution – Remove unwanted migration files actively?

Migration files, if not managed carefully, can end up containing a changelog of entire history for how a codebase performed DDL changes to the underlying tables. While migrations in ORM are a standard and best-practice, one must carefully audit periodically to remove or combine ad-hoc and obsolete migrations.?

3. Schema design?

Solution – Documentation?

Documentation of schema chosen, and their relationships (entity relationship diagrams) are critical for not only explaining design choices to other team members but also helpful for collaboration and improvements. Any changes to this state must be updated to maintain correctness.?

4. Use of ORM?

Solution – Use case driven choice?

No-SQL based applications mostly utilize language specific SDKs and query methodologies without requiring an ORM. In such cases, point 3 above pertaining to documentation and strict version and access control becomes important. For example, consider a No-SQL scenario where incorrect schema management changed datatype of an integer to string, leading to multiple cascading failures.?

Some cloud-based databases offer flexibility of maintaining table schema and their versions on the cloud. This is particularly important for ETL, ELT and streaming data processing. In such cases, the use of an ORM is discretionary as the underlying data-tables may not necessarily be row-oriented.?

5. Understand the database type used?

Solution – Know your database?

Consider a scenario where the database is column-oriented and not row-oriented (for example Postgres). While column-oriented tables are optimized for columnar data access patterns, the same may take longer run durations and resources in row-oriented tables. Knowledge of underlying database (when using an ORM) helps developers in avoiding some common pitfalls associated with data query and processing.??