Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.
Sign up to join this community
Is saving SQL statements in a MySQL table for executing later a bad idea? The SQL statements will be ready for execution, i.e. there will be no parameters to swap or anything, for example DELETE FROM users WHERE id=1.
I guess I'm being lazy, but I thought of this idea because I'm working on a project that will require quite a few cron jobs that will execute SQL statements periodically.
It's safe, if that's what you're asking. As long as you're as careful about your security as you are with your data's security.
But don't reinvent the wheel, Stored Procedures ARE bits of SQL stored in a table. And they support, nay encourage, parameterisation.
Also note, you can make your security simpler AND reduce the number of points of failure AND reduce the network communications by using the MySql Event Scheduler instead of cron.
Other databases have equivalents to these, for good reason. You're not the first to need this functionality.
If you want to save SQL statements in a database for later execution, there's a better option than putting them in a table: use the built-in functionality provided for this specific purpose. Put them in stored procedures.
No, I would say it is not a good idea.
It means every "real" query/update will need 2 hits on the database - one to get the "real" query/update, and one to execute it.
This may not be a huge issue in a small system, but the more users/transactions your system gets, the less it will scale.
I am guessing this comes from looking for an alternative to embedding SQL in your code?
Encapsulating the SQL in a stored procedure as Mason Wheeler suggested, would be one way that be a lot better than this idea.
Sorry, I don't think that is a good idea.
Consider the case where the table in question changes. Say your id column gets renamed to new_id.
As well as the change itself, there is a version of your code written for an old vintage of the database that may no longer run. Sure, you might know to check in the code table but it isn't immediately obvious for someone else.
For a change such as I've described, I'd typically look at standalone SQL files, stored procedures, triggers, in-line SQL in client code (VB, C#, C++ etc) but the last place I'd think to look is in the database tables. Although maybe I will now! :)
There are valid reasons to store SQL in a table. Software I work with at work now includes a system for generating documents and the documents need to include fairly complex calculations using data in a database. The documents are updated frequently, are often significantly different in terms of the data needed, and the calculations that need to be performed. SQL is being used, basically, as a scripting language for performing these calculations and that SQL is stored in tables that also store other info for these documents. The people that maintain those documents are not programmers or DBAs, but they're familiar with the database schema and they're proficient with SQL. It would be significant overhead for them to maintain that SQL code in the form of stored procedures.
For what you described – "... a project that will require quite a few cron jobs that will execute SQL statements periodically" – the other answers are probably correct in suggesting that you used store procedures, or the equivalent, for the DBMS you're using.
A good criteria for deciding whether it's sensible to store SQL in a table versus in a stored procedure is determining who will be maintaining that SQL. If users maintain that SQL, i.e. they write it and change it whenever they want, then it may be perfectly fine, and indeed best, to store that SQL in a table. If developers will be maintaining that SQL, then it should be stored in a form that can be updated by your (hopefully automated) build and deployment procedures, e.g. stored as an object like a stored procedure in the database itself.
The easy way out would be to have an .ini or other configuration flavor file to keep the queries in. This way you can have them in one place, change anything without having to access the database to do so, not hit the database multiple times and also you may want/need at some point to use parameters or add conditions to your queries (augment them with more complex stuff for a particular case in your code).
Of course you can keep them at database level (either in a table or, better yet, as stored procedures), however if you're as lazy as I am ;-) but are also concerned with performance, an .ini file and PHP's beloved parse_ini method to read it, are the way to go (if you're going for another programming language, you're on your own figuring out similar approaches)! Usually I would read this file in the application's bootloader and keep it in a static object (maybe with a cache layer on top) to really speed things up if we're talking about a very large number of distinct queries.
If you only need the SQL statement once, for example, if you are queuing a bunch of operations to perform during off-hours, then yes, putting them into a table will work just fine.
If you need to execute the same SQL statement at specified intervals, then the stored procedure route others have mentioned is likely a better choice for you.
That said, what you are asking about is fairly unusual, and may be an indication of some other problem. For example, if you are waiting to delete a user from a database, it may be better to call a scheduled script that does the operation through your application code, rather than recording the actual SQL statements. This way the SQL and code are never out of sync.
