Best practice: When to use database views and stored procedures in cfwheels app?

[Thorsten Eilers]

Hi,

up to now I only uses the cfwheels ORM (70%) and custom queries (30%) in my cfhweel app.

Works fine.

The database guys tell me this is bad practice and I should use database views and stored procedures.
It would be more professional, faster, more secure and mantainable.

What is best practice in which cases to use database views and stored procedures in an cfwheels app in general?

What are the cons and pros?

Regards
Thorsten

[Singgih Cahyono]

Personally, for read-only query I don't care about the technology, as long as it is perform fast and familiar for the developer (including junior programmers).

For update query, I prefer ORM.

Consider this example, person.delete() versus "DELETE FROM person". The first one is more flexible, because if in the future, you decide to do soft delete, you need to change the code only in one place without changing the controllers.

What if you need to createdby field, by taking value from the current ColdFusion user? You need to put the code in one place too without changing the controllers.

What if you need validation (simple or complex relationships) ? You need to put the code in one place, without changing the controllers.

Consider this example, score.add() versus "INSERT INTO SCORE". The first one is more flexible as mentioned above.

Plus what if you need to archive the previous value as historical data? You need to put the code in one place, without changing the controllers.

What if you have several controllers calling the same method? You don't have to duplicate the SQL code on each controller.

The bottom line, if you use SQL for update query, you might end up with spaghetti code, less maintainable, less secure, less profesional, and probably slower.

Regards,

Singgih

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfwheels+u...@googlegroups.com.
To post to this group, send email to cfwh...@googlegroups.com.
Visit this group at http://groups.google.com/group/cfwheels.
For more options, visit https://groups.google.com/groups/opt_out.

[Chris Peters]

The answer to this question simply depends on your circumstances.

CFWheels generally follows a pattern of placing as much query logic as possible in your application layer. An extreme example would be to NEVER use stored procedures or views. The idea behind this is that it's just a hassle to have to hunt through the database to update every little bit of stored procedure logic when you need to make a change. I personally hate the tooling for working with databases, so I'm happy to keep it in CF. If you're developing a ColdFusion app, then ColdFusion is what you're focusing on. Why add unnecessary complexity by introducing another layer of code?

Of course, that extreme doesn't always make sense.

Personally, I would only mess with stored procedures if I was working with a DBA or database developer who does nothing but write stored procedures all day.

Your individual opinion on this matter could vary from mine, of course. :)

--

[Singgih Cahyono]

It depends you flavor of architecture as well.

Back in the client-server era you have only two choice to put data access codes: in the client or in the server.

Then came the three-tier era that bring more confusion on where to put data access codes: in the data, application or presentation tier.

I still saw people putting data access code in presentation tier until today (Not OK)

Nowadays, I prefer to use hexagonal a.k.a. domain-driven-design.

It means your client could be anything Desktop Web App, Mobile Web App, Mobile App, etc...

Also your database could be anything Database Server, In-Memory database for Unit testing, etc....

So, it is obvious that I should not put important code anywhere (client-side or database-side) except in the center (in my model).

Don't forget that data access code is not only CRUD operation, but might involves soft-delete, historical archiving, auditing, validation with message, etc...

There is one exception that I will use stored procedure.

It is for ETL (Extract Transform Load) operation, because it moves millions of rows and will be very slow to use model.

[Chris Peters]

Great point about the flexibility to swap out the storage layer if needed. But let's keep in mind that that is actually a minority case. Sure, some apps have millions of records, but many do not.

Again, this reinforces that it depends on your situation. :)

[Thorsten Eilers]

Thanks Singgih and Chris for your thoughts.
I will think about it a little bit more.
Thorsten

[Singgih Cahyono]

Also, wheels ORM is not any ORM. It has convention and dynamic methods that shrink your codes significantly compared to stored procedure.

[Singgih Cahyono]

One more to add. One can argue that ColdFusion is only a client, because the database has also Java or PHP clients, for example.
In that case, it might make sense to move all important codes into stored procedure.
But this style is so 70's and not fun. Modern SOA architecture prefers object oriented language to write the core domain. And the let any other clients connect via custom REST API, for example.
Personally, myself, I will go with the team. If they prefer database as the center, so be it. Otherwise, cfwheels models will be the center.