The decision of using an ORM library such as SQLAlchemy, Doctrine, django-orm, etc. or just choosing a database instead and using it with raw SQL queries is quite a considerable dilemma which many engineers face and requires much of pondering on a case to case basis.
Both approaches have their pros and cons. On one hand, ORM gives you the flexibility to replace the backend database at your whim any time in the project lifecycle. Did you make a mistake with SQLite and your transactions are growing now? No worries, just configure the MySQL database driver in your ORM and problem solved!
But on the other hand, it ties your hand and restricts you to just basic CRUD operations and you must work with certain assumptions when it comes to interacting with the database. Want to quickly extract the "month" part of a DateTime field and sort your reports according to it? Want to split a text or varchar field based on a certain character or convert a float to int? These seemingly simple tasks may require lots of Googling and sometimes even hackish workarounds depending on which ORM you are using! But with raw SQL queries, this becomes very easy as almost all the various dialects of SQLite, MySQL, PostgreSQL, etc. support a built-in function for doing such things.
And then there are those who find "in-between" solutions. They use raw SQL for these one-off tasks and keep that dialect specific code in a separate module or class so maintaining them becomes easier. Another approach is to have an additional "DB Layer" class or module to interact with your database instead of using an ORM.
And yet another consideration is speed and performance. As you start scaling your application in both size and number of concurrent users, you might feel tempted to get rid of the ORM overhead. But there are many large platforms which use ORM without feeling the performance brunt of it. Reddit, for example, still uses SQLAlchemy to the best of my knowledge.
Which approach do you prefer and why? Let me know in the comments below!
Top comments (0)