Use Transactions When Manually Accessing SQL Databases

Sometimes we find ourselves in a situation where we need to manually connect to the production database and change something. When we run into these situations we want to decrease the risk of causing problems as much as possible and this article will show you one way to do this: Transactions.

Normally SQL Transactions are a way to combine a bunch of related queries together into a single, transaction to the database. If any of them fail for any reason then nothing will be committed. We can use this same trick when manually working with a database, allowing us to make our changes, check our work, and only commit when we are sure that things are okay.

For this example let's set up a new Database. (I'll be using Postgres for this example, but I'll also be using the commands that are standard for SQL, they should work with other SQL based databases as long as they support Transactions). To begin with we'll create two tables as follows.

We'll create these tables using a Transaction in PSQL (The interactive shell for Postgres) this should work for your choice of interactive shell.

If you follow along, a couple of things to note. If you have an error with any of the commands in the Transaction, the entire Transaction will fail. You will need to run the ROLLBACK; command and then try again. We'll explore rolling back shortly.

postgres=# START TRANSACTION;
START TRANSACTION
postgres=*# CREATE TABLE species (
    id SERIAL PRIMARY KEY,
    name VARCHAR(10)
);
CREATE TABLE
postgres=*# CREATE TABLE pets (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    species_id INT REFERENCES species (id)
);
CREATE TABLE
postgres=*# COMMIT;
COMMIT
postgres=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | pets    | table | postgres
 public | species | table | postgres
(2 rows)

postgres=#

Note that in PSQL we are given a *= to show us that we aren't in normal mode, but inside a Transaction.

To finish the transaction and commit the changes to production we use the COMMIT; command. This immediately commits these changes to the database and are now available for use by other connections.

Let's try another transaction, but this time we'll make a mistake so that we have to rollback. We'll insert two pet's into our database, Xilbë the cat, and Ranger the dog.

postgres=# START TRANSACTION;
START TRANSACTION
postgres=*# INSERT INTO species (name) VALUES ('cat');
INSERT 0 1
postgres=*# INSERT INTO species (name) VALUES ('dog');
INSERT 0 1
postgres=*# table species;
 id | name
----+------
  1 | cat
  2 | dog
(2 rows)

postgres=*# INSERT INTO pets (name, species_id) VALUES ('Xilbë', 2);
INSERT 0 1
postgres=*# INSERT INTO pets (name, species_id) VALUES ('Ranger', 2);
INSERT 0 1
postgres=*# table pets;
 id |  name  | species_id
----+--------+------------
  1 | Xilbë  |          2
  2 | Ranger |          2
(2 rows)

postgres=*# SELECT species.name AS species_name, pets.name AS pet_name FROM pets JOIN species on species.id = pets.species_id;
 species_name | pet_name
--------------+----------
 dog          | Ranger
 dog          | Xilbë
(2 rows)

postgres=*# ROLLBACK;
ROLLBACK
postgres=# table species
postgres-# ;
 id | name
----+------
(0 rows)

postgres=# table pets;
 id | name | species_id
----+------+------------
(0 rows)

postgres=#

All of the commands were successful, but we made a data error that could only be caught by checking our work with a JOIN query. Running this allows us to catch that Xilbë has been entered as a Dog instead of a Cat. Xilbë would not be pleased so we rolled back the changes and therefore nothing happened in the database.

This shows the power of using Transactions in a manual setting, we can do our work, and then take time to check it all without having whatever we have done visible to any other connections to the database. Only when we are sure that we are ready do we commit it.