I’ve been a database developer (among other skills) for most of my professional career. My first real code was an automated data update tool in Lotus 1-2-3 for a distributed phone directory database. I went on to cut my developer baby teeth in FoxPro, then grew up using Microsoft SQL. When I think about a new application, I think about the database early, if not actually first. So I have some biases…
I’ve used Code First principals when learning Ruby with a MongoDB database on the project, but I was too wrapped up in the newness to think about the impacts. Now I’m working through a course on MVC in ASP.Net, using Visual Studio and MS SQL, so I’m on familiar ground. And although the instructor gave reasoned arguments for why Code First is a good practice in his experience, as a database guy my first impression is that there are some smells.
My expectation, in essence, is that Code First database design is short-sighted:
- In a production environment with lots of real data, migrations can have a huge impact and down time on a SQL database. Applying Code First changes is not as agile as it sounds if you end up, say for example, vertically splitting a table with 10 million rows in order to create a one-to-many relationship on two new tables.
- SQL performance monitoring treats LINQ SQL calls as ad-hoc code, and re-compiles the queries every time. While the impact may be small, it makes it nearly impossible to use native or third-party performance profiling tools. These tools are optimized for tracking and analyzing performance loads using stored procedures.
- Code First binds the database model to the rest of the code, and impairs the ability to refactor the database code in the future for performance without touching that other code – e.g. partitioning a data table. It violates ACID principals in this way because it is not atomic.
- Code First puts the business logic further away from the data, and forces the data logic for connecting to the data up to the application tier. This could violate DRY principles as external applications are added… but even if it doesn’t, forcing connections through the higher tier may not be the most efficient or desirable solution. (That being said, if your application is built as a public facing web service, this may be less of an issue.)
- Related to #4: Code First assumes that database operations will be limited to CRUD actions. This ignores the power and flexibility of database level applications.
Of course I’ll follow the instructor as I continue through the course and try to keep an open mind on Code First database development, but decades of database experience tell my gut that Code First works better for early development than on-going support and maintenance. Unless you intend to ship your code and walk away, I’d look for alternative Agile Database design principles from an experience DBA/database developer and figure out how to apply them to your code practices. It may take longer up front, but will save headaches down the road.