Testing SQL with transactions

Have you ever accidentally deleted the entire contents of a table and had to go explain to the DBA what you did and why they now need to spend their afternoon restoring everything from backups? If you say no then you obviously have never written SQL, because we've all done something similar at some point in our careers.

The thing is though there are simple ways to prevent this. For example restricting permissions for a user(type), to not giving direct access to production databases. More than that though what amazes me is that there is a safe simple way to test your SQL scripts which many new developers seem oblivious to.

I am of course talking about SQL transactions. By wrapping some potentially dangerous sql in a transaction block with a rollback we can test what the behavior of our scripts will be without actually committing those changes.

SQL Server

Here is the SQL server transaction syntax:

BEGIN TRANSACTION DELETE FROM dbo.users ROLLBACK TRANSACTION

MySQL/MariaDB

And here is the MySQL syntax:

START TRANSACTION; DELETE from users; ROLLBACK;

Now obviously there are other ways your sql could cause issues such as tying up resources etc, but at least with rolling back transactions you should not lose existing data. Please if you are a new developer tasked with writing sql OR a manager with a new junior developer make sure to go over transactions.

Further Uses

This is just scratching the surface of how useful and important transactions are and if you have never used them before then I urge you to go and learn about them as they are a truly powerful tool.

p.s.Other databases (e.g. PostGres) also have transactions I'm just not familiar what their syntax looks like.