4 ms·
For the past few years, I abandoned the ORM and went straight to Stored Procedures using Data Transfer Objects / DAOs. It is no panacea, but has worked well for
by snomad 13y ago
For the past few years, I abandoned the ORM and went straight to Stored Procedures using Data Transfer Objects / DAOs. It is no panacea, but has worked well for these reasons:
1. I can grep all of my SQL! No dynamic SQL generation means all SQL statements are known. This alone has been huge to me. Of course it depends on storing the SPs in version control and settling on good naming conventions (eg. always write table.column).
2. The SPs are easily testable and verifiable endpoints to themselves.
3. Small Performance boost of using compiled / validated SQL.
4. No dynamic SQL also means that performance tuning is a snap. I see a bad actor I can track it down easily and fix the problem.
One thing that has made this possible is a convention of naming SPs by tablename_DMLname_[description]. e.g. customer_sel_byemail.
- jmnicolas 13y agoWhat is "DMLname" in "tablename_DMLname_[description]" ?
- sime 13y agoDML is short for data manipulation language as opposed to DDL which is data definition language. So in this case it's including in the stored proc name the type of data manipulation operation being performed by the proc. In the example "sel" is short for select so it's a query operation. You would also have insert, update and delete.
- 9876543210 13y agoA named query, or any other form of named SQL statement. It could be a view (a select query, stored as a database object) or a procedure (a grouped set of sql statements that might involve changes that are written to the database, possibly a simple insert, or simple update, or simple delete, but potentially much more complex). DML is one class of SQL statements, and DDL is another. The two together encompass most of your common, practical database operations.
- jmnicolas 13y agoGot it, thank you both.