DEV Community: Nick Benoit

Measuring progress as a developer

Nick Benoit — Fri, 30 Oct 2020 00:20:26 +0000

Progress comes slowly, but still, if I think of where I was a year ago I am certain that I have come a long way. There are few days where I can close my laptop for the day without having learned something useful. Though I know I have picked up a lot of new skills, it is tough to quantify progress on one's journey to become a better developer.

So here is my question for the DEV community, how do you measure progress in your career as a developer?

My initial stab at answering this question is looking at the projects I lead at work. Early on, I mostly contributed small pieces to larger projects. In the past year the number and scope of things that I oversee has grown quite a bit.

Another approach is looking at skills. I have learned quite a bit about leveraging Redis and Mysql in the past year. Should I measure progress by looking at growing list of skills I could put on my resume?

Even in combination, these still don't seem to fully capture the idea of career progress to me. What do you think? How do you measure career growth?

3 Things I wish I knew about databases two years ago

Nick Benoit — Fri, 16 Oct 2020 12:04:45 +0000

Transactions

A database transaction is a way to logically group a series of database interactions into one functional unit. This unit will either get applied to the database, or it will get removed.

Lets look at an example of how transactions make our application more resilient. In this example, we run a grocery store rewards app. All members with 100 points automatically get sent a gift card, then get their points reset to 0.

So we do a query to find all grocery store rewards members with over 100 rewards points so we can automatically send them a thank you gift card. After we send the gift card, we should zero out the rewards points.

SELECT accounts.id, accounts.email FROM accounts JOIN rewards ON accounts.id=rewards.id WHERE rewards.points > 100;

Next our app creates some async jobs to send the gift card emails.

Finally, we update the database to reset the points of all the users.

UPDATE rewards SET rewards.points=0 WHERE rewards.points > 100;

So what could have gone wrong here? In my demo app it probably worked, right?

The problem case we could have seen here is called a 'dirty read'. Basically, we made a query on the rewards table to find all rows with points greater than 100. Then later, we did the same query again, and we expected it to be the same data.

If someone had checked out at the store, and gone from 99 -> 101 rewards points after we created the gift card jobs, but before we reset the points to zero, their points would have been zeroed out, but without receiving a giftcard.

Wrapping all of these interactions in transaction would have prevented this kind of behavior. The database would have guaranteed that the WHERE clause would have returned the same results on both reads.

Foreign Key Constraints

Relational databases are all about "relations", which in simple terms is relating rows in different database tables. We do this by doing a JOIN on some kind of key. Looking back at the last example, we did this JOIN rewards ON accounts.id=rewards.id.

A foreign key constraint is a database rule that makes sure that when you create a row in the rewards table, the id column must be the id of a valid row in the accounts table. This is great because it just gives us a bit more protection against accidentally filling up our database with bad data.

Row locking

When you think about concurrent writes to your database, I hope the hair stands up on the back of your neck just a bit. There are lots of things that can go wrong here, ranging from the subtle to the catastrophic.

Modern databases do a very good job of handling these various cases, but getting it right from an application perspective requires quite a bit of attention is being paid. Some databases like SQLite will not even allow concurrent writes from separate connections by default.

The particular case I want to call out here is where an update to your database depends on a previous read. If two threads are trying to do something like this, they have potential to wipe out each others updates.

Lets look at an example from our rewards program app.

# User 1 has 15 rewards points
Connection 1: Reads current rewards of 15
Connection 2: Reads current rewards of 15
Connection 1: Calculates new values should be 17, and updates the database
Connection 2:  Calculates new values should be 16, and updates the database

# Final value: User 1 has 16 rewards points

We can solve this problem by first, ensuring that this dependent read and write are within a single transaction, second, ensuring we have our database configured to use the required transaction isolation level, and three, using a FOR UPDATE clause with our SELECT statement.

Which might look like this:

SELECT id, points, rewards_id FROM rewards WHERE rewards_id=:rewards_id FOR UPDATE;

What the above does is maintain an exclusive lock on the row we have read from just like we have already made our update call. This prevents other connections from reading temporarily until we have either committed or rolled back our transaction. Now we can ensure that even if we do have two concurrent writers, they will not wipe out each others writes.

Conclusion

The moral of the story here is that modern databases can do a lot of neat stuff. It is worth putting in some time to learn some of those features, especially those that help prevent you from shooting yourself in the foot.

Now vs. Then: How I would build it now vs. how I would have built it way back when

Nick Benoit — Fri, 09 Oct 2020 13:39:58 +0000

Incremental progress is hard to measure. In an effort to reflect on the knowledge i've gained in the last year, I compare a naive implementation of a simple app I might have come up with a year or two ago, with how I would build it today.

Problem:

Design an application to keep track of and report tips for a restaurants wait staff

What should the schema look like?

Version 1:

CREATE TABLE waiters (
    id int,
    name varchar,
    tip_total int
);

In this version of the application, when a tip is collected, it is entered into the system. The row for the corresponding wait staff to include an updated total.

This SQL might look something like:

  SELECT *  FROM waiters WHERE id=:id;

  # Application logic calculates new values 
  # Something like: new_amount = result[2] + amount

 UPDATE waiters SET tip_total=:new_amount WHERE id=:id;

Reporting tips at the end of the night is then as simple as:

SELECT name, tip_total FROM waiters;

Then after all the tips are split up, I can go ahead and zero out the count to be ready for the next day. Easy as pie.

UPDATE waiters SET tip_total = 0;

But wait, what about waiters splitting tips for a table?

How about just two sequential select / update statements where splitting the update amount is managed by the application logic.

Version 2

CREATE TABLE waiters (
    id int,
    name varchar
);

CREATE TABLE tips (
    id int,
    staff_id int,
    currency_code varchar,
    amount int,
    time datetime,
    FOREIGN KEY (staff_id) REFERENCES waiters(id)
);

In this version of the application, when a tip is added to the system we will insert a row into the Tips table. Something like:

 INSERT INTO tips VALUES (1, :table, :usd, :now)

Reporting now becomes slightly more complicated. In order to report tips for a single member of the wait staff I need to:


SELECT SUM(amount), currency_code, CONVERT( Date, time) as d GROUP BY currency_code where staff_id = :staff_id AND d = :today

Which will give me a table of all currencies I accepted tips in, which I will then need to convert to USD in application logic.

What are the differences between these two approaches?

Certainly both versions are very far from being a real life production quality applications, but I'd like to dig into some of the reasons I believe version 2 is better.

Sins from most egregious to least:

By relying on deleting data to maintain an accurate count, we lose the ability to look back in time and do any kind of historical reporting.
By depending on getting the most up to date version of the tip total in order to calculate the new value, we introduce a nasty multiple writer race condition that could lose tips
We are trying to maintain an aggregate manually within the database. This limits our ability to do future reporting.
In the case of a split tip, version 1 doesn't use transactions. This means process death or an untimely exception could only partially apply a tip.

How version 2 implementation deals with these issues:

Some people probably think it seems goofy to even include this. I can guarantee that the version of me who had hardly touched a database would have thought it was fine, I mean hey, it fulfills the application requirements. The primary fault I see with this though is that even though it fulfills the requirements of the application right now, it is not very future proof. As soon as you ask the question, what is my average tips per night? You're kind of out of luck, the data is already gone. Another less substantiated down side is that deleting data is scary. If you get it wrong there's no getting it back, alternatively, if you push a version of your app with the wrong query, one patch version later you're back in business.
Arguably the most egregious sin introduced by maintaining this sum manually is the way we calculate it. We do a read, then use our application logic to update the column with a new number. What happens if two thread are concurrently trying to write?

Well.. if we're unlucky, something like this:

# Bob has $15 in tips
Thread 1: Reads current tip total of $15
Thread 2: Read current tip total of $15
Thread 1: Calculates new values should be $17, and updates the database
Thread 2:  Calculates new values should be $16, and updates the database

# Final value: Bob has $16 in tips

Uh oh... we just dropped the first $2 tip.

We could fix this by introducing some careful locking. Setting the transaction isolation level to REPEATABLE_READ and using a SELECT... FOR UPDATE would probably get us there. That is a lot of complexity that is easy to get wrong though. Let's just insert tips into their own table. Concurrent addition of rows to a table isn't going to cause us any grief. Databases can deal with that.

Databases are pretty good at generating aggregate statistics. Lets leave that to the database. We are maintaining our our SUM function, but what if we want the mean? Or the median? Let's keep the data in the database, and perform calculations based on the data.
My example didn't really highlight transactions too much, but I would say the biggest thing i've learned in the last year is that using transactions is important. In toy app land, it can be fairly easy to have avoid complicated queries and updates that whose consistency must be enforced. In real app land, queries tend to get complicated, and real users tend to have a way of finding those tricky edge cases. Transactions are a basic construct that lets you group interactions with the database. From what I sit currently, if you're doing a bunch of related things to your database, you probably ought to make sure they are grouped within the same transaction to ensure consistency of both reads and writes.

All in all, sometimes knowledge tends to sit around and accumulate without me really noticing. It is nice to sit back once in a while and reflect on some of that new stuff in your head.

Happy Coding