Database + Application = Philosophy

Scenario: You want a database and an application. Sounds simple - and extremely common.

It should be simple, but too often I find that it is an over complicated part of the system - sometimes by the tools which claim to make it easier. i.e. Object Relational Mapper's (ORM's)

I have experience making a custom ORM from scratch as well as using off the shelf products: Entity framework, NHibernate and Subsonic.

Needless to say I am coming from the Microsoft stack - but these argument apply to whatever stack you are into.

They save you time by automatically implementing the data access layer. But I believe that it comes at a cost.

Why? Ultimately, it feels like there is an element of hype when it comes to using libraries. Inexperience will lead you to believe that they will solve all of your problems.

• What is Microsoft/JQuery/etc doing now? Whatever it is, it must be awesome! It must be the next big thing!
• What is the most downloaded library on Nuget? Everyone must be using it for a reason! I must have it too! I dont want to miss out!
• A library that does loads of stuff for me? that MUST be good!
• I won't need to touch the database if i use this library! WOW.
• I can get my application up and running faster!
• It can generate database upgrade scripts for me!
• I get compile time checking for my queries! My application won't break anymore!
• I can easily swap my database to another type!
• If I plug in these libraries it will promote good design in my software! After all, we are taught that everything must be designed properly, with nice diagrams and glossy documentation.

With these arguments going on its easy to get caught up in the hype.
There is of course truth to all of the above points, but... 

• Learning the ORM - takes time in itself - these libraries can be big! They are designed to work with different database types (SQL Server, Oracle, MySQL), multiple query options, configurable with XML files, edmx files or even more libraries like Fluent NHibernate. 
• Code bloat - you need lots more config.
• Unless you take care, you will end up being coupled to the library
• There will be bugs and features missing - you may end up waiting on 3rd party release schedules.
• You will have a large dependency on the library - which can make breaking changes with new versions.
• The SQL generated and sent to the server can be unpredictable. You will need to intercept these and check what is running being run on the database.
• Upgrade scripts generated by an ORM should be treated with extreme care. Only in the most trivial cases would I allow this to be executed and only then after a complete review.
• ORMs mean you don't need to write stored procedures
• less use of execution plans & slower performance.
• Can't secure the database as easily. 
• Cant develop or test database actions in isolation.
• Make changes (within reason) without having to rebuild the application
• You will avoid learning SQL. This would be a crime. SQL is so useful and isn't that hard either. It's more universal and stable than the ORMs. If your SQL is written by hand, you can make it efficient and easy to understand. If you do get problems with a query, it will be a lot easier to debug and fix manually written SQL than ORM generated SQL.
• Your code will more likely become infected with Linq (or equivalent). Can obviously be prevented by imposing the right design - but the temptation maybe too great.
• You will most likely still need stored procedures - for complex queries where linq etc just can't cut the mustard.
• Are you really going to change your database mid project? How long have you used your database system - are you actually thinking of scrapping it? I doubt it. That's potentially years of investment in the nuances of a single database type. Rewriting data access isn't (relatively) such a big deal if you are swapping the database. 
• How many tables do you have? is it really going to take that long to create a custom data access layer?

Essentially:

• More is not always better.
• If you are involved in data access, do yourself a favour: Learn ADO.NET and SQL.
• Treat database development as a completely separate role to application development. They are different beasts and I believe it will be a few years before we can control both using a single methodology. 
• ORM's still have a place. Don't discount them - just know when to use them and what their limitations are.

Whilst writing this blog post, I discovered blogs discussing the same subject:

• http://rlacovara.blogspot.co.uk/
• http://www.vertabelo.com/blog/technical-articles/orms-under-the-hood

More ORM related links:

• http://russellscottwalker.blogspot.co.uk/2013/10/active-record-vs-data-mapper.html
• https://dzone.com/articles/martin-fowler-orm-hate