I’ve said this before and I think I’ll be saying it for a while yet, ColdFusion is not a database server, and should not be used as one. Or put differently, if your DBMS can do the job, let it do so.
Case in point: Earlier today I was giving someone a hand with an application, and was looking at some database interaction code. When I asked what the code was doing the developer explained that he needed datetime stamps for record creation and update. And so, his SQL INSERT and UPDATE statements (in
So what is wrong with that? Lots! For starters, there is no logical reason to manipulate those columns within CFML, ColdFusion does not need those values and does no special processing to obtain them. In addition, ColdFusion is not going to perform that processing as quickly as your DBMS would. Furthermore, the first time that rows are inserted or updated using a client other than ColdFusion things would break (or require rewriting). And that is just the start of it.
A better solution would be to let the database do the work, that is what DBMSs are designed to do. This particular application was using SQL Server, so I’ll use that in this example. The datetime stamp for record creation could simply use a DEFAULT of GETDATE() to set the date and time to the system date and time on record insertion. To set the update datetime stamp a trigger could be use so that any time a row is updated a SQL UPDATE statement would be issued to update the appropriate column.
That’s the right way to do it.
If you want to read more on this topic, see this recent CFDJ column: http://www.sys-con.com/story/?storyid=43786.
ColdFusion is NOT a DBMS
6 responses to “ColdFusion is NOT a DBMS”
-
I certainly agree in principle with what you’re saying, but it’s worth noting some caveats.
There are lots of benefits, in certain situations, to keeping things inside the application.
The most obvious is database portability. When moving from Access to SQL Server to Oracle to Posgres it’s often nice if your application won’t require any/many changes. If you really let the DB do all that it’s good at, that won’t be easy.
Another advantage is keeping "business logic" in one code base. I’ve seen applications that liberally mix stored procedures with custom tags/CFCs, and down the road it becomes a nightmare to make changes because the location of assumptions is spread across two pieces of software, and the stored procedures are rarely in version control with the CFML code.
In my experience, the majority of ColdFusion applications in the world use a database that only that application uses. Yes, that’s not something you can guarantee from now to eternity, but it’s the reality. Obviously, if your database is one used by ColdFusion and one or more other "clients" then your design decisions should definitely favor letting the database own most data-related issues. I’m just saying I don’t think it’s as clear cut as your post suggests in all cases. -
Nathan, I agree, there are exceptions (as noted in the CFDJ article I refer to in the post). But, I think that they are few and far between. And I see far more people err on the side of putting too much in ColdFusion than the opposite.
-
Nathan,
I agree and disagree with what you’re saying. I think there needs to be a balance between what’s in the application and in the database. However, 90% of the time the Database will be faster.
My solution to this problem has been to 1) use Mach-II for it’s object oriented framework and 2) create persistence objects which are the ONLY objects to touch the database or any other storage method (DB, XML, flat file, etc).
As long as these objects are the only things which touch the database and all they do is touch the database then if you need to port DBMS then all you need to do is update the queries in these objects. If for some reason your new DBMS system doesn’t support a feature then it becomes the responsibility of the persistence objects to provide that functionality. That way you never have to touch tested code (other than the persistence objects) and to move between platforms all you need to do is swap out the persistence objects.
Just my two cents.
Ben — I did have a question. I’ve never really used triggers, but I have a need. You specifically mention using a trigger to update a last updated field… for the life of me, I can’t figure out the syntax to do that. I can’t tell in a trigger what it is I’ve updated. Do you have an example?
Doug -
Doug,
The language of the trigger is dependant on the database you are using. For example, what works with Oracle will not work with SQL Server. -
Doug,
Here’s a very basic SQL Server Trigger:
CREATE TRIGGER authors_trig_ins_upd
ON authors
FOR INSERT, UPDATE
AS
/* Write Some T-SQL Here */
If you have SQL Server, search the "Books Online" collection for the term "CREATE TRIGGER" – there are a ton of usage examples. -
Well, it seems that I wasn’t quite clear with this. I know how to write triggers for MS SQL server. I just don’t know them very well. (I also sometimes assume that the whole world users the same database I’m using at any moment.) The thing I didn’t know how to do was, when you have an update trigger, to see the data that you’re updating. IE: in the insert and delete triggers you can draw on temporary insert and update tables with the data you’re working with. I couldn’t figure out how to do that with an update, as whenever I looked at either of these tables they were empty. That’s all.
Mental note: Never post to forta.com before coffee. Lots of Coffee.
Leave a Reply