When creating a new product, you have to be pragmatic and nimble. You have to be able to quickly iterate and change your product based on feedback. This means using the tools that get in your way the least and allow you to focus on the product itself.
When it comes to talking to a database this often means using an ORM library. An ORM library allows you to write code in a language that is familiar to you, and then it translates that code into SQL. This means you can write code like this:
// using prisma orm
const user = await prisma.user.create({
data: {
name: "John Doe",
email: "[email protected]",
},
});
And the ORM will automatically translate that into the SQL flavor required by the SQL database you are using, automatically taking care of everything like escaping strings and so on.
While this is great for getting started, I found (and we're getting into a small rant of mine against these tools but I'll keep this short) that the same thing that empowers you at the beginning is also the one holding you back once that first gates are passed.
Most of my coworkers until now had no idea about SQL beyond queries that look like this:
SELECT * FROM users WHERE email = '[email protected]';
This is of course not an issue in general to get the ball rolling, but it will become a problem in the (near) future if you don't pay attention to what's happening underneath.
Bear in mind that using an ORM doesn't prevent you from using transactions. Every single ORM I've seen/used has support for this and if it hasn't you should probably stay very far from it. 😬
As a result of this, and if your team doesn't have people knowledgeable about SQL, you might end up not knowing about the wonders of modern databases.
One such wonder is transactions. This is available under pretty much every relational database out there and is nothing new although it's often forgotten.
On consistency
Say Bob and Alice are avid readers. Alice has a bookshelf with a bunch of books on it and Bob really would love to buy one of Alice's books for his collection.
If we were to translate this into a set of SQL operations, it would look something like this:
ℹ️ This is a very simplified example, but it's enough to illustrate the point.
-- We're using names instead of ids for clarity
UPDATE wallet
SET balance = balance + 10
WHERE
username = 'Alice';
UPDATE wallet
SET balance = balance - 10
WHERE
username = 'Bob';
UPDATE books
SET owner = 'Bob'
WHERE
"bookTitle" = 'Bob Trianglepants'
AND "owner" = 'Alice';
Once these three operations have gone through, Bob's wallet has been debited by 10 and Alice's has been credited by 10. The book has also been transferred to Bob.
But what happens if for any reason the third operation fails? The money has been transferred from Bob to Alice but the book has not been transferred to Bob.
We can certainly agree that this would be an issue in real life
Alternatively, what if Bob was at the same time doing a transaction with Charlie? By the time we try to transfer the money to Alice, Bob might not have enough money in his wallet anymore. Of course, we could change the order of the operations to first transfer the money and then transfer the book, but this is not a solution either as Bob could then not have the money to pay Charlie instead.
To solve this, we need a way to make this whole set of operations happen as if it were just only one operation. If we were able to do that, we could be sure that once the operation goes through we will have the expected final state.
This is what we call an atomic operation.
In the context of databases, it is one of the ACID properties of a transaction.
Quoting wikipedia:
An atomic transaction is an indivisible and irreducible series of database operations such that either all occurs, or nothing occurs.
A guarantee of atomicity prevents updates to the database occurring only partially, which can cause greater problems than rejecting the whole series outright. As a consequence, the transaction cannot be observed to be in progress by another database client. At one moment in time, it has not yet happened, and at the next it has already occurred in whole (or nothing happened if the transaction was cancelled in progress).
In addition to ensuring that our data is always in the state we want, these guarantees also make reasoning about concurrent changes much easier
How does it work?
It's much easier than what you'd think. Let's take the example above and see how we can make it atomic.
START TRANSACTION; -- This is the equivalent of BEGIN
UPDATE wallet
SET balance = balance + 10
WHERE
username = 'Alice';
UPDATE wallet
SET balance = balance - 10
WHERE
username = 'Bob';
UPDATE books
SET owner = 'Bob'
WHERE
"bookTitle" = 'Bob Trianglepants'
AND "previousOwner" = 'Alice';
COMMIT;
The first thing we do is to start a transaction. This is done by calling the START TRANSACTION command.
If nothing more is done, this would have no impact. The transaction will show its benefits only once we start doing operations on the database.
But as soon as we start modifying data, the benefits of using transactions show up. Doing an UPDATE will apply a lock on the affected rows. The lock mechanism and the type of lock is outside of the scope of this article, but it's important to know that it exists.
These locks will ensure that no other query can modify the rows that are being modified by the current transaction. This will provide isolation.
By design, the effects of an incomplete transaction are not visible to other transactions.
If all statements go through, we will reach the COMMIT; statement. This will commit the transaction and make the changes permanent.
If any of the statements fail for any reason before reaching the COMMIT; one, the transaction will be rolled back and all the changes applied since the START TRANSACTION; will be discarded.
At any point in time, we can also call ROLLBACK; to rollback the transaction manually and discard all the changes.
Is that all?
Not exactly. Transactions allow for much more flexibility and come with a few implications.
But this is enough to get started and will cover most of your use cases.
Topics you might want to read about to dive deeper: