
I said the last SQL piece was experimental, but that was before it cruised into my top posts. You could point to the obscene clickbait title. That's both logical and entirely fair, but hear me out. SQL's not the most popular kid in school, and that air of mystique gave her a fighting chance... 

Either way, full-stack fun is back on the menu! For an introduction to SQL's time-bending nature, start with [Part I](https://alechorner.com/blog/post2).

Today, we dive deeper, covering the complex interactions that make SQL brilliant. We tackle the pros and cons, the power of `JOINS`, and the modularity of `UNION ALL`. But first, let's start with some Javascript.  

<Warning title="Intended audience">This post goes quite a bit deeper into SQLs main concepts and is intended as an _intermediate_ guide to using SQL in your development workflow.</Warning>

## The JS Approach

Let's start with a problem. We need to parse and track the net liquidity of the U.S. Federal Reserve. We have access to all required data but need to find the percent change between rows. 

> - FED Net Liquidity = (Federal Reserve Balance) - (Treasury General Account + Reverse Repo Agreements)
> - Don't worry about what these mean, but the process is an interesting (and deep) rabbit hole if you're so inclined. 

The initial data would look something like: 

```javascript=
const fedBalance = [
  // Your data goes here:
  { date: '2023-01-01', fed: 100, tga: 50, rrp: 30, net_liquidity: 70, spx: 200 },
  { date: '2023-01-02', fed: 110, tga: 55, rrp: 35, net_liquidity: 75, spx: 210 },
  // More data...
];
```
Remember, we need to find the percent change, or the difference between the current and proceeding rows, divided by the proceeding row, then multiplied by 100. JS offers a range of approaches, but `map` does the trick. 

```javascript=
const calculatePercentageChange = (prev, current) => {
  return ((current - prev) / prev) * 100;
};

const fedBalanceFull = fedBalance.map((row, index, arr) => {
  if (index === 0) {
    return {
      ...row,
      net_change_fed: null,
      net_change_tga: null,
      net_change_rrp: null,
      net_change_net_liquidity: null,
    };
  }

  const prevRow = arr[index - 1];

  return {
    ...row,
    net_change_fed: calculatePercentageChange(prevRow.fed, row.fed),
    net_change_tga: calculatePercentageChange(prevRow.tga, row.tga),
    net_change_rrp: calculatePercentageChange(prevRow.rrp, row.rrp),
    net_change_net_liquidity: calculatePercentageChange(prevRow.net_liquidity, row.net_liquidity),
  };
});
```

This gives us what we need, but it took a hefty dose of repetition to get there. It also adds another layer of complexity to your front and/or backend implementations, which should be limited by design. 

All this to say, I prefer formatting as much as possible before it hits my application.

## A Glance Through the Window

True to its name, SQL is a _querying_ language specifically designed to handle and manipulate large datasets. The below features one of SQL's heavy lifters: 

`Window functions` perform calculations across a set of rows related to the current row. Three main types exist:  
> 1. Aggregate, or function like `SUM()`, `MAX()`, or `COUNT()`
> 2. Ranking, or `RANK()` and `DENSE_RANK()`
> 3. Value-based, or functions like `LAG()` and `LEAD()`

A full rundown is outside this guide's scope but will be thoroughly covered in a follow-up post. For now, focus on `LAG()`. Note that `window functions` include an `OVER` clause, a `PARTITION BY,` and/or an `ORDER BY`. But the `OVER` clause distinguishes them from the average SQL query. 

As `window functions` are row-based operations, we must specify _how_ the data relates to the rows around it. `OVER` acts as that guide. In plain English, a SQL function lets us ask for data. A window function extends that, enabling us to ask for calculated data in a specific order.

Where `SUM(amount)` returns the total sum of an entire column in our dataset, `SUM(amount) OVER (ORDER BY date)` returns a row with the summed totals of each day. The below showcases `LAG(),` which finds the difference between a row and the row immediately preceding it. 

```sql=
CREATE TABLE IF NOT EXISTS fed_balance_full AS (
    SELECT date,
        fed,
        fed - LAG(fed, 1) over (ORDER BY date) AS net_change_fed,
        tga, 
        tga - LAG(tga, 1) over (ORDER BY date) AS net_change_tga, 
        rrp,
        rrp - LAG(rrp, 1) over (ORDER BY date) AS net_change_rrp,
        net_liquidity, 
        net_liquidity - LAG(net_liquidity, 1) over (ORDER BY date) AS net_change_net_liquidity, 
        spx, 
    FROM fed_balance
);
```

Here we ask for the difference between the current and preceeding rows, ordered by date. We get the same result, but less wordy and more human-readable. 

 I tested both operations. I ran the JS locally in VSCode and ran the query via a local-hosted PostgreSQL database on the same machine. The SQL took under 50 _milliseconds_ to process and deliver, while Javascript took around 0.253 seconds. So, the SQL query processed around 5.06 times faster than the equivalent JS.

The tests were (admittedly) isolated, but it's not all about speed and brevity. SQL handles complex queries, mutations, and interactions with outright class. 

## Recruit More Data

SQL's a maestro when it comes to manipulating and querying relational data. This becomes invaluable in operations that cross-reference multiple tables, allowing you to pull insights from seemingly disparate data. In a business context, it's often a unique ID that serves as the bridge between tables, linking, say, sales data to specific customers or salespersons.

For example, consider two tables, sales and salesTeam, where we link them via a salesId:

```sql= 
-- NOTE: While SQL offers a `money` data type, we use `numeric` 
-- here for compatibility with JavaScript data types in React.
CREATE TABLE IF NOT EXISTS sales (
    id varchar PRIMARY KEY, 
    salesDate date, 
    salesAmount numeric
);

CREATE TABLE IF NOT EXISTS salesTeam (
    salesId varchar REFERENCES sales(id),  
    salesDate date, 
    salesTotal numeric,
    PRIMARY KEY(salesId)
);

```
Use `JOIN` to find the matching records between two or more tables. We covered the basics of each type in the first [article](https://alechorner.com/blog/post2), but here we'll use `LEFT JOIN`. 

<Notice>To locate related records between our tables, we'll utilize `JOIN` operations, specifically `LEFT JOIN`. A `LEFT JOIN` merges all rows from the left table with matching records from the right table, based on a condition specified in the `ON` clause.</Notice>

In our two tables, `sales.salesId` matches the values held by `salesTeam.id`. We include this information in our `ON` to join the salesTeam table _on_ the `id` match. Now, let's get to joining:  

```sql=
SELECT sales.id, sales.salesDate, SUM(salesTotal) as netSales
FROM sales
LEFT JOIN salesTeam ON sales.id = salesTeam.salesId
GROUP BY 1, 2
ORDER BY 2 DESC
```

With this, we can query the total sales for each salesperson in a given period. 

### UNIONS 

Let's ramp things up. We've been cruising at ground level with flat queries, but it's time to soar. Enter SQL's `UNIONS` and nested queries.

SQL's `UNION` declaration joins multiple queries using related tables. SQL also allows for `nested` queries. Think of `nesting` like JS `for loops`. The comparison isn't 1:1, but the mental model is similar. 

For this, let's head to [Dune Analytics](https://dune.com/home). I said it before, but you don't need to be into crypto to appreciate Dune. It's the ideal spot to hone your SQL, as the entire site is one massive relational database, which you can query to your heart's content. 

We'll start with `UNIONS`, which you'll typically see as `UNION ALL`. 

> - `UNION ALL` literally stacks two or more result sets on top of each other, keeping duplicate rows.
> - `UNION` does the same but eliminates duplicate rows, which can be less efficient if you need those duplicates for your analysis.

It has myriad uses, but the below showcases my favorite. 

Now, hold on tight; we're diving deep. The query below utilizes each technique we've learned so far. At its core, it calculates the Total Value Locked (TVL) in a lending protocol by summing up deposits and subtracting withdrawals. Take a moment to absorb the data flow from the inner queries to the top-level aggregate.

It has numerous things going on: 

> 1. We assign a name to each query and subquery using `WITH`. 
> 2. We nest a `SELECT` query in our `FROM` clause.  
> 3. We merge the two queries with `UNION`. 

```sql=
-- Nesting in the `FROM` clause.
WITH borrows AS (
    SELECT dt, 
        project, 
        token,
        asset_symbol,
        sum(delta) as "TVL"
    FROM (
        SELECT DATE_TRUNC('day', evt_block_time) as dt,
            project, 
            asset_address as token,
            asset_symbol,
            sum(usd_value) as delta
        FROM lending.borrow
        GROUP BY 1, 2, 3, 4
        
        UNION ALL 
        
        SELECT DATE_TRUNC('day', evt_block_time) as dt,
            project, 
            asset_address as token,
            asset_symbol,
            -sum(usd_value) as delta -- negative for $ out 
        FROM lending.repay
        GROUP BY 1, 2, 3, 4
    -- Nested `FROM` clauses need to be aliased. 
    -- You can use `AS` or type away. 
    ) sub
    
    GROUP BY 1, 2, 3, 4
    ORDER BY 1 DESC
);
```

Now, we hit the true time savers. The above could likely be done in Javascript, but good luck. That's the beauty of SQL. It offers unparalleled efficiency when handling or performing complex computations on large datasets. 

We have a few moving pieces to work with here. 

The top-level `SELECT` aggregates the below queries to find the TVL (Total Value Locked), or the sum of all deposits minus any withdrawals. Our nested `FROM` names four columns in each query but focus on the last item.

> * `sum(usd_value) as delta`
> * `-sum(usd_value) as delta -- negative for $ out`

The result? The Total Value Locked, or the sum of all deposits minus withdrawals. It's a powerful metric in just a few lines of SQL, outperforming similar JS-based solutions in speed and simplicity.

## Wrapping Up 

If you're still with me, well done! I know it was a ton to absorb, but SQL gets simpler with time. SQL doesn't have many declarations, but the depth of each can be staggering. 

Fortunately, the majority of web projects require fewer intricate queries. But if complex data's your swing, nothing beats SQL.

Now, to quickly recap: 

> - We compared SQL to a Javascript approach
> - Introduced `window functions`, which allow for absurdly simple data sorting and manipulation
> - Covered complex querying through the use of `JOIN`
> - And learned the power of nested queries or SQLs modular `for` loop

So, what's next? We'll keep peeling back the layers of SQL, diving into stored procedures, triggers, and more advanced window functions. Feel free to message me on [Twitter](https://twitter.com/kiwi_daniels) if you have specific questions or scenarios you'd like covered. I'm more than happy to explore them in future posts!

In this ever-changing world of web development, keeping your skills sharp is crucial. If you're looking to gain an edge, particularly with data-heavy applications, the depth and power of SQL should not be underestimated.

Like CSS, love it or hate it, it's always there to help! 

Cheers!




An in-depth look into SQL queries that make complex data sorting a breeze. We'll cover everything from joins to nested queries. Read on!

SQL Saves Hours, Possibly Lives, Part II


The average `GET` request holds a few *kilobytes* of data, primarily consisting of headers. That's all it takes to fetch a database of questionably guarded data. It doesn't matter if you work on the front or back end, sensitive data should be handled with care. 

That said, I've noticed some confusion, and it's entirely understandable. I shared many of the same questions until I got into the nitty gritty of web security. In this article, we'll explore various algorithms and tackle when and why we use each solution. 

But first, let's cover a little history and define a few terms.  

## A (very) Brief History of Modern Cryptography

<Notice>
<p style={{ fontSize: "20px", fontWeight: "600" }}>Terms to Know</p>

1. **Ciphers** are algorithms used to encrypt and decrypt information.
2. **Encryption** is the process of scrambling data so that it can only be viewed by parties equipped with the correct key. 
3. **Hashing** is a one-way algorithm that produces a fixed-size string, or hash code, that appears entirely random. This hash is used to locate specific data, verify content, and ensure its integrity and authenticity.
4. **IV**, or initialization vector, is a random number merged with the secret key to generate a unique "session" key. This session key is then used to encrypt and decrypt data. 
</Notice>

If you already know your history or want to get coding, [click here](`https://alechorner.com/blog/intro-to-cryptograhpy#what-is-encryption`). That said, I think it's easier to appreciate the tools you have once you know where they came from! 

### From Caesar to Modern Ciphers

Ciphers date back to ancient times, but for the sake of modern history, let's fast-forward to the Caesar cipher. Think of it as the "grandparent" of modern cryptography.

I know, I know, I said modern history, but it'll be a brief dip into times past. Promise.

Caesar's cipher is simple. Each character in a message shifts along the alphabet at preset intervals. Say we wanted to encrypt the message, "His name was Robert Paulson". Using the Ceasar cipher with a shift of five characters, we get, `mnx sfrj bfx wtgjwy ufzqxts`.

Feel free to take a test drive below! 

<CaesarCipher />

The cipher works for personal or time-sensitive data but is a breeze to crack with modern technology. Which leads us to the age of connectivity.

#### Symmetric Keys: DES as the Trailblazer

The early '70s gave us DES (Data Encryption Standard). 

DES utilized a single symmetrical key for encryption and decryption. It laid the groundwork for what would become the modern encryption stack. 

For the first time, the cipher wasn't preset. 

#### Asymmetric Keys: The RSA Paradigm

The late '70s saw RSA (River-Shamir-Adleman) introduce the asymmetric method. With two keys—one for encryption and one for decryption—RSA added an extra layer of security. 

Think of them like mathematical puzzle pieces where the public key only fits with the correct private key.

<KeyDemo />

Unlike DES, it still sees heavy use today. Its asymmetry is particularly suited to applications heavily reliant on secure key exchange, like blockchains, email services, digital signatures, SSL/TLS for web security, and VPNs.

> - Quick Takeaway: DES laid the groundwork for symmetric encryption, while RSA advanced the field by introducing asymmetric methods for secure key pairing.

#### SSL & TLS Equip Us for the Digital Age

Fast-forward to the '90s, and SSL (Secure Sockets Layer) became the web standard. It saw regular use through 2014, and then along came TLS (Transport Security Layer).

TLS came equipped for the ever-morphing digital landscape. On the web, we can't assume honest intentions. Quite the opposite. TLS, also called a TLS handshake, enables both the sender and receiver to confirm the identity of the other before any data is transferred. 

> - Boiled down, TLS is a secret handshake between your machine and a web server, so all involved know they're among friends. 

Today, most sites are served as HTTPS, an implementation of TLS encryption atop the standard HTTP protocol. So, any site using HTTPS utilizes TLS to encrypt and decrypt information. 

#### AES Enables Security at Scale

AES, or the Advanced Encryption Standard, surfaced in 2000 and quickly became the go-to algorithm for myriad web applications. 

Like DES, it utilizes symmetric keys, meaning the user and sender employ the same decryption key. The "trustless" nature makes AES ideal for securing data in transit and at rest. Which leads us to 2023. 

AES and RSA still see heavy use in myriad applications. With its ties to TLS, AES reigns supreme for data transfer and storage, particularly on the backend. But the internet of today requires more finesse than days passed. 

Today, data is token-based. AES typically (not always, as we'll soon see), handles database storage and API data transfers, but that's only half the picture. What about the front end? 

#### The Age of Tokens

One of the areas where RSA shines is in token-based authentication systems like [JWT](https://www.npmjs.com/package/jsonwebtoken) (JSON Web Tokens) and [OAuth 2.0](https://oauth.net/2/). The model is slower than AES but well-suited to random key generation. 

> - **JWT** enables claims to be transmitted between two parties and can be encrypted for tamper-proofing. In JWT, RSA is often used to sign these tokens so that the recipient can trust the source and integrity of the content. 
> - **OAuth 2.0** is another authentication protocol commonly used for token-based authentication in modern web applications. In OAuth2.0, RSA is often used to generate secure tokens, particularly when `Authorization Code` flows are used. The protocol uses asymmetric cryptography, most often RSA, to ensure secure token exchange, even in environments where HTTPS may not be reliable.

JWT and OAuth 2.0 are specifically tailored for user authentication, but that's worthy of a separate post. Now, we only skimmed the surface, but we hit the major takeaway:

> Internet requests are encrypted via the TLS standard, which commonly uses the AES or RSA cipher. 

## What is Encryption? 

In web development, encryption enables the secure transfer of messages and data. To paint in broad strokes, let's say we make a `GET` request to the Pokemon API. The simplified flow looks like this: 

> 1. The user requests Dragonite data, initiating a TLS handshake with the server.
> 2. The server sends back its certificate, which includes its public key, for verification. 
> 3. After verifying, the user generates a pre-master secret, encrypts it with the server's public key, and sends it back to the server. 
> 5. The server decrypts the pre-master secret with its private key. Both parties then use this secret to independently generate the same session key.
> 6. Any further messages between the user and server flow through the freshly encrypted session key. 
> 7. The user receives the requested (and glorious) Dragonite data.

But that's enough talk. Let's look at some code. Node provides a default [`crypto`](https://nodejs.org/api/crypto.html) library covering most of our use cases. Alternatives with lower learning curves exist, but if we can avoid a dependency, then why not? 

First, we'll need to generate an encryption key. There are several ways to handle this, but I like using `openssl`.  

```bash=
$ openssl rand -hex 32
```

You'll see a long hex string. There's your random key. We'll add this to a `.env` or `.env.local` file to avoid accidentally showing it to the world. 

But how do we go about creating and maintaining our own encryption logic? 

```javascript=
import crypto from 'crypto'; 

const KEY = process.env.ENCRYPTION_KEY; 

export async function encryptData (message) {
    /* 
     Generate a 12-byte IV (Initialization Vector) using `randomBytes()`.
     This size is commonly recommended for the GCM mode of AES.
     
     Create a cipher using the AES-256-GCM algorithm with the help of 
     `createCipher()` function. We initialize it with the encryption `KEY`
     and the generated IV.
     
     // Note: I'm generating a random IV, but you'd typically need a method 
              to securely share it on the client side.
    */ 
    
    const iv = crypto.randomBytes(12); 
    const cipher = crypto.createCipheriv(
        "aes-256-gcm", 
        Buffer.from(KEY, "hex"),
        iv,
        { authTagLength: 16 }
    ); 
    
    /* 
     Encrypt the message using the cipher and output it as 
     hex-encoded data. Then retrieve the authentication tag, 
     which provides integrity and authenticity.
    */
    
    let encrypted = cipher.update(message, "utf-8", "hex");
    encrypted += cipher.final("hex"); 
    const authTag = cipher.getAuthTag().toString("hex"); 

    return {
        iv: iv.toString("hex"), 
        encryptedData: encrypted,
        authTag: authTag,
    }; 
}; 
```

But encryption's only half the story. At some point, we need to decrypt. Fortunately, it works much the same but reversed. As a quick example:  

```javascript=
export async function decryptData ({ iv, encryptedData, authTag }) {
    const decipher = crypto.createDecipheriv(
        "aes-256-gcm", 
        Buffer.from(KEY, "hex"), 
        Buffer.from(iv, "hex"), 
        { authTagLength: 16 }
    ); 
    
    decipher.setAuthTag(Buffer.from(authTag, "hex")); 
    
    let decrypted = decipher.update(encryptedData, "hex", "utf-8"); 
    decrypted += decipher.final("utf-8"); 

    return decrypted; 
}
```

## When Do We Use Encryption  

Encryption's major appeal stems from reversibility. Use encryption for any data that needs to be later decrypted, like payment information. You don't want the world to see it, but you may need to use it later.

Payment processing aside, there's plenty that needs encrypting. Chat applications encrypt messages end-to-end, ensuring only the sender and receiver can read them (I mean... ideally). Smart devices use it to encrypt the data flow from server to machine. 

Really, there's no end to the practical examples. It doesn't matter if the data's at rest or flowing free, it should be encrypted. Only decrypt when absolutely necessary. 

Algorithms like AES-GCM handle encryption and produce authentication tags (`authTag`) that ensure the data hasn't been tampered with, like digital seals. 

You can see those in action in the code above. 

## What is Hashing? 

Unlike encryption, the hashing process is irreversible. Once hashed, a message can't simply be unhashed. At first glance, that doesn't seem that useful, but most data should never see the light of day. Things like user credentials, social security numbers, and down the list. 

Hashing is perfect for these scenarios. As a developer, you have no need for a cleartext password. Instead, compare the incoming hash with the stored password hash. If they're the same, you'll know it. 

SHA-256 never ceases to amaze me. It's a hashing algorithm that returns 64 characters regardless of input length. Give it a try below! 

<SHA256Demo />

It doesn't matter if you type "hey" or paste in a transcript of every conversation ever held. The resulting hash will always be 64 characters long.

That's peak efficiency, and it's absolutely glowing. 

<Notice>Notice how each character change alters the entire string. "Dog" produces an entirely different result than "dog" or "Dog ". But you can see the issue. Once it goes into the blender, it's impossible to piece it back together (at least with current technology).</Notice> 

Alongside its irreversibility, hashing offers "data fingerprinting", integrity checking, and speedy lookups. We won't go too deeply into these categories, but we've already touched on the first two. 

In the above password example, I mentioned comparing the hash of the incoming password to the stored password hash. This functions as a validation and integrity check, as any slight deviation to the contents changes the hash entirely.  

## Just Add Salt

"Salting" is a term you've likely come across, typically regarding password protection. Salting is separate but tangential to hashing. It may seem a bit arcane at first. It sure did for me, but it's as simple as it is clever. 

Say a user inputs the plaintext password, `#password123`. If you hash this directly, anyone with the same password would end up with the same hash. This is problematic.

If shadowy super-coders got their hands on the hashed passwords, they could precompute hashes for common passwords to find matches. 

This is where we add a little salt. The "salting" process appends random data, known as salt, to the plaintext. So, the hash for `#password123` would be something like `#password123+random_salt`. This salt is randomly generated for each entry, so even if two users share a password, their hashes differ due to the unique salts. 

Here's how that looks in code: 

```javascript=
import crypto from 'crypto'; 

// Generate a 16 byte 'salt'
const salt = crypto.randomBytes(16).toString('hex'); 
const password = "#password123"; 

// Merge the password with the salt, then hash
const hashedPassword = crypto.pbkdf2Sync(
    password, salt, 1000, 64, 'sha512').toString('hex'); 
```

Then store the `salt` and `hashedPassword` in your database. This protects the data from reverse engineering and brute-force attacks. As each salt is unique, hashes need to be computed on a per-user level, making for an incredibly inefficient attack target. 


## Putting it All Together

To better encapsulate the concept, I built a little component depicting each part of the encryption process. Typically, the keys return as Objects or `Promise[Object]`, so the below strings have been converted to `base64` for your viewing pleasure.

> - Generate a public and private key. 
> - Enter a message into the `textarea` and hit 'Encrypt.' 
> - Then decrypt to view the unsalted result.
> - Finally, add salt to view your freshly seasoned message! 

<RSADemo />

## To Conclude 

> - TDLR: Encrypt data that needs to be read later and hash data that should never be read. 

Encryption and hashing are related but separate methods used to secure data on the web. Today, data flows via the TLS standard, allowing the sender and the receiver to verify one another before exchanging data. TLS commonly utilizes the AES cipher for encryption and RSA for secure key exchange. 

Encryption and hashing are related but separate methods used to secure data on the web. Today, data flows via the TLS standard, allowing the sender and the receiver to verify one another before exchanging data. TLS commonly utilizes the AES cipher for encryption and RSA for secure key exchange. 

Never pass sensitive information like passwords, payment data, and user credentials in plaintext. Instead, hash the data. Hashing is an irreversible process which scrambles incoming data and spits out a `base64` string, regardless of the length of the input. This hashed data is then salted, and both the hash and generated salt are sent to the server. The stored hash can then be compared against an incoming hash to confirm its validity. 

So, while encryption, salting, and hashing are related, they each play essential roles in the greater security process. 

Today, we covered the basics of web encryption using Node's default `crypto` library. To quickly recap:

> - We handled the creation of a unique encryption key.
> - We created a unique AES cipher and IV to encrypt and decrypt our data. 
> - We covered the hashing and salting process, ensuring unique data hashes and closing attack vectors. 
> - We watched the process from start to finish to better understand each step. 

With our feet sufficiently wet, the follow-up article dives into user authentication and the tokenized web! Until then, thanks for reading, scoffing, or just stopping by! 


Cheers! 

A deep dive into encryption, hashing, and cryptography in modern web development.

An introduction to encryption, hashing, and cryptography in modern web development.

An Interactive Guide to Web Cryptography

  
There's something about the fusion of modern and retro that has my inner designer feeling warm and fuzzy. Nostalgic is probably the right word. 

My design choices likely gave that away, but pixel art brings it out in full force. In that spirit, this article tackles two methods to bring those animations to life! 

Now, everything in frontend web development starts with a [box](https://alechorner.com/blog/the-zen-of-box-shadow) before cascading into bundles of neatly packed `divs`. Like `div` Tetris. Maintain that mental model as we dive into animating our whimsical, 8-bit sprites, as they work the exact same way.

## What's a Sprite? 

I used to love making "flip animations." I'd take a stack of Post-its, draw a stick figure on each, and make slight variations as I continued through the pile. Only when I rapidly flipped through them could I see the little animation I made. 

Those were, more or less, poorly drawn sprite sheets. A sprite sheet is a long strip containing each image in an animation. They look like this: 

![](https://i.imgur.com/4TPpb6y.png)

I source my sprites via two avenues. 

If I can make it myself, I use [Aseprite](https://www.aseprite.org/). Aesprite is like a simplified Photoshop specifically built to create 8-bit art. It's a fantastic tool and only requires a one-time purchase. That means no subscription model, which was my deciding factor. 

I'm new to pixel art, so anything more than "basic" is out of my wheelhouse. When I hit that inevitable wall, I turn to pre-made sheets, our second avenue.  

As sprites are typically used in game development, myriad sites exist to buy, sell, or open-source pixelized creations. Here are a few I've used myself, though a quick Google search showed dozens of options: 

> - [Open Game Art](https://opengameart.org/) is the largest and most active option. Free downloads range from full game assets to individual character models and actions.
> - [Kenney.nl](https://kenney.nl/assets) is smaller but requires far less sifting to find quality art than OGA. 

## Animating A Sprite Sheet

How do we "flip" between images in a sheet? We'll need a few `background` tricks, but first, let's get a feel for how it all works. Naturally, the aspect ratio is set in pixels, with 64x64 as the most common.

<MoveBox />

For a typical 64-bit sprite sheet, our box moves 64 pixels along the x-axis to reach the next image in the frame. This repeats until we hit the final frame, after which we reset the x-axis and begin from the top. Note that the above requires 112px per image due to upscaling. 

So, just like those flip animations, pixel art "moves" as we rapidly flip between images to create an animation. We have options here, but let's start with pure CSS. 

The simplest approach is `background`. [`Background`](https://developer.mozilla.org/en-US/docs/Web/CSS/background) is a short-hand property allowing us to set myriad background styles within a single property. 

For sprite animation, we're concerned with `background-image`, `background-position-x`, and `background-position-y`. These properties set our image URL and specify our alignment along the x and y-axes. 

We previously touched on [keyframes](https://alechorner.com/blog/building-a-custom-code-sandbox), but as a brief recap: 

> `keyframes` provide waypoints for our CSS to follow throughout the life-cycle of an animation sequence. This grants granular control over each step of the animation sequence.  

<SandboxWrapper template="react-ts" files={{
  '/App.tsx': 
`import "./styles.css";

export default function App() {
  return (
    <div className="App">
      <img />
    </div>
  );
}`,
'styles.css': 
`
.App {
  font-family: sans-serif;
  text-align: center;
}

img {
  height: 128px;
  width: 64px;
  border: 0;
  background: url("https://i.imgur.com/LJMWV6k.png") 0px 0px;
  animation: playFire 0.5s steps(6) infinite;
}

@keyframes playFire {
  from {
    background-position: 0 0;
  }
  to {
    background-position: -384px 0;
  }
}
`
}} />

We have life! But let's break that down. 

First, we set an empty image tag in our `App.tsx`. While we're using a React sandbox, the same could done using only HTML and CSS. 

We then set our image's `background` property to the image's URL, and start at the beginning of the image, as both the x and y-axis are set to `0px`. 

In `animation`, we set a variable name for our animation (`playFire`), which we'll target in our `keyframes`. It takes a half second to complete (`0.5s`), changes six times (`steps(6)`), and loops infinitely. 

Moving down to our `@keyframes`, we target our variable, `playFire`, and set our `start` and `stop` values. Last time, we passed `0%` and `100%`. When only two values are specified, `to` and `from` function the exact same way. As singular starts and stops.

Now, each frame in the animation is 128 pixels tall and 64 pixels wide. So, to shimmy through it, we'll need to move 64px along the x-axis. 

We start at `0 0`, or the edge of our sheet, and with each step, we move 64px along the x-axis. With six steps and 64 pixels per step, we'll shift a total of `384px`. 

But why negative? Setting positive movement along the x-axis scrolls through the length of the image. But the effect we're after is static movement, so we invert that by _pulling_ the image towards us. This keeps the sprites in sync and maintains our horizontal positioning.  

The end result? Our `playFire` animation takes a half second to complete, loops infinitely, and moves -64px across six images, leaving us with `-384px` of horizontal movement.  

## Animating An Image Set with React 

Sprite sheets are our simplest method, as we have everything along the same strip. But what if we have a folder with an image file for each animation step? 

We'll need some React `state` for this approach, but the good news is the CSS is far simpler. It also lends a bit more control over the end result. 

This is the method I chose for the fire on my front page. We'll cover the pros and cons of each, but I don't see a massive difference with pixel art animations. The file sizes are inconsequential, and the medium doesn't mind slight imperfections. We'll leave that to the realists. 

```tsx=
import styles from './Campfire.module.css'; 

// we'll assume these are held in public/assets
const frames = [
  '/assets/fire/fire001.png',
  '/assets/fire/fire002.png',
  '/assets/fire/fire003.png',
  '/assets/fire/fire004.png',
  '/assets/fire/fire005.png',
  '/assets/fire/fire006.png',
  '/assets/fire/fire007.png',
]; 

export default function CampfireAnimation() {
  const [currentFrame, setCurrentFrame] = useState(0);

  useEffect(() => {
    const timer = setInterval(() => {
      setCurrentFrame((prevFrame) => (prevFrame + 1) % frames.length);
    }, 125); 

    return () => clearInterval(timer);
  }, []);

  return (
    <div>
      {frames.map((frame, index) => (
        <img
          className={`${styles["campfire-frame"]} ${
            currentFrame === index ? styles.active : ""
          }`}
          src={frame}
          alt="campfire frame"
          key={index}
        />
      ))}
    </div>
  );
}
```

The result is the same but a little more intuitive to implement. We still use CSS animations but leverage adaptive classes to simplify the process. 

> - Store the file paths in an array. 
> - Set our initial `state` to 0. 
> - `useEffect` to set an interval denoting how long the animation runs. In the above, we cycle to the next frame every 125 milliseconds. Be sure to clear this figure in your `cleanup function`. 
> - We then map through these frames, granting us the illusion of movement. 

The CSS really only has two moving parts. We want to begin with each frame as `display: none`. This ensures we don't render the images out of order. It also allows us to set `display: block` on the active frame, displaying only the "active" image. 

```css=
.campfire-frame {
  display: none;
  width: 300px;
  height: 400px;
}

/* Active frame */
.campfire-frame.active {
  display: block;
}
```

<div style={{ width: "100%", display: "flex", justifyContent: "center", alignItems: "center" }}>
<CampfireAnimationSmall />
</div>

## Performance

I was asked which method is more performant (read: sleek, agile, insert your preferred performance buzzword), and I didn't have an answer. After testing, both come with pros and cons. 

CSS is the move for pure performance. Relative to the React approach, initial load times take a hit, but once rendered the browser takes the wheel. So, our animation never re-renders, and the result is typically smoother and less CPU-intensive. 

The CSS approach really only comes with one trade-off. It's far less flexible. 

With React, we can change speeds, direction, or any other aspect of the animation dynamically. In other words, React lets you color outside the box. You aren't married to the initial movement as it can be tweaked to your liking. 

React also enables conditional and dynamic loading. One lovely example is the [Firewatch](https://www.firewatchgame.com/) parallax effect. Note that a similar effect can be created with pure CSS, but React's event handlers make for a smoother, more refined user experience. 

That said, the React approach has its cons. For starters, the element re-renders with each change in state or image "flip". At face value, we take a slight performance hit. 

Fortunately, we don't have to take it at face value. Profiler shows each render takes less than 0.1ms and only re-renders the image component. 

We're also situated near the bottom of the waterfall, so the hit to our performance is negligible. I'd be interested to see if the same holds true on mobile, but another time. 

## To Conclude

> - Pure CSS has slower initial load times, but once the animation renders, it will never trigger a re-render. That said, some animation effects require complex logic not possible in CSS. 
> - React loads faster but causes a component-level re-render with each image change. Each render takes under 0.1ms in a development environment, so any hits to performance are negligible. React is far more modular than CSS, which enables you to alter or restyle your animations.

I feel like I always say this. Give both approaches a shot, and see which suits you best! 

I use the React approach in my banner. The ability to quickly resize and change speeds on the fly was worth more than the minor uptick in performance. But your needs may be different! 

Some animations might not need the complex interactions React brings to the table. For those instances, rock pure CSS. If you're looking for more granular control, the React approach might serve you better. 

Either way, thanks for reading! Hopefully, you enjoyed and learned enough to incorporate your favorite pixel animations into your work! 



A deep dive into several methods of animating pixel art using CSS and React.

Animating Pixel Art & Sprites with CSS

Animating Sprites with CSS and React


No one likes a shifting layout. Designers hate that it decimates the first impression they worked hard to craft. Frequent users hate it because it stalls their standard interactions. And I hate it because the Lighthouse score knocks my delicate ego. 

Over the years, one CSS element caused 75 percent of my layout-shifting woes. That element is `border.` I use them frequently, but they're delicate, particularly on hover. 

Then I found `box-shadow`, and it took me in like a siren. Here's the fun bit. `Box-shadow` causes _zero_ layout shift. None at all. Let me demonstrate.

<SandboxWrapper template="react-ts" files={{
"/App.tsx": 
`import "./styles.css";
import React from "react";

export default function App({ border = "1", boxShadow = "1" }) {
  return (
    <div className="App">
      <h1 style={{ padding: "5px" }}>My Neat Blog</h1>
      <div className="card-container">
        <div className="card">
          <span>Border</span>
        </div>
        <div className="card2">
          <span>Box-Shadow</span>
        </div>
      </div>
      <div className="content">
        <h2> My neat blog content </h2>
      </div>
    </div>
  );
}
`,
"styles.css": 
`
.App {
  font-family: sans-serif;
  text-align: center;
  width: 100%;
  background-color: #1f2028;
  color: #f5f5f5;
}

.component-container {
  --shadow: 0;
  --border: 0px 0px rgba(125, 55, 25, 1);
  max-width: 365px;
  padding: 5px;
}

.card-container {
  display: grid;
  grid-template-columns: repeat(2, minmax(0, 1fr));
  width: fit-content;
  gap: 40px;
  margin: 0 auto;
}

.card,
.card2 {
  display: flex;
  align-items: center;
  justify-content: center;
  height: 150px;
  width: 150px;
  border: 2px solid black;
  border-radius: 0.55rem;
  margin-bottom: 20px;
  /* box-sizing: border-box; */
}

.card:hover {
  border: 4px solid aliceblue;
}

.card2:hover {
  box-shadow: 0 0 2px 4px dodgerblue;
}

.content {
  width: 100%;
  display: flex; 
  align-items: center; 
  justify-content: center;
}

@media screen and (max-width: 768px) {
  .card-container {
    display: flex !important; 
    flex-direction: column;
  }
}
`
}}/>

Now, I'll be entirely honest here. When I discovered this, I had no earthly idea _why_ the two behaved differently. It turned out quite a bit was at play.

`Borders` aren't simply broken. We'll cover how to fix that ugly shift, but we'll also explore `box-shadow` and the pros and cons of the two solutions. 

Let's start by breaking down the `box-shadow` CSS element. CSS offers several short-hand properties that apply myriad styles within a single variable. Variables like `border`, `animation`, `margin`, and `box-shadow.`

## The Nitty Gritty of Box Shadow

`Box-shadow` aggregates five properties: 

> 1. `offset-x` moves the shadow along the x-axis.
> 2. `offset-y` advances the shadow along the y-axis. 
> 3. `blur-radius` defines how much the shadow blurs around its edges.
> 4. `spread-radius` increases the size of the blur, making the shadow larger and brighter.  
> 5. `color` is (surprisingly) our shadow's color. 

Like `border`, any of these properties can be excluded. For our purposes, CSS interprets `box-shadow` as follows: 

> - If two values are provided, CSS interprets them as `offset-x` and `offset-y`. 
> - If a third value is present, it represents the `blur-radius`. 
> - And if a fourth is included, it defines our `spread-radius`.

Say we had `box-shadow: 0 0 2px 2px red;`. Without x or y-axis offsets (set as 0 0), our shadow sits directly behind our element. It's slightly blurred and extends 2px beyond the edges of its target. 

This syntax enables a nifty little maneuver. What if we only set `spread-radius` and `color`? The result is, more or less, the same as a `border,` a flat line that encompasses our element. 

There is a slight visual difference. See if you can spot it. 

<BorderVBox />

A CSS `border` "grows" inwards, while a `box-shadow` expands outwards. That makes sense, but it's not something I'd considered. However, that alone doesn't explain why they behave differently. 

The next section covers the differences in full, but before we move on, let's cover some of `box-shadow's` advantages and disadvantages. The advantages mostly funnel into choice of design, but there is one major performance uptick. 

<ShadowCard 
  text={"A `box-shadow` creates the depth and deminsionality that a `border` cannot."} 
  text2={"They add contrast to visually separate two similar elements."}
  text3={"Or style them to mimic the appearance of a border, without altering the element's size."}
/> 

Most importantly, `box-shadows` don't trigger geometry or layout changes. This includes neighboring components, as well as the inner contents of the shadowed element. If you're a blogger, that means no moving pictures or squashed text on hover!   

Most of the "advantages" are cosmetic (not all, just most!). Unfortunately, its disadvantages _can_ negatively affect performance. Like all CSS paints and animations, rendering `box-shadows` can be costly. Even more so for large elements, as the browser may force re-paints while scrolling.

At least, that's what articles from 2018 kept telling me. But nowadays, most of that is offloaded to the GPU, so your animations aren't as nearly as power-hungry as they used to be. There's such a thing as too much, but your inner designer, or real designer, will stop you before it's noticeable. 

That said, the same goes for _any_ CSS animation. If you're using changing `box-shadow` or `border` on hover, similar to the above, then the differences in performance are negligible. 

## The Anatomy of Border and Border-box

As HTML tends to go, it starts with a `div`. A `div` doesn't begin with any inherent border, and if one is applied, it lands on the `div's` outer edge by default. It might be a "me" thing, but this feels wrong. This quote by Peter Paul Koch says it better than I could. 

> "Logically, a box is measured from border to border. Take a physical box, any box. Put something in it that is distinctly smaller than the box. Ask anyone to measure the width of the box. He'll measure the distance between the sides of the box (the 'borders'). No one will think of measuring the content of the box."

Web development is mostly just a bunch of boxes. We size boxes, shape boxes, and then piece them together to craft a functioning interface. We aren't always concerned with measurements of the contents of these boxes, but we always consider the actual dimensions of said box.

To remedy this, we'll utilize one of CSS's "newer" features: `box-sizing: border-box;`. Remember, `divs` have no border by default.`Border-box` creates this inner `edge` on which to place one. 

Back up to our earlier example, and uncomment the `box-sizing` line to see its effect live. That said, this alleviates one issue but not both. `Border-box` maintains the layout of its surrounding neighbors, but as `borders` grow inward, it still affects the sizing of its inner contents. 

So, we're still left with the component-level shift. It's lightyears ahead of the horrors of shifting neighbors, but it still feels lacking. 

Note that we can correct this as well, but that's beyond the scope of this article. We'll cover these `pseudo classes` in a follow-up tutorial. 

## Should I Use Box-shadow or Border? 

The short answer is to use whichever you prefer! 

After a good deal of testing, neither is "better." I use both, as both have their uses. For instance, my article cards have an overlay showing the post category. I feel `box-shadow` works best here, as the card itself is small, and I'd rather not risk the text getting squashed or moved if some condition occurs that I didn't consider.

The same goes for the article cards themselves. I prefer the aesthetic and didn't notice a performance difference when using `box-sizing`. But at the end of the day, development is about creation. What I think looks fantastic could be the sloppiest design you've seen in your entire life. 

So, try out both approaches and find which one suits you. Worse comes to worst, at least you added a new tool to the 'ol box. 

But if you ever find yourself in need of some layering, `box-shadow` has a couple tricks to spare. 

## Box-shadow's Roads Less Traveled

`Border` only let's us define a singular border. `Box-shadow` lets you run wild. 

```css=
box-shadow: 2px 4px 0 4px hsl(230deg, 100%, 69%), 
            4px 8px 0 8px hsl(310, 68%, 70%), 
            8px 10px 0 12px hsl(170, 38%, 48%);
```

<MultiShadowCard text={`"You miss 100% of the shots you don't take. — Wanye Gretzsky — Michael Scott"`} />

You might be wondering what exactly that's useful for. Honestly, I have no idea. You could play with the opacity to add a bit more depth, and it's certainly worth a look if you're ever tasked with building drag-and-drop-styled card components. 

Either way, hopefully, someone out there sees potential in this strange multi-shadow card jutsu <small>(I cut this and put it back around ten times. I'm weak)</small>.

We typically use `box-shadows` to add depth behind an element. These drop shadows add an illusion of dimensionality to an otherwise static webpage, making it appear as if our element casts a shadow.

These layered-shadows offer maximum _lift_, but what if need _depth_? While `offset` moves our shadow along the x or y-axis, `inset` sets the shadow _inside_ the frame. Like all `box-shadows` these can be chained. 

<SandboxWrapper template="react-ts" files={{
"App.tsx": 
`
import "./styles.css";
import MultiShadowCard from "./MultiShadowCard";
import InsetShadowCard from "./InsetShadowCard";

export default function App() {
  return (
    <div className="App">
      <div className="card-container">
        <MultiShadowCard text={"Multiple shadows for maximum lift."} />
        <InsetShadowCard text={"Inset for maximum depth."} />
      </div>
    </div>
  );
}
`,
"/InsetShadowCard.tsx": `
import "./styles.css";
import React from "react";

const InsetShadowCard = ({ text }) => {
  return (
    <div
      style={{
        marginTop: "20px",
        marginBottom: "40px"
      }}
      className={"inset-shadow-container"}
    >
      <div className={"shadow-card-inset"}> 
        <p>{text}</p>
      </div>
    </div>
  );
};

export default InsetShadowCard;
`,
"/MultiShadowCard.tsx": `
import "./styles.css";
import React from "react";

const MultiShadowCard = ({ text }) => {
  return (
    <div
      style={{
        marginTop: "20px",
        marginBottom: "40px"
      }}
      className={"multi-shadow-container"}
    >
      <div className={"shadow-card-multi"}>
        <p>{text}</p>
      </div>
    </div>
  );
};

export default MultiShadowCard;
`,
"/styles.css": 
`
.App {
  height: 100%;
  width: 100%; 
}

p {
  text-align: center;
}

.card-container {
  width: 100%;
  display: grid;
  grid-template-columns: repeat(2, minmax(0, 1fr));
  font-family: "IBM Plex Sans";
  font-weight: 500;
  margin-bottom: 20px;
  color: #222;
  gap: 10px;
}

.shadow-card-multi {
  display: flex;
  align-items: center;
  justify-content: center;
  height: 175px;
  width: 175px;
  padding: 20px;
  background-color: #f5f5f5;
  border: transparent;
  border-radius: 0.55rem;
  box-shadow: 2px 4px 0 4px hsl(230deg, 100%, 69%),
    4px 8px 0 8px hsl(310, 68%, 70%), 8px 10px 0 12px hsl(170, 38%, 48%);
  margin: 0 auto;
  font-size: 16px;
  font-family: "IBM Plex";
}

.shadow-card-inset {
  display: flex;
  align-items: center;
  justify-content: center;
  height: 175px;
  width: 175px;
  padding: 20px;
  background-color: #f5f5f5;
  border: transparent;
  border-radius: 0.55rem;
  box-shadow: inset 0 0 10px 4px rgba(0, 0, 0, 0.3),
    inset 0 0 24px 8px rgba(0, 0, 0, 0.2),
    inset 0 0 36px 12px rgba(0, 0, 0, 0.3), 12px 6px 0 0 rgba(0, 0, 0, 0.15);
  margin-bottom: 20px;
  margin-top: 20px;
  margin: 0 auto;
  font-size: 16px;
  font-family: "IBM Plex";
}
@media screen and (max-width: 768px) {
  .card-container {
    display: flex; 
    flex-direction: column; 
  }
}
`
}} /> 

Our element _sinks_ into the frame. To be fair, these are no secret. I see them in the wild regularly, but they're more useful than you might think. An element could "cast" a shadow on its neighbor, emphasize a blockquote, or give an image a vignette effect via a `div` wrapper.

It's also worth noting that I did run into a small 'gotcha' while making the above components. As `box-shadow` doesn't add to the size of an element, I had to double the bottom margin to keep it from <TextGradientComponent text={`bleeding`} /> onto the text. Something to keep in mind, particularly in a mobile environment.

## To Wrap 

> `Box-shadows` do not affect the size of an element, but like all CSS animations, they come with some rendering overhead.
> `Borders` will alter an element's dimensions, but this can be partially remedied via `box-sizing`.

Neither property trumps the other, but if I _had_ to choose, I'd roll with `box-shadow`. That may change as we dive further into `pseudo-classes,` but as it stands, `box-shadow's` versatility is tough to beat.

Like always, thanks for reading and perhaps even enjoying it! Feel free to reach out with any feedback you may have! I'm working hard to craft as interactive an experience as I can. I'd love to hear what works and what doesn't!



A deep dive into CSS border, box-sizing, and box shadows. Find out why box shadows cause zero layout shift!

A deep dive into CSS border, box-sizing, and box shadows. Why do they cause zero layout shift?

The Zen of Borders and Box Shadows


I was initially content with my code blocks. The aesthetics were in line with my expectations, and they worked. But something bothered me. I've consumed a staggering amount of development content, tutorials, and tell-alls, but around 10% stuck with me. 

The difference came down to _interactivity_. Sure, I can copy and paste with the best of them. Make a few tweaks, change a handful of variable names and types to fit my needs, and then pat myself on the back for a job well done. The feature works, so I move on. But returning to a handful of these snippets, I realized I had no idea _why_ they work. 

So, I decided to build a custom code sandbox using the lovely [`Sandpack`](https://sandpack.codesandbox.io/docs) dependency. This package was crafted by the equally lovely minds behind [Codesandbox.io](https://codesandbox.io/), and I have to say. They really laid it all out there. I mean all of it. You have access to a simple-to-use vanilla setup through `Sandpack,` but it also exposes a lot of lower-level components that give you complete control over the styling and functionality of your little environment. 

This piece isn't exactly a tutorial, but rather, dissects the benefits of a code sandbox and the interactivity it provides. Rest assured, we'll cover `Sandpack` in depth in a future article. 

That said, let's explore its basic capabilities. 

## What's a Code Sandbox? 
<SandboxWrapper template="react-ts" files={{
  'App.tsx': `import React, { useState } from 'react';
  import './styles.css';

  const TestButton = () => {
    const [count, setCount] = React.useState(0);

    return (
      <div style={{
        display: "flex",
        justifyContent: "center",
        alignItems: "center"
        }}
      >
        <button 
          onClick={() => setCount(count + 1)}
        >
          Count: {count}
        </button>
      </div>
    );
  };
  export default TestButton;
  `,

'styles.css': `
  button {
    display: inline-block;
    text-align: center;
    appearance: none;
    font-family: "Karla VF", Karla, Inter, -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol";
    background: darkcyan;
    color: #fff;
    font-weight: 600;
    font-size: 18px;
    padding: 14px 20px;
    border: 0; 
    border-radius: 8px;
    cursor: pointer;
    transition: all 0.1s ease-in-out;
    box-shadow: 0 2px 2px rgba(0, 0, 0, 0.5);
    backgroundColor: "hsl(230deg, 100%, 69%)",
    height: "100px",
    width: "150px",
  }
`
}}/>

That amazes me. Obviously, not the counter. That's about as average as they come, but the sandbox itself. [MDX](https://alechorner.com/blog/mdx-bundler-is-my-new-best-friend) is already incredibly modular and offers the best developer experience I've come across. `Sandpack` makes it feel complete.  

## A Brief Introduction to Sandpack
Now that it's been praised, let's dive into how it works. First, the `<Sandpack />` component. This is your baseline, vanilla setup, which looks and works well out of the box. 
    
It accepts four primary arguments: 

> 1. `files,` or the code displayed in each tab. 
> 2. `template,` or the language used. I commonly use `react-ts,` `react,` and `javascript.`
> 3. `theme,` or style.
> 4. `options,` which houses sub-arguments like `customSetup.`
    
```tsx=
   import React from "react";
   import Sandpack from "@codesandbox/sandpack-react";
                
    const App = () => {
        let files = {};

        return (
          <Sandpack 
            template="react-ts"
            theme="dark"
            files={{
              // Note: All files must start with a backslash
              "/App.tsx": `
                export default App () => {
                  return <div>Hello World</div>;
                `
              }
            }}
          />
        );
    };
    
    export default App;
```   
        
The above returns a default "Hello World" but also highlights the limitations of static code blocks. Both have their place, but some problems call for more engaging solutions.

## Some Things Are Better Left Visual
              
This is particularly true for both CSS and IKEA furniture. In the below, try uncommenting out `border-radius` in `styles.css.` Like the great stonemasons of old, we chiseled off the rough edges to create a circle. Tinker with that `border-radius` value. 
              
It doesn't need to be in pixels. `Em,` `rem,` and percentages work as well.
          
<SandboxWrapper files={{
'/Box.tsx': 
`import React from 'react';
import './styles.css';
    
const Box = () => {
  return (
    <div>
      <div className="card">    
      </div>
    </div>
  );
};

export default Box;
`,

'/styles.css': 
`.card {
  display: flex; 
  align-items: center;
  justify-content: center;
  height: 200px;
  width: 200px;
  background-color: red;
  /* border-radius: 100px; */
  /* animation: hoverEffect 2s infinite alternate; */

}

@keyframes hoverEffect {
  0% {
    transform: translateY(0);
  }
  100% {
    transform: translateY(10px);
  }
}
`,
"App.tsx": `import Box from "./Box";
import React from "react";
import "./styles.css";

export default function App () {
  return (
    <div>
      <Box />
    </div>
  );
};`
}}
template="react-ts"
/> 

## A Whimsical Floating Circle
Now, we have a nice circle. It's a bit red for my taste, but all the same. What if we wanted to make it "float"? We don't need fancy Javascript, as CSS does the trick. Go ahead and uncomment `animation`.
    
And... there it goes! Like a butterfly, our square emerged as a magnificent, floating circle. But let's break down what we did to get there. 
    
> - We crafted a circular `div` using `border-radius.` We chose a 100px radius or half of the element's height and width. For a math-free approach, use `border-radius: 50%`.
> - `Animation` has a few moving parts. First, `hoverEffect` assigns a label to our animation.
> - The animation lasts two seconds (`2s`), continuing infinitely (`infinite`) and alternating from top to bottom (`alternate`).  
> - Leaving us with: `animation: hoverEffect 2s infinite alternate`.
    
Again, play with these values and see how the animation morphs. The last piece of the puzzle is CSS `@keyframes.` These provide _waypoints_ for our CSS to follow throughout the life-cycle of an animation. Above, you'll see I've added two intervals to our `keyframes`: 0% and 100%. In essence, a start and a stop. 

> Note that you can add as many intervals with additional animation logic as required. You're certainly not limited to zero and 100.   

Earlier, we set the name `hoverEffect` in our animation. This allows us to use it as our aforementioned `waypoint,` ensuring our `keyframes` affect only the expected element.
    
## Conclusion
    
An interactive sandbox is critical for tech-centric educational content. That's not to say nothing can be learned from static code blocks, but rather, a sandbox shows what a code block can only tell. We learn by doing, and a sandbox lets you do just that.  
    
It's already becoming a staple in my method, and I don't see that changing anytime soon.
    
We'll dive deeper into `animations` in a complete guide, as it truly has too many variations for the scope of this piece. That said, hopefully, you learned something new or appreciated the approach!

This blog and I are works-in-progress, but we'll get there one <TextGradientComponent text="interactive" /> post at a time. Thanks for reading, scoffing, or just stopping by! 

Why I built my custom code sandbox to add a layer of interactivity

Build a custom code sandbox for your MDX, Nextjs, or React blog, and enjoy full interactivity.

Why I Built My Custom Code Sandbox


This post is about MDX and Kent C. Dodds' `mdx-bundler.` That's right. The whole thing. I'm receiving nothing in return for posting this, and I'm not sponsored by 'ol Kent. It's just a personal golden child that I'm excited to share with the world. 

For this post, we'll use this blog as our example. It's built using Next.js, which I couldn't recommend more for sites with _mostly_ static content. For my info on the ins and outs of the blog, shimmy over to this [post]().  

Now, before we dive into the wonders of bundling, let's start from the top.

## What's MDX? 

You're likely familiar with the first two letters without even knowing it. All those Github `. READMEs` are written using Markdown or `.md` files. Markdown is, semi-ironically, a markup language that makes it easy to describe what a page _should_ look like. 

Essentially, it's HTML with fewer steps. 

> -  In Markdown an `<h1>` tag is just `# H1 Title`
> - An H2 is `## H2 Title`
> - A list is as easy as `-` or `*`
> - Maybe a quote? `>`
> - For a full list, check the [Markdown Cheat Sheet](https://www.markdownguide.org/cheat-sheet/)

This is all well and good and makes for a fantastic development experience for content creation, but its true power stems from the X. We'll call it the X-Factor (_I'm sorry, I couldn't help it_).

The X stands for extended, or Markdown Extended. This extension allows us to import and use custom React components within our Markdown content. 

```mdx
import TestButton from './TestButton';

# A Title, WOW!

Some content. 

<TestButton /> 
    
## Here's an H2

Some more content
```

The above would render the title as an `<h1>` and our button as the React component you'd expect. On its own, MDX has a ton of things going for it. Most notably, a simplified developer experience for content creators, but with the full suite of interactivity offered via React! 

And we haven't even gotten to `mdx-bundler` and `gray-matter` Things are about to get spicier.

> NOTE: Currently, I do not recommend [MDX for CRA-based](https://mdxjs.com/docs/getting-started/#create-react-app-cra) (_create-react-app_) applications. It takes far too much monkey-patching to get up and running, and I ran into numerous issues with race conditions from a traditional server-side setup. That isn't to say it can't be done, but alternative methods save more than a few headaches.

Using MDX with Next.js, or any framework, requires a few dependencies. The names of these imports, however, do differ slightly from framework to framework: 

> - [`mdx`](https://yarnpkg.com/package/mdx)
> - [`mdx-bundler`](https://yarnpkg.com/package/mdx-bundler)
> - [`esbuild`](https://yarnpkg.com/package/esbuild) - *required for `mdx-bundler`*
> - [`next/mdx`](https://yarnpkg.com/package/@next/mdx)
> - [`@mdx-js/react`](https://yarnpkg.com/package/@mdx-js/react)
> - [`rehype`](https://yarnpkg.com/package/rehype)
> - [`rehype-slug`](https://yarnpkg.com/package/rehype-slug)
> - [`rehype-autolink-headings`](https://yarnpkg.com/package/rehype-autolink-headings)

## Enter mdx-bundler & gray-matter

The former complies all of your Markdown and its dependencies to serve at run-time rather than build-time. 

> - `Build-time` refers to the steps to compile and prepare your code for production.
> - `Run-time` occurs when your application runs in response to user requests and occurs after the `build` and `compile` stages.

Say you notice a typo in a published post. With build-time solutions, correcting this error would require a rebuild and redeploy. And as each change triggers a rebuild, your build times increase the more content you serve. 

On the other hand, say you have a pre-defined route for your MDX content. When a request arrives, the MDX is loaded and passed off to `mdx-bundler` to render. Your build times never increase, as the bundler handles each render on request. All post data can be cached to avoid unnecessary re-bundling if looking for even more speed. 

And then we have `gray-matter`. This package parses any `frontmatter`, or metadata found within your content. These items can be used throughout your codebase and significantly improve your SEO. 

In the following, we'll loosely utilize the handy and official [Next.js guide](https://nextjs.org/learn/basics/create-nextjs-app).

So, we've covered what `mdx-bundler` and `gray-matter` _are_, but how do we use them? As previously mentioned, `gray-matter` is a dependency for `mdx-bundler.` Before we dive into bundling, let's take a quick dip into `gray-matter`. 

```mdx=
---
title: Post One
seoTitle: How I built his post using MDX and mdx-bundler
abstract: 10 reasons doctors HATE Post One
isPublished: true
publishedOn: 2023-08-11
---
import React from 'react';
import TestButton from './TestButton';

# A Title, WOW!

Some content. 

<TestButton /> 
    
## Here's an H2

Some more content
```

Everything situated between three dashes is our `frontmatter`. The `gray-matter` package parses our content, separating its values into `content` and `data`. `Content` holds our parsed Markdown, and `data` our `frontmatter`. 

`mdx-bundler` does the same but returns our values as `code` and `frontmatter`. `Code` contains the content of our posts, and `data`-- you get it--the metadata, or `frontmatter`. Now, let's move on to the main event, the Great Bundling.

`mdx-bundler` utilizes two primary functions, `bundleMDX` and `getMDXComponent`. The former handles the actual bundling, while the latter is used to populate that bundled content with React components. 

Your bundle function should look something like this: 

```tsx=
export async function getPostData(slug: string) {
  const fullPath = path.join(articlesPath, `${slug}.mdx`);
  const source = fs.readFileSync(fullPath, 'utf-8'); 

  const { code, frontmatter } = await bundleMDX({
    source: source,
    mdxOptions(options, frontmatter) {
      options.rehypePlugins = [
        rehypeSlug,
        rehypeAutolinkHeadings,
      ];
      return options;
    },
  });

  return {
    slug, 
    frontmatter, 
    code,
  };
};
```
To break that all down a bit: 

> 1. Collect all MDX files in the `pages` directory via `fs.readdirSync`
> 2. Load and parse all my frontmatter via `gray-matter`
> 3. Bundle the MDX and parsed frontmatter with `mdx-bundler`
> 4. Pass any options or plugins that dictate the format of our content to `mdx-bundler`
> 5. Return the data, which in Next must be an object.

As for the options and plugins, `mdx-bundler` can handle several extensions, but `remark` and `rehype` are the two I see most commonly. 

In the above snippet, we're using `rehypeSlug` and `rehypeAutoLinkHeaders`. We use the `Slug` for dynamic routing, `AutoLinkHeaders` for our table of contents, and header links. Note that in Next apps, `staticProps` must be returned as an object, hence the usage of `return {}`.

## Where the bundled magic happens 

It's awesome enough that `mdx-bundler` compiles all your content to serve run-time, but its true strength lies in how it handles your reusable components.

This blog has a handful of components that I use for each post. Take the anchor links found in the headers. I created and styled the component once, passed it to the bundler, and every single header in every single MDX post shared that universal style. It's my incumbent golden child for a reason! 

But like anything in tech, this works better with a demonstration. To quickly recreate the above: 

```tsx=
import { FiLink } from "react-icons/fi";

type HeaderProps = {
  children: string;
};

/*  
    Ensure each header has a unique id, 
    I recommend something a little more robust
    than this. 
*/ 

const generateId = (text: string) => {
  return text
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/(^-|-$)/g, "");
};

// repeat for subsequent header tags
export const H1 = ({ children }: HeaderProps) => {
  const id = generateId(children); 
  return (
    <h1 id={id}>
      {children}
      <a href={`#${id}`} aria-hidden>
        <FiLink />
      </a>
    </h1>
  );
};
```

Before we wrap, once everything's bundled up, how do we pass the data to our components? That's where `getMDXComponent` steps in and why this bundler is an absolute workhorse. 

```tsx=
export default function BlogPost({ 
  code, 
  frontmatter
}: BlogPostProps) { 
  const Component = useMemo(() => getMDXComponent(code), [code]);

  return (
    // cut for brevity
    <article>
      <Component
        components={{
          h1: HeaderComponents.h1,
          h2: HeaderComponents.h2,
          h3: HeaderComponents.h3,
          h4: HeaderComponents.h4,
        }}
      />
    </article>
  );
}; 
```

In plain English, it automates the importing, and even usage, of React components in your blog posts. So, the only imports you need are bespoke components used exclusively in a single post. The rest? They're already bundled. 

The switch made writing an absolute joy and was worth every ounce of time spent porting. Gone are the days of tedious HTML structuring for each post, and I couldn't be happier. 

## Wrapping up 

If I had to give `mdx` and `mdx-bundler` a tagline, I'd go with, "Get back to the fun parts." 

I love to build, but I also love to write. This setup allows me to do just that. Focus on the content and spend my development energy on adding fun, possibly even useful, features to the site.

For my tastes, the flow is the perfect happy medium between `full-code` HTML grinds and `no-code` auto-blog sites. So, if you're anything like me and hopelessly torn between two loves, give `mdx` and `mdx-bundler` a long look.

Once you do, you might not look back. 

<small>Why does that sound so dramatic?</small>



An in-depth look at using mdx-bundler to improve your blog

MDX-Bundler Is My New Best Friend


> #### Terms to Know
> - `state` -- The internal data a React component maintains. Within these components, state can be changed or mutated, and control how it behaves and renders. 
> - `props` -- While state is _mutable_, or can be changed, props are the _immutable_, or unchangeable, parameters passed from parent to child components. 


Ahh, Redux. It's a complicated, frustrating abstraction, but once it clicks, it morphs into a powerful, modular tool that becomes a part of your standard reset. Alternative solutions [certainly exist](https://yarnpkg.com/package/xstate), but Redux still sees the lion's share of usage with just under [**nine million**](https://snyk.io/advisor/npm-package/redux) weekly downloads. 

## Should you use Redux in 2023? 

If you're building a simple, or mostly static, application with limited state changes, then probably not. [React Context](https://react.dev/reference/react/useContext) will serve you just fine for most personal projects. 

Redux pulls its weight in more complex projects -- personal, work-related, or anything in between. As your application scales, the appeal of state management tools scales in turn.

It's worth noting that Redux is rarely used as a standalone solution. Myriad packages exist to help assuage the initial complexity, and for the most part, they work great. Unfortunately, the majority of guides and documentation are either outdated or too superficial to glean much from. Today we'll (hopefully) put some of that to rest... *or add to the pile*.

I know I know... more dependencies. I don't love it either, but if you've chosen Redux--or life chose it for you--it's still a skill worth learning, and many of its core concepts will carry over to newer, fresher solutions. When that time comes, you'll pick them up with ease.

So, if you're where I was, that hopelessly lost and confused, you've come to the right place. While this blog uses Next.js, the general structure doesn't change.  Where it does, I'll be sure to make a note.

Now, you might be thinking something like, "What's your point?" Or maybe, "Get on with it."

To you, I say, "Yeah... okay."

> #### The stack: 
> 
> - [redux](https://yarnpkg.com/package/redux)
> - [redux/react-redux](https://yarnpkg.com/package/react-redux)
> - [reduxjs/toolkit](https://yarnpkg.com/package/@reduxjs/toolkit)
> - [reselect](https://yarnpkg.com/package/reselect)
> - [redux-saga](https://yarnpkg.com/package/redux-saga)

```cmd=
//Installation
yarn add redux react-redux @reduxjs/toolkit reselect redux-saga
// OR 
npm i redux react-redux @reduxjs/toolkit reselect redux-saga
```

## What is Redux? 

As JS apps ramp in complexity, our code manages and maintains far more `state` than ever. [Redux's documentation](https://redux.js.org/understanding/thinking-in-redux/motivation) puts this quite well: 

> _"Managing this ever-changing state is hard. If a model can update another model, then a view can update a model, which updates another model, and this, in turn, might cause another view to update. At some point, you no longer understand what happens in your app as you have lost control over the when, why, and how of its state. When a system is opaque and non-deterministic, it's hard to reproduce bugs or add new features."_

In short, Redux handles the entirety of your application's state within a single object tree, acting as your 'single source of truth' that behaves consistently across an entire application. It's also _mostly_ framework agnostic, so once you have a feature up and running, it's easy to port from project to project. 

To that end, we'll start with something I use in nearly all of my projects, including this blog: the vaunted dark mode. 

No more history stalling, promise. Let's get after it. 

## How do I use it? 

Here's how I generally structure my Redux folders. It may seem like a few too many `index.ts` files, but they do serve a purpose! 

<DirectoryTreeView content={`web-core/
  ├── mode/
  │   ├── sagas/
  │   │   └── index.ts
  │   ├── selectors.ts
  │   ├── slice.ts
  │   └── index.ts
  ├── index.ts
  ├── reducers.ts
  └── sagas.ts
  store/
  ├── store.ts
  ├── constants.ts
  ├── hooks.ts
  └── initialize.ts
  `
  }
/>

Redux works around the concept of a `store.` True to its name, this `store` is a central hub that _stores_ the current state of your application. This becomes your 'single source of truth' as this global state is stored within a single object tree, ensuring data consistency across an application. 

So your starting point will always be setting up your store. As mentioned, this is a Next.js blog, so there will be a tiny difference in implementation. We'll utilize the `configureStore` function via `@reduxjs/toolkit` and `createWrapper` via `next-redux-wrapper.` 

```tsx=
import { Provider, useSelector, TypedUseSelectorHook } from "react-redux";
import { configureStore } from "@reduxjs/toolkit";
import { createWrapper } from 'next-redux-wrapper'; 

const prod = Boolean(
  (process.env.REACT_APP_API || process.env.NODE_ENV) === "production" 
); 

/* We'll round back to these */
// const sagaMiddleware = createSagaMiddleware(); 
// import reducers from "@/web-core/reducers";
// import sagas from "@/web-core/sagas";

export const store = configureStore({
  reducer: reducers, 
  preloadedState: {},
  middleware: [sagaMiddleware],
  devTools: !prod,
});

/* Next.js specific can be ignored for vanilla React */
const nextStore = () => store; 
export const wrapper = createWrapper(nextStore, {debug: false}); 
```
With that, we have our store. The `@reduxjs/toolkit` dependency offers quite a few useful functions. Here we use `configureStore`, which automatically combines our `reducers` and `middleware`, and enables `Redux DevTools` in non-production environments. We also include an empty object as our initial store state. 

The bottom portion is Next.js specific. We use `createWrapper` via `next-redux-wrapper`. This grants `getInitialProps` access to our store on the `client side`. Left unwrapped we'd need to create a separate `store` instance on the `server`, which sounds horrific.   

Now that we have our `store`, what makes Redux tick? 

## Redux actions and reducers

We know that Redux stores the global state of our application within a single object tree and that this object tree is used throughout your application. To access this state, Redux utilizes two basic types: `actions` and `reducers`. 

`Actions` are simple strings that bind a name to an action that _will_ occur. Toggling dark mode would look something like this: 

```tsx=
const toggleThemeInit = createAction<string>('TOGGLE_THEME_INIT');
```

`Reducers`, on the other hand, define what actually occurs when the `action` string is passed to Redux. Now, there are several ways to handle this step, but I always keep it in a `slice.tsx` or `slice.js` file. A `reducer` function can be as complex or as simple as you'd like. I prefer simple, as `redux-saga` handles the majority of my side effects. 


```tsx=

import { createSlice, PayloadAction } from '@reduxjs/toolkit'

type ModeProps = {
    mode: string | undefined; 
    loading: boolean; 
    error: string;
}

interface initialState: ModeProps {
    mode: undefined;
    loading: false; 
    error: '';
}


const modeSlice = createSlice(
    name: 'mode',
    initialState, 
    reducers: {
        // Reducers go here
    }
)
```

Ya gotta love Typescript's lovely boilerplate, but the gist is simple. Our `ModeProps` return three objects. `Mode` will always return either a string or `undefined`. More specifically: 'light', 'dark', or `undefined`. 

We pass our `ModeProps` into our initialState interface via Typescript's `key: Type` pattern and set our initial values. We then initialize an array of objects using `createSlice`. To toggle our theme, we need to handle our theme switch, set the value in our `store`, and cover the errors that may arise. Each of these `reducers` can exist as a subset of a previous instruction or do its own thing entirely: 

```tsx=
const modeSlice = createSlice({
    name: 'mode',
    initialState, 
    reducers: {
        switchThemeMode: (state) => {
            state.loading = true;
        },
        setThemeMode: (state, action: PayloadAction<string>) => {
            state.loading = false; 
            state.mode = action.payload;
        },
        setFailure: (state, action: PayloadAction<any>) => {
            state.loading = false; 
            state.error = action.payload;
        },
    },
});

const reducer = modeSlice.reducer;
const actions = modeSlice.actions;

export { reducer, actions }
```

`switchThemeMode` tells Redux that a new theme is loading. `setThemeMode` resets loading and sets our new theme, and `setFailure` handles any failures that might occur during the swap. 

We export the created `reducers` and `actions` to use in our application and the last piece of our puzzle.

## Enter Sagas

That final piece is `redux-saga`. It's certainly worth noting that this package is technically deprecated. Despite that, it sees around five million monthly downloads, so is it really dead, or just kind of "undead"? 

Either way, I'm still using it, as I prefer to manage my effects, or how the state changes, in a single location that fires in unison. In other words, when a saga is called, all its instructions are run until we hit an error. 

`Sagas` utilizes five main functions for handling side effects: 

> 1. `all` -- Used to run any number of created sagas. 
> 2. `put` -- `PUT` requests instructing the saga middleware to schedule an action dispatch to the store.
> 3. `call` -- Similar to a `GET` request, `call` instructs our middleware to call a function with its `args` as arguments.
> 4. `takeEvery` -- Spawns a saga on each action dispatched to our store that matches the provided pattern.
> 5. `takeLatest` -- Fetches only the latest saga call and cannot be run concurrently.

Your mileage may vary, but I use `call,` `put,` and `takeEvery` almost exclusively. But `sagas` offers one more function, and it's the most useful by a longshot. That function is `yield.`You can wrap any of the above in a `yield` to have it run concurrently, capturing each request in the order of arrival.

Now, let's see how it all looks.

```tsx=
import { all, put, takeEvery } from "redux-saga/effects";
import { actions } from "@/web-core/mode/slice";
import { PayloadAction, createAction } from "@reduxjs/toolkit";

const {
  setThemeMode,
  setFailure,
} = actions;

export const toggleThemeInit = createAction<string>("TOGGLE_THEME_INIT");

function* setThemeSaga(action: PayloadAction<string>) {
  console.log("PAYLOAD RECEIVED: ", action.payload);

  let mode = action.payload;
  try {
    // checks local storage falls back to OS settings
    const isOsDark = window.matchMedia("(prefers-colors-scheme: dark)").matches;
    const osMode = isOsDark ? "dark" : "light";
    const curMode = mode || localStorage.getItem("theme") || osMode;

    if (curMode === "light") {
      // swap root ids w/ preference
      document.documentElement.classList.remove("dark");
      document.documentElement.classList.add("light");
      localStorage.setItem("theme", "light");
    }
    if (curMode === "dark") {
      document.documentElement.classList.remove("light");
      document.documentElement.classList.add("dark");
      localStorage.setItem("theme", "dark");
    }
    // Set preferred mode
    yield put(setThemeMode(curMode));
  } catch (error) {
    yield put(setFailure(error));
    console.log("ERROR CHANGING THEMES: ", error);
  }
}

export function* watchChangeMode() {
  yield takeEvery(toggleThemeInit.type, setThemeSaga);
}

export function* toggleModeSaga() {
  yield all([watchChangeMode()]);
}
```

That was a lot to absorb, but I wanted to showcase as many features as possible. Sagas, and all concurrent functions, should be labeled with `function*`. 

Note that `yield all` is unnecessary here and would only provide real value if multiple watchers existed in the scope. Otherwise, the flow is similar to what we've already seen. We create an `action` string representing the entire flow of instructions dictated within the saga. 

We grab `{ mode }` from our payload and check the current Redux `mode state`,  followed by local storage, and finally, the user's browser settings.

If `mode === "light"` swap to an adaptive `html light` class, and vice versa for `mode === "dark"`. We then yield our mode and establish a `watcher` to handle concurrent changes to our theme state. 

### Almost There

We can finally round back to `store/store.ts`. We're still missing our `reducers.ts` and `sagas.ts`. Fortunately, these solely bundle and export our code. Again, not necessary if the mode is the only Object in your store but incredibly powerful if you're managing several.

```tsx=
const sagaMiddleware = createSagaMiddleware(); 
import reducers from "@/web-core/reducers"; // alter path if not using Next
import sagas from "@/web-core/sagas"; // alter path if not using Next
```
We're still missing our `reducers.ts` and `sagas.ts`. Fortunately, these only bundle and export our code. Again, not necessary if `mode` is the only Object in your `store` but incredibly powerful if you're managing several. 

```tsx=
// reducers.tsx 

import { combineReducers } from "@reduxjs/toolkit";
import { reducer as mode } from "./mode";


export default combineReducers({
  mode,
});

// sagas.tsx

import { sagas as modeSagas } from "./mode";

/* Reminder that the `yield all` in watchChangeMode was a demo. */
const { watchChangeMode, toggleModeSaga } = modeSagas;

export default [
  toggleModeSaga,
];
```

## Wrapping up
 
And there you have it, a functional dark mode implementation using Redux and Redux Saga in 2023. This post was denser than expected, so we'll handle implementation in Part Two. We'll create a custom toggle switch and fully implement our Redux-centric dark mode!

Thanks for reading, skimming, or just dropping by!

An in-depth look at the technical structure of MDX

Redux and Redux-sagas in 2023

Using Redux & Redux-sagas in 2023


SQL is about as underrated as CSS. To be entirely forthcoming, I'm not sure how controversial an opinion that is, but all the same. Nowadays, `no-SQL` and `kinda-SQL` solutions are everywhere. I use Mongo almost daily, and I love it, but I'm often left searching for features that aren't there.

For blogs, personal projects, and a litany of other micro to mid-size projects, Mongo can't be beaten. But once you dip your toes into data-centric projects you'll find a lot more value in relational databases like PostgreSQL. 

First, we'll quickly tackle the basics, then I'll share some of my favorite tips and tricks to manipulate tables and views in SQL. The goal is to have the data in the exact format you're expecting _before_ you push and/or fetch it from the database. 

I know it's redundant to a strange degree, but I've used local Postgres DBs to format my data before pushing it to a cloud-hosted database... Whatever works, right? 

...Right? 

## What's SQL? 

Structured Query Language, or SQL, is a language built to gather specific data from large datasets. Put simply, you tell the database what you want, and it gives you only that. 

SQL's one of those oddball languages. It only has a handful of declarations, but the interactivity between them is staggering. Let's start by creating two tables. 

```sql=
CREATE TABLE IF NOT EXISTS Customers (
    id varchar PRIMARY KEY,
    name varchar, 
    email varchar,
    dateOfPurchase date
);

CREATE TABLE IF NOT EXISTS Sales (
    salesId varchar PRIMARY KEY,
    name varchar, 
    sold int
);
```
In a Mongo database, we'd have no way of easily and directly joining these two tables. We'd have to settle for a large nested array that includes the sales data. With Postgres, we can compare, contrast, and even create a new table or view out of our queried data. 

To set a baseline, let's take a look at `JOINS`. SQL offers four options to specify how you want to merge the two tables. You'll primarily use `LEFT JOIN` and `INNER JOIN`, but the other two have their place.

> 1. `CROSS JOIN` - returns all items that belong to BOTH tables
> 2. `LEFT JOIN` - returns all records from the left table, and matched rows from the right table
> 3. `RIGHT JOIN`- returns all rows from the right table and matched rows from the left
> 4. `INNER JOIN`- returns all rows that have matching records in both tables


```sql=
SELECT sum(sold) as numSold, 
    date_trunc('day', dateOfPurchase) as dt  
FROM Customers c 
LEFT JOIN Sales s on c.id = s.salesId
GROUP BY 2 
ORDER BY 2 DESC
```

That returns a list of the sum of all items sold on each date, ordered by date in descending order. The nice thing about SQL is that the vast majority is completely human readable, and does exactly what you'd expect.

## SQL and the wonders of CASE WHEN

[Dune Analytics](https://dune.com/home) is my favorite place to practice SQL. Even if you have zero interest in crypto, a blockchain is, at its simplest, a distributed database. This makes it ideal for showing the full extent of what a relational database can do. 

Dune is just that. Sets of large tables, many of which can be joined using an `id`, `hash`, or `date`. Take this query. It calculates the totals borrowed and lent by a protocol and separates these users into tiers based on dollar value. It also showcases one of my most used SQL patterns, `CASE WHEN`. 

> Note: In SQL casing doesn't matter, and formatting only vaguely matters. We'll discuss formatting rules in a later, more expansive post.

```sql=
select distinct borrower,
    date_trunc('day', "block_time") as dt, 
    (case when "usd_value" > 0 and "usd_value" <= 100 
      then 'a. Minnow <100'
          when "usd_value" > 100 and "usd_value" <= 1000 
            then 'b. Shrimp 100-1k'  
          when "usd_value" > 1000 and "usd_value" <= 5000 
            then 'c. Crab 1k-5k'  
          when "usd_value" > 5000 and "usd_value" <= 10000 
            then 'd. Fish 5k-10k'  
          when "usd_value" > 10000 and "usd_value" <= 100000 
            then 'e. Dolphin 10k-100k'  
          when "usd_value" > 100000 and "usd_value" <= 1000000 
            then 'f. Shark 100k-1M'  
          when "usd_value" > 1000000 
            then 'g. Whale >1M' end) as tier,
     count("borrower") as holders,
     sum(usd_value) as amount
from lending."repay"
    where project = 'MakerDAO'
    and "block_time" >= now() - interval '90 days' 
    and "usd_value" > 0

group by 1, 2, 3
order by tier desc;
```

This data can then be easily charted to give interested parties a granular view of the users interacting with the protocol. Now, imagine parsing that out in JS, even Python... it's really not all that fun. 

SQL is fantastic at handling the heavy lifting, so you can do more of what you enjoy: building, researching, exploring, sharing, and other -ing verbs. They're all back on the table as efficiently as possible. 

This serves as a primer. I'm not entirely sure how much interest there is in this kind of backend fun, but for now, we'll give it a fair shot. 

Thanks for reading! 

SQL is often overlooked, but good SQL can save hours

SQL Saves Hours, Possibly Lives


These are the hardest posts to write, particularly to start. It comes with the territory. Writers find, curate, and weave interlocking details into a coherent story. Rarely are we the focus of that narrative. 

That said, thanks for stopping by! I'm Alec Horner, web developer, and content strategist, born and raised in Houston, Texas. My story isn't new, but it is uniquely mine. Like most big things, it started with something small. 

I graduated with a degree in journalism. Looking back, it wasn't _exactly_ journalism that drew me to writing, but the depth of research. I liked to find and share exciting, new things. At first, I thought I'd found the proverbial **one**. To an extent, I did. I researched, wrote, shared, and it made me happy. 

But I got paid to write, not code. That was true until the moment the Discourse API went dark. 

## The early years

At the time, I was a contributor for MakerDAO, an Ethereum-based stablecoin protocol. DAOs are transparent and distributed by design, and most communications occur online in open forums. Contributors needed a means to quickly parse through the data and accurately depict critical events through weekly reports and community updates.  

They needed a functional alternative. To say that I was surprised that I was tasked with crafting it would be an understatement. To this day, I'm convinced I was mistaken for someone else. I had experience with Python and various SQL engines, but I'd never built anything used by anyone but me.

I've been writing longer than I've been coding, but over the years that ratio skewed further and further towards development. That little web scraper opened the door wide open. I'd found a new, seemingly unlimited, form of creativity.  

For the next two years, I took any data-wrangling gig I could find. I built local Postgres servers, distributed cloud servers, wrote APIs, and built analytics dashboards. But I also wrote bespoke research pieces, tracked KPIs, parsed sentiment data, and made frequent reports on my findings.  

I learned the depths of SQL, which turned out to be far deeper than expected. I learned how to create, handle, and manipulate complex arrays. I learned more than I need to about data types, storage, lifespan, and best practices. But most importantly, I learned the importance of good data habits and operational security.  

I had fun, but when it came to sharing my findings, I quickly hit a ceiling. Python's a fantastic language, but it's no secret that it's not particularly suited to web development. 

## 2021 to Present

It was January 2021 when it finally hit me. I wanted to be a full-stack developer with a deep understanding of a product's life cycle. I wanted to unearth unique angles and craft _my_ ideal implementations to display them. 

These years I spent cramming my brain with as much Javascript and friends info as possible. I've never gotten tired of it, and I'm not sure I ever will. 

Web development offers such a unique, personal touch on creativity. The reach is a part of it, but the rest is something more pure. The whimsical odds and ends don't seem like much, but make an application memorable. To me, that's art. It's also what makes both writing and programming infinitely exciting. 

Each project is unique, and the toolkit is like an ever-changing orchestra. In that sense, they're encapsulations of life. First, you find an issue or pain point. You set out to remedy it, and you quickly find that there is no one-size-fits-all solution. All you have is your interpretation of what that might look like. 

That's where my skillset allows me to thrive. I was fortunate to have a deep understanding of the copy and marketing process and ample training in customer-facing environments. It made me a far more adaptable developer and enabled me to provide value from conception to implementation. 

Get in touch. Let's build something special! 


alec horner

read. relax. react.