DEV Community

loading...

How to execute SQL?

tamasrev profile image Tamas Rev ・3 min read

People claim that it’s dangerous to generate and run SQL in the production. We should execute SQL in production carefully.

Wait! Why is it important to run SQL carefully? More importantly, how to run SQL carefully? It’s not about typing SELECT slowly, is it?

Why should we do it carefully?

If we do any change in production system, we should do it carefully. Whether we deploy new code, upgrade the operating system or run any SQL, we should not mess up the system that’s already up and running. Users don’t like downtime. If users can’t access our page that hurts business. So that’s why.

How can we make production changes carefully?

This carefulness falls into the following three categories:

  1. We should make sure that nothing goes wrong during the change.
  2. We should make sure that when something goes wrong, it has a minimal impact.
  3. When something goes wrong, we should have a plan how to fix it

Making sure nothing goes wrong

So, we should test our change before deploying it. In case of SQL we should have an identical database structure that we can play with. It’s even better to have a dataset realistically similar to that of the production database. Then we can run our stuff on that.

These kind of databases usually need a separate environment. Usually the companies are trying to spare those resources. So we might have a handful of multi-purpose databases. We better don’t mess them up, not even during testing. How can we do this? You can see my tricks in the following sections.

Trick 1: Testing the WHERE clause

Before running a DELETE or an UPDATE, we should run a SELECT with the same WHERE clause. So we know we’re targeting the desired dataset.

Trick 2: Using a transaction

We can run our stuff in a transaction. In Oracle SQL Developer we can simply turn off auto commit. So if we’re happy with our results, then we can push the commit button. If we aren’t then we push the rollback button.

Microsofts SQL Server Manager Studio doesn’t provide this functionality. But we can simulate it like this:

BEGIN TRANSACTION

-- do stuff

-- run SELECT-s to make sure we like the result

ROLLBACK TRANSACTION

Once we have the desired result, we can run it with a COMMIT instead of a ROLLBACK.

Trick 3: Having a backup of the test database

It’s good to have a backup of the test database. This can be a snapshot of a virtual machine, or a snapshot of the database files. So if everything else fails, you can always go back to the previous state.

Making sure the impact is minimal

There are several strategies to do risky stuff with minimal impact.

We can implement small changes. So the result should be small. Usually it means Continuous Integration, or maybe Continuous Delivery. Lots of automatic tests make it difficult to do stupid things on production. I don’t have much experience with those. As far as I heard, it requires lots of concentrated effort from the management and the technical teams to have Continuous Delivery. They say the problem itself if hard and there is no easy way around it. If you want it, then the whole company must figure out a way to do that in their business.

The Bank Card business are regulated by the PCI security standard. They must comply with a strict change management policy. So they roll out the changes in the off-hours. This is how they can have minimal impact.

Having a plan to fix it

This plan is usually about rollbacks and backups. In case of code change there must be a way to roll it back. In case of a database change, there must be a way to roll it back.

This means that before we go live with a change, we should test the rollback too.

Backups are problematic because the online traffic doesn’t stop for those couple of hours while you’re restoring the backups. Usually we should avoid this.

Wrapping up

  1. Try not to do ad-hoc changes on production databases.
  2. Test the WHERE clause before changing the data.
  3. Test the SQL commands with transactions.
  4. Test the changes on test systems first.
  5. Test the rollback too.
  6. Have a back-up.

What are your tricks?


Originally appeared at tamasrev.

Discussion

pic
Editor guide
Collapse
lluismf profile image
Lluís Josep Martínez

Oracle has a very interesting tool called flashback table: docs.oracle.com/cd/B19306_01/serve...

In the rare cases when users delete something by error and want to get it back, you can do it without restoring the full database.

Collapse
tamasrev profile image
Tamas Rev Author

Wow, I need this for SQL server...

Collapse
lluismf profile image
Lluís Josep Martínez

My trick is to forbid any non-developer type to touch the production DB with raw SQL. Most of the times they do it wrong, specially when the data model is complex and the fix involves several steps. Explain the problem to a developer and we'll build a console option (backdoor) to fix it.

Collapse
tamasrev profile image
Tamas Rev Author

Developers too can mess up SQL. Especially because SQL is easy to learn but hard to master.

Collapse
lluismf profile image
Lluís Josep Martínez

Of course, but at least we test the tool before putting it in production.

Collapse
carywreams profile image
Cary Reams

an EXPLAIN version of some statements can provide a "sniff test" from a different perspective.

Collapse
tamasrev profile image
Tamas Rev Author

Wow, adding to my toolbox.

Collapse
larswaechter profile image
Lars Wächter

Before I update or delete anything in SQL I comment out the command while I write it and check it carefully afterwards.