I've been picking up SQL! I wanted to take my MongoDB experience and see what some of that database querying would look like in the world's most popular database querying language.

Not a very narrative write up, but maybe some of these will help pull back the curtain on what SQL looks like!

Notes are courtesy of this introduction to MySQL.

Arithmetic

When selecting, you can do arithmetic on integers. You can also give them alias's with the AS clause:

USE sql_store;

SELECT
    first_name,
    last_name,
    points,
    points * 10 + 100 AS "discount factor"
FROM customers
-- WHERE state = 'FL'
ORDER BY first_name

You can edit the data returned and apply the changes. Neat!

SELECT DISTINCT state returns only the unique values with no repeating values

USE sql_store;

SELECT name, unit_price, ROUND(unit_price * 1.1, 2) AS "New Price"
FROM products

<> is the same as !=

single or double quotes work.

not case sensitive with strings.

Query by date

USE sql_store;

SELECT *
FROM orders
WHERE YEAR(order_date) >= YEAR(CURDATE()) - 5

Order of ops in logic operators:

AND goes first. then OR

IN and NOT

USE sql_store;

SELECT *
FROM Customers
WHERE birth_date > '1990-01-01' OR points > 1000 AND
    state NOT IN ('VA', 'GA', 'FL')

BETWEEN

USE sql_store;

SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'

LIKE operator

USE sql_store;

SELECT *
FROM customers
WHERE last_name LIKE 'b__y'
-- % any number of characters
-- _ single character

USE sql_store;

SELECT *
FROM customers
WHERE phone LIKE '___-___-___9' AND
    address LIKE '%Trail' OR
    address LIKE '%Avenue'

REGEXP

Special characters:

• ^ beginning
• $ end
• | logical or
• [abcd] match one of these values for one character
• [a-z] match within range for one character

USE sql_store;

SELECT *
FROM customers
WHERE last_name REGEXP 'b[ru]'

IS NULL

USE sql_store;

SELECT *
FROM orders
WHERE shipped_date IS NULL

Joins

Inner Join: The basic. We're starting from orders and we're pulling in the other table

USE sql_store;

SELECT order_id, first_name, last_name, o.customer_id
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id

We're also giving an alias to orders and customers as o and c. Nice!

Another example:

USE sql_store;

SELECT order_id, p.product_id, name, quantity, p.unit_price
FROM order_items oi
JOIN products p
    ON oi.product_id = p.product_id

Join across DB's

Not hard...

USE sql_store;

SELECT *
FROM order_items oi
JOIN sql_inventory.products p
    ON oi.product_id = p.product_id

Self Join

USE sql_hr;

SELECT e.first_name, e.last_name, m.first_name as manager_first_name, m.last_name as manager_last_name
FROM employees e
JOIN employees m
    ON e.reports_to = m.employee_id

Joining Multiple Tables

USE sql_store;

SELECT *
FROM orders o
JOIN customers c
    on c.customer_id = o.customer_id
JOIN order_statuses os
    ON os.order_status_id = o.status

Great visual explanation of Inner and outer joins, with the ven diagram visual. √

Inner and Outer Joins SQL Examples and the Join Block

Another ref for table joins

Inner join - You're getting only the intersections between the tables

Outer joins - you are including one full table plus the intersecting data. A left join includes all the customers, along with their order details. A right join gets the same result as the inner join if you're selected table is the left one.

USE sql_invoicing;

SELECT
    pm.name as payment_method,
   c.name as client_name,
   date,
   amount
FROM payments p
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id
JOIN clients c
    ON p.client_id = c.client_id

Compound Join Conditions

order_items has a compound key. Meaning, the unique identifier here is not a single id, but is in fact the combination of two id's from other tables.

Why not use a unique id here? Are there benefits to that? Shouldn't all tables have unique ids?

Well, regardless, here's how you handle it:

SELECT *
FROM order_items oi

Implicit Join

SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

Same as what we've been writing above. Not recommended, because leaving out will return a cross join.

Outer Joins

SELECT
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id

SELECT
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
RIGHT JOIN orders o
    ON c.customer_id = o.customer_id
ORDER BY o.order_id

SELECT
    oi.product_id,
    name,
    oi.quantity
FROM products p
LEFT JOIN order_items oi
    ON oi.product_id = p.product_id

Multiple tables:

USE sql_store;

SELECT
    c.customer_id,
    c.first_name,
    o.order_id,
    sh.name as shipper
FROM customers c
LEFT JOIN orders o
    ON o.customer_id = c.customer_id
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id

Note: Avoid right joins. Right joining can lead to complex, hard to understand queries.

SELECT
    o.order_date,
    o.order_id,
    c.first_name,
    s.name,
    os.name as order_status
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
LEFT JOIN shippers s
    ON o.shipper_id = s.shipper_id
JOIN order_statuses os
    ON o.status = os.order_status_id
ORDER BY order_status, o.order_id

Self Outer Joins

SELECT *
FROM employees e
LEFT JOIN employees m
    ON e.reports_to = m.employee_id

In this case, this query will include the manager that we are requesting.

USING

LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id

-- SAME AS

JOIN shipers
    USING (shipper_id)

Easier to write if the ids match!

Works for matching multiple columns, too

SELECT *
FROM order_items oi
JOIN orde_item_notes oin
    USING (order_id, product_id)

SELECT date, c.name as client, amount, pm.name as credit_card_name
FROM payments p
JOIN clients c
    USING (client_id)
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id

Natural Joins

Easier to code, but not recommended.

Joins tables based on the columns that match.

SELECT *
FROM orders o
NATURAL JOIN customers c

Cross Joins

Between two tables, shows all possible combinations for all rows in the two tables.

Colors: red blue green

size: s m l

Res:

red s blue s green s red m blue m green m etc...

SELECT *
FROM customers c, orders o

OR

SELECT *
FROM customers c
CROSS JOIN orders o

Prefer the explicit syntax

Union

SELECT
    order_id,
    order_date,
    'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT
    order_id,
    order_date,
    'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01'

Can combine records from multiple queries!

Columns that you return should be equal. Otherwise, you will get an error. This will error:

SELECT first_name, last_name -- cause of the error
from customers
UNION
SELECT name -- only one column here
FROM shippers

Inserting a Row

One way, if using all columns:

INSERT INTO customers
VALUES (
    DEFAULT,
    'Chris',
    'Padilla',
    '1922-01-01',
    DEFAULT,
    'address',
    'city',
    'TX',
    DEFAULT
)

If explicitly defining columns

INSERT INTO customers (
    first_name,
    last_name,
    birth_date,
    address,
    city,
    state
)
VALUES (
    'Chris',
    'Padilla',
    '1922-01-01',
    'address',
    'city',
    'TX'
)

INSERTING multiple rows

INSERT INTO products (name)
VALUE ('Chris'),
    ('Jenn')

Inserting Hierarchical Rows

Parent > Child relationships. One parent can have multiple children...

1. Add a new order.
2. Insert based on the LAST_INSERT_ID()

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2023-03-27', 1);

INSERT INTO order_items
VALUES
    (LAST_INSERT_ID(), 1, 1, 2.00),
    (LAST_INSERT_ID(), 2, 1, 4.00)

Copying a table...

CREATE TABLE orders_archived AS
SELECT * FROM orders -- Sub query - queries for use in another query

You have to set your primary key and AI column....

Using conditional select statement as a subquery

INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01'

CREATE TABLE invoices_archive AS
SELECT
    i.invoice_id,
    c.name,
    i.invoice_total,
    i.payment_total,
    i.invoice_date,
    i.due_date,
    i.payment_date
FROM invoices i
JOIN clients c
    USING (client_id)
WHERE payment_date IS NOT NULL

Updating a row

UPDATE invoices
SET payment_total = 10, payment_date = "2023-03-27"
WHERE invoice_id = 1

Using variables in your SET... you can use other fields to make updates like so:

UPDATE invoices
SET
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE invoice_id = 3

Updating multiple rows

Uses the same syntax. MySQL specifically has a safe mode that prevents updating multiple rows. You can turn it off by unticking "Safe Updates" in the preferences. You may have to restart MySQL after The IN clause can be handy, too.

UPDATE invoices
SET
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id IN (3, 4)

Using Subqueries in an Update Statement

Fun fact - you can select part of your SQL doc to run a script

UPDATE invoices
SET
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id =
    (SELECT client_id
    FROM clients
    WHERE name = 'Myworks')

Use the IN operator for multiple values:

UPDATE invoices
SET
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id IN
    (SELECT client_id
    FROM clients
    WHERE state IN ('CA', 'NY'))

Good practice: Select your query BEFORE you run an update statement on your DB

UPDATE orders
SET comments = 'GOLD'
-- SELECT *
-- FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE points >= 3000)

Deleting Rows

DELETE FROM invoices
WHERE client_id = (
    SELECT client_id
    FROM clients
    WHERE name = 'Myworks'
)

It's tempting early on to strongly associate with the tools you're using day in and day out. I feel really comfortable with React, so at time's I've thought of myself purely as a front end guy or a React guy or a JavaScript guy.

Ultimately, though, the people you serve need someone who is more versatile. Not that job titles matter, but the gig is typically "Software Engineer," not "React Dev."

As you develop, your problem solving abilities and sensitivity to creating systems transcends and particular tool. That's where the fun problem solving is, anyway. The tool is just a vehicle for speaking with the computer.

Chad Fowler in The Passionate Programmer makes a great case for this:

It's not about where you sit on the perceived value chain of project work (where the architects holds the highest spot of royalty). It's about how generally useful you make yourself...

To visualize what it means to be a generalist, it can help to dissect the IT career landscape into its various independent aspects. I can think of five, but an infinite number exists...:

• Rung on the career ladder
• Platform/OS
• Code vs. Data
• Systems vs. Applications
• Business vs. IT.

So many great dimensions through this lens. Platform/OS is a particularly juicy one. Full stack web dev can be balanced with mobile or desktop development. Developer and designer is another fun one, unsurprisingly common for front end developers.

The last point, especially, is worth noting. If you're with a startup ,the company is small enough where individual entrepreneurship and creativity play just as big a role as your technical skills.

Thankfully, the work is more fulfilling when you lean into generalizing.

Another one down!! This one I picked up so I could have more room to explore compared to my little moleskin.

It started out with figure drawing dissections:

And ended with animal construction studies:

Listen on Youtube

So dreamy...

I was visiting UNT recently for old times sake and made it into a practice room with a baby grand!! So much fun!

"Did I help someone today?"

Let me borrow from Simon Sinek's Golden Circle Model for a second: Why, how, and what.

At the end of the day, the bottom line is no small line in business. KPI's are a target to keep an eye on. Performance metrics and weekly outputs are something to continue monitoring. The "what" matters.

In the trenches, tools do matter. The systems we chose to implement, the ways we refactor our code, the time spent experimenting with a new technology. The "how" matters.

What matters most is certainly the "why" of helping someone.

Everything else almost feels like bike shedding when the question "Did I help someone today?" is the main guide.

Software is pretty expansive when it comes to this question, too!

Some days, that answer is "I helped a coworker debug a problem that's been slowing them down for weeks!" A deep and impactful day!

Some days, it's "I released a feature that will be used by thousands of people!" A broad impact sort of day.

The nice thing is, even on "low productivity days," we can answer affirmatively to that question in some dimension, if we're genuine.

Bring on the clear skies and open windows!!

Purchase on 🤘 Bandcamp and Listen on 🙉 Spotify.

Dave Rupert shared last year this amazing resource of translated video game magazines — Schmuplations.

Dave's post highlights some great connections between game development and Miyamoto's advice for success in the game industry. I did more digging and also enjoyed this Miyamoto quote from the same article:

What advice do you have for aspiring game designers?

No matter what your creative field, you should try to find a job that offers you many chances to realize own potential.

Before that, though, I think it’s important to refine your own sensibilities. 10 years from now, games will have changed. It won’t just be the same style of games you see today. If all you do is mimic what exists now, it will be difficult for you to create anything in the future.

I know it’s a cliche, but I think aspiring designers should follow where their curiosity leads, and try to accumulate as many different experiences as possible.

Another vote for following curiosity. Video games were (and still are?!) a young medium, so it's practically a necessity to be looking outside of games for inspiration from the world. There's simply more out there.

I'm excited to keep digging through the crates on this site! Here are a few more of my favorite snippets, this time from Beep Magazine's Game Music Round Table Interview:

What’s the secret to success in the game industry?

I hope this doesn’t come off wrong, but I don’t really remember trying super hard. One day I looked up and noticed things were going pretty well. Of course, I’ve been very blessed to have talented people and teachers around me. So I would probably have to say I didn’t really try super hard to get where I am… it just happened naturally.

A vote for effortlessness. It's what's easy and natural that we end up excelling at, and so, opportunities open. A nice counter to the notion that all that's worthwhile is on the other side of hard work alone.

A couple more on breaking into games. I don't ultimately see myself in games, but it's fun to see how musicians made it in way back when!

What advice do you have for aspiring game designers?

At present there are two in-roads to working in game music. The first is to join a game development company. For that it’s helpful to go to a 4-year college, and all the better if it’s a music school. The other way would be to gain some notoriety as a composer first, perhaps in a rock band or something, but basically if you can build a reputation in the music industry as a player, composer, or arranger, you might end up getting commissioned for this sort of work. Whichever path you choose, having that can-do spirit of “I want to write music no matter what!!” is important. If you can carry that passion with you wherever you go, and sustain it, I think nothing will serve you better.

Just an interesting perspective that still feels true. Though, in the indie world these days, it helps to have another skill to bring to the table: art, development, story writing, etc.

Here's that counter point from Mieko Ishikawa, composer for the Ys series:

For those who want to write video game music, I think knowing a bit of computer programming is a big advantage. There’s lots of people out there who can write songs, but if that’s your only talent, I think it will be rough-going in this industry.

Seems like good advice for creative work in general. Go all in on trying to make it on your art alone, and then pick up auxiliary skills to sustain your creative work.

Having been a teacher and, generally, someone who places a high value on exploring the world through learning, I end up reading a few books on education.

I'm only just coming across Seth Godin's Stop Stealing Dreams, a manifesto on education. It also happens to be about the values in our society, but education is the driver.

The illustrated version is beautifully done. Here are my favorite points from first glance:

33. Who Will Teach Bravery? The essence of the connection revolution is that it rewards those who connect, stand out, and take what feels like a chance. Can risk-taking be taught? Of course it can. It gets taught by mentors, by parents, by great music teachers, and by life.

44. Defining The Role of A Teacher. Teaching is no longer about delivering facts that are unavailable in any other format... What we do need is someone to persuade us that we want to learn those things, to push us or encourage us or create a space where want to learn to do them better.

56. 1000 Hours. Over the last three years, Jeremy Gleigh... has devoted precisely an hour a day to learning something new and unassigned...This is a rare choice...Someone actually choosing to become a polymath, signing himself up to get a little smarter on a new topic every single day... The only barrier to learning for most young adults in the developed world is now merely the decision to learn.

If you've been in music, likely you've had an instructor teach bravery. Sometimes it's the director that encourages you to give it one more try. Or the drum tech that's not giving up on a section of percussionists struggling with a tricky passage. Or the private teacher that so wholly embodies their instrument and the musicianship of a piece. So much so that it's impossible to resist the allure of striving for that sense of awe and wonder in our own musicianship.

Those teachers have also created environments where students can be fully immersed in a world of music, no matter how big or small. It's what makes classroom band more special in my mind than solely taking private lessons - you can't beat a thriving community of learners where you're playing a unique role in the musical process. Every band needs a kid on tuba and another on flute playing together.

A somewhat disappointing truth is that many students that graduate high school, having played an instrument for 7 years at that point, don't pick it up again. I think there's a missed opportunity - leading with greater creativity and exploration in the classroom. I'm sure Seth explores those ideas in the book as well.

On the other hand, though, it's still one of the best vehicles for learning resourcefulness that we have. To the last point I've quoted - all the musicians I know have absolutely no problem applying creativity, persistence, and bravery in their endeavors beyond the instrument. I have musician friends who are also photographers, who knit, work with aquaponics, code for fun, mountain climb, and write novels. Many of my colleagues are now master teachers. A significant number of them also have skillfully transitioned into roles in tech, finance, health, marketing, entrepreneurship, and sales.

The vision of the once young musician grows beyond music, beyond the self, beyond a local tribe to a larger community and a greater opportunity for contribution and the wonderful lives that support that.

In that case, music education is doing exactly what I think it is uniquely best at: Expanding Dreams.

Miranda and I drew camels and snails while listening to Arooj Aftab this week! 🐌 🐫

Listen on Youtube

Shaking that guitar and stretching my barre chords

Like flossing: really important to do, hard to get in the habit of.

But I've had too many tragic losses of data growing up. And now my entire life's work happens and is saved on my computer. So I've set up a system for myself!

I mostly follow Wes Bos' Backup Strategy. It keeps things easy and nearly automatic. Specifically:

• I backup my computer to a Time machine on a WD My Passport drive.
• My computer is backed up remotely to Backblaze
• On a WD Passport I have old files, videos, and photos from older computers that aren't
• Those older files are also synced with Backblaze.

Backblaze is great because it runs automatically in the background. It's not at all a resource hog.

It's only semi automatic because I still have a calendar reminder to actually plug in my drive every Friday morning. All of my ports are usually full with MIDI controllers and other gazmos. Ideally, I'd have some sort of monstrous port that takes in my monitor, midi instruments, guitar audio interface, AND USB to leave my external drive plugged in. Putting it on the wishlist!

My intro to databases was through MongoDB. Coming from a web development perspective, it was comfy! Documents look and work like JSON objects. They're flexible and can adapt to changing schemas. I've enjoyed it!

I'm dipping SQL this week to broaden my horizons in data land. The first conceptual overview I got was this:

• SQL databases are Relational Databases
• Other database structures are Non-Relational, or NoSQL Databases

So are relationship structures exclusive to SQL?

SQL

The "relational" distinction comes from the fact that data is stored within tables in SQL.

My e-commerce app would divide its data into three tables:

• User
• Items
• Orders

Each table for each would have data pertaining specifically to that category of data. They'd then be linked through a relationship.

Orders, for example, would have a many-to-many relationship with items. Many items can be in many orders. Many orders can have many items.

Users could have a many-to-one relationship with Orders. A user can have many orders, but an order is tied to only one user.

So when your querying the items in the user's order, SQL has to travel through a few relationships:

1. We start with the user ID
2. We query an order based on the user
3. We query items based on what's contained in the order
4. We get massage the data to show what's relevent to our query.

And thus, SQL gets the relationship monicker.

MongoDB

What made this confusing at first is that MongoDB falls under the non-relational database category, but I can make these same queries in MongoDB as well.

MDN has a terrific guide to Express and Databases. When setting up the DB schema for MongoDB, it's done with a relational structure. In their library example, there are schemas developed for books, authors, book instances, and genre. All of which are tied together through relationships. In mongo, the ID is used to connect these documents.

So the NoSQL descriptor is a more accurate lead. The query language is different, but relationships are still an offering in Mongo.

The non-relational categorizing, though, is somewhat fair: The main benefit of Mongo is that you can nest in one document what you would otherwise split between tables.

A user document can store their orders within a nested object in MongoDB. Depending on your application, you may not be accessing orders outside of the context of a query to the user. Mongo has a clear healthcare example where patient information is stored in their document

Matthew Hinsley's Form & Essence quotes William Stafford's "A Way of Writing" —

A writer is not so much someone who has something to say as he is someone who has found a process that will bring about new things he would not have thought of if he had not started to say them. That is, he does not draw on a reservoir; instead, he engages in an activity that brings to him a whole succession of unforeseen stories...For the person who follows with trust and forgiveness what occurs to him, the world remains always ready and deep, an inexhaustible environment, with the combined vividness of an actuality and flexibility of a dream. Working back and forth between experience and thought, writers have more than space and time can offer. They have the whole unexplored realm of human vision.

There is no certainty when you sit down to write. That's the magic! It's simultaneously a daunting aspect of the process as much as it is the whole spiritual point of doing it in the first place!

While explaining the importance of letting go of certainty in creative process, Matt shares this:

Have you had a good conversation recently? Did you say something interesting, profound even?...If you did, I can almost guarantee that you were accessing your creative self. And I will also suggest that there's no way you could have given that advice, in that way, had you not been having that conversation. It was in the act of conversing, or reacting to energy and idea, that you put thoughts and experiences swirling in your subconscious together into words that became concrete statements in Form as they exited your mouth.

An anecdote to the blank page then is to make the creative process something other than closing the door, turning off the lights, and staring at a blank page. But instead, a conversation.

I've been in a rut with writing music. I haven't been brining reference into my practice. I've been sitting at the keyboard and simply waiting for what comes out. While there's an intuition that's part of the process, the energy is muted when starting this way.

But nothing kick-starts writing music better than listening to something I love, and then wanting to play around with it in my own way. In the same way that visual artists aren't cheating when they draw from reference, it's quite the opposite - the music is more buoyant and fun when it's in conversation with other recordings and musicians.

This conversation-style of creating is baked into the culture of blogging! Much of blogging is finding something you read that was interesting, then adding your own two cents while connecting it to a particular audience. You're adding your own story to the mix, telling the original author's tale in a way that adds a new dimension, compounding it's message, and creating a new audience for the source material.

Creatively conversing turns wonder into art.

I have a need for speed when developing. That goes for starting up all of my development applications.

Here's what needs to happen before I start coding:

1. Open my repo in VS Code
2. Load in any environment variables
3. Start the local server
4. Open up chrome
5. Visit localhost:3000

A short list. But it takes a few key strokes and mouse clicks to get there. I think I can do better!

Linux Alias Commands are the answer.

I have a few set up for a few different use cases. Here's the one the runs the recipe for the steps above:

alias cap="cd /Users/cpadilla/code/my-repo && code . && open http://localhost:3000 && source settings.sh && npm run dev

I'm adding this to my .zshrc file at the root of my account.

That turns all five steps into one:

$ cap

Another use case: I keep a list of todos in markdown on my computer. Files are named by the current date. I could do it by hand every time, but there's a better way:

alias dsmd="touch $(date +%F).md && open $(date +%F).md"
alias todo="cd /Users/cpadilla/Documents/Todos && dsmd"

There we go! $(date +%F) is a command for delivering a specific date format. The todo alias navigates to the correct folder and then uses dsmd to create and open the file.

Small quality of life changes! They add up when you do the same commands every day. 🙂

I'm testing a form on an e-commerce app. I've already looked at testing the components that add an item to the cart. Now I need to setup a test for updating an item in the cart.

My app leans on Redux to store the cart items locally. As per the React Testing Library Guiding Principles, I'm going to be asserting my app's functionality by checking what shows in the DOM against my expectations. Notably: I'm doing this instead of asserting the redux store state. I'll also be integrating a test store and provider in the mix.

The "how" today is Redux. The principle, though, is that if you're using a datastore, cache, or any other source, you want to be integrating it in your tests and not simply mocking it. That goes for either app state management or the data layer.

Mocking

In my app, to accomplish this, I have to render the whole page with the Layout component included:

  const rendered = renderWithProviders(
    <Layout
      children={
        <CartItemPage
          item={chipsAndGuac}
          itemID={'5feb9e4a351036315ff4588a'}
          onSubmit={handleSubmit}
        />
      }
    />,
    { store }
  );

For my app, that lead to mocking several packages. Here's the full list:

jest.mock('uuid', () => ({
  v4: () => '',
}));

jest.mock('next/router', () => {
  return {
    useRouter: () => ({
      query: {
        CartItemID: '5feb9e4a351036315ff4588z',
      },
      push: () => {},
      events: {
        on: () => {},
        off: () => {},
      },
    }),
  };
});

jest.mock('@apollo/client', () => ({
  useQuery: () => ({
    data: { itemById: { ...chipsAndGuac } },
  }),
  useLazyQuery: () => ['', {}],
  useMutation: () => ['', {}],
  gql: () => '',
}));

afterEach(() => {
  cleanup();
});

jest.mock(
  'next/link',
  () =>
    ({ children }) =>
      children
);`

I'm not testing any of the above, so I'm not sweating it too much. It did take some doing to get the right format for these, though.

Redux Testing Utilities

You'll notice that my render method was actually renderWithProviders. That's a custom utility method. The Redux docs outline how you can set this up in your own application here.

That's it! That's the magic sauce. 🥫✨

The philosophy behind it is this: You don't need to test Redux. However, you do want to include Redux in your test so that you have greater confidence in your test. It more closely matches your actual environment.

You can take it a step further with how you load your initial state. You could pass in a custom state to your initStore() call below. But, a more natural would be to fire dispatch calls with the values you're expecting from your user interactions.

Here I'm doing just that to load in my first cart item:

test('<CartItemPage />', async () => {
  const formExpectedValue = {
    cartItemId: '5feb9e4a351036315ff4588z',
    id: '5feb9e4a351036315ff4588a',
    image: '/guacamole-12.jpg',
    name: 'Guacamole & Chips',
    price: 200,
    quantity: 1,
    selectedOptions: {
      spice: 'Medium',
    },
  };

  const store = initStore();
  store.dispatch({
    type: 'ADD_TO_CART',
    payload: {
      ...formExpectedValue,
    },
  });

  . . .

}

From there, you're set to write your test. That's all we need to do with Redux, from here we'll verify the update is happening as it should by reading the values in the DOM after I click "Save Order Changes."

The details here aren't as important as the principles, but here is the full test in action:

test('<CartItemPage />', async () => {
  const formExpectedValue = {
    cartItemId: '5feb9e4a351036315ff4588z',
    id: '5feb9e4a351036315ff4588a',
    image: '/guacamole-12.jpg',
    name: 'Guacamole & Chips',
    price: 200,
    quantity: 1,
    selectedOptions: {
      spice: 'Medium',
    },
  };

  const handleSubmit = jest.fn();

  const store = initStore();
  store.dispatch({
    type: 'ADD_TO_CART',
    payload: {
      ...formExpectedValue,
    },
  });

  const rendered = renderWithProviders(
    <Layout
      children={
        <CartItemPage
          item={chipsAndGuac}
          itemID={'5feb9e4a351036315ff4588a'}
          onSubmit={handleSubmit}
        />
      }
    />,
    { store }
  );

  const pageTitleElm = await rendered.findByTestId('item-header');

  expect(pageTitleElm.innerHTML).toEqual('Guacamole &amp; Chips');

  const customizationSection = await rendered.findByTestId(
    'customization-section'
  );

  const sectionText = customizationSection.querySelector(
    '[data-testid="customization-heading"]'
  ).innerHTML;

  expect(sectionText).toEqual(chipsAndGuac.customizations[0].title);

  const spiceOptions = await rendered.findAllByTestId('option');
  const firstOption = spiceOptions[0];

  expect(!firstOption.className.includes('selected'));

  fireEvent.click(firstOption);

  const updateCartItemButtonElm = await rendered.findByTitle(
    'Save Order Changes'
  );

  expect(firstOption.className.includes('selected'));

  fireEvent.click(updateCartItemButtonElm);

  const cartItemRows = await rendered.findAllByTestId('cart-item-row');

  const firstItemElm = cartItemRows[0];

  const firstItemTitle = firstItemElm.querySelector(
    '[data-testid="cart-item-title"]'
  );
  const customizationElms = firstItemElm.querySelectorAll(
    '[data-testid="cart-item-customization"]'
  );

  expect(firstItemTitle.innerHTML).toEqual('1 Guacamole &amp; Chips');

  const expectedCustomizations = ['Mild'];

  expect(customizationElms.length).toEqual(expectedCustomizations.length);

  customizationElms.forEach((customizationElm, i) => {
    expect(customizationElm.innerHTML).toEqual(expectedCustomizations[i]);
  });
});