Boost Prisma query performance: Add non-unique foreign key indexes, verify with 'explain query' for speed!

Optimize DB queries with user-specific note indexes, boosting performance and reducing scan times.

Optimizing Database Queries with Indexes

[00:00] So I'm going to use the CLI, we'll say SQLite 3, and point it to Prisma DataDB. And then I'll paste in our explain query. And here's that. Explain query plan, select star from note where owner ID equals 1. And our query plan is to scan the note. So a scan's not always a terrible thing.

[00:19] Like it's not instantly, oh, it's a scan, it must be bad. But in our particular case, we're going to be getting lots and lots of notes. And so if any time we wanted to find a note for a single user, you can think of scan as like we're looking at every single record, right? So if we're having to look at every single record,

[00:36] and when our plans for notes is to have many of them, then that seems inefficient, right? So it would probably be a good idea to add a index to that. So let's go to our Prisma schema here. And we're going to add a couple of indexes to our models here.

[00:55] So we'll add an index for the owner ID right here. And the reason we have to do that is because the owner ID is not unique. If we take a look at our migration file here, then we'll see we actually do already have a couple of indexes that Prisma creates for us. And that's for all of the unique fields that we have.

[01:14] So the user has a unique on the email and on the username. And then the user image, it's a one-to-one relationship. So we have a at unique on the user ID. But for this one-to-many, where one user can have many notes, we're not going to have at unique on this. That wouldn't make sense.

[01:33] So we're going to just tell our database, hey, we need to have an index. Make a special record or special place where you have an index for all of the notes by their owner ID. So we'll add index, and that's going to be owner ID.

[01:50] I don't care to specify a name, so thanks anyway, Copilot. So we'll save that. And then let's add one for all of the other models. I just do this as like a default. It's pretty much a pretty solid default for most of the databases to add an index to all foreign keys that are not unique.

[02:08] So index here on the note ID. And also always a good idea when you're doing any sort of performance thing at all to add a comment. So we're going to say non-unique foreign key. And we'll do the same thing on this one as well.

[02:29] And then we don't need an index here because again, the user ID is unique. We have that index right there anyway. All right, so with that now I can say, let's pull up another terminal, npx prisma migrate dev. And we're going to make a new migration, foreign keys.

[02:48] And let's take a look at the migration that was created here. So it created an index for the note owner ID and the note image note ID there as well. And so with that now, if I rerun that explain query plan, and there's our select, then we're going to still get a scan note.

[03:07] And that might be because I need to actually perform the migration. Let's see, yeah, our database is already in sync, but it could be that I need to turn this off and start it up again. So let's get our explain query plan, stick that in there and copy that, stick that in there.

[03:29] And now we're getting what we wanted. We can search the note using index note owner ID, IDX. If you're curious what indexes you have in a given database, you can actually select that from a special table. So we're going to say select name from the SQLite underscore master,

[03:49] where the type is index. And that's going to give us all of the indexes that we've got. We've got auto indexes that are created for us. And then we have the index on those unique fields. And then those indexes we just created ourselves. So at any time, if you want to look at what indexes are available,

[04:08] then that is how you accomplish that. So our query is definitely going to be faster. Normally this is something that you want to try and measure somehow. So maybe create a bajillion notes or something and then test that out and like look at querying a specific user's notes. So like we could go to Cody's notes

[04:27] and measure how long that query is going to take. And then of course you also have logging going on as well. So then you can take a look at those logs as you're navigating to these different pages and just make sure that the queries are shorter than they were before you made the change. So it's a good idea to make sure you measure

[04:44] before you actually implement these indexes. But at the same time, an index and a non-unique foreign key is almost always a good bet. And so I pretty much could say like 99% of the time you should do that. So that is adding a unique or a index

[05:03] on a foreign key that is not unique.

Optimizing Query Performance with Indexes in Prisma

[00:00] For this exercise, we're going to be optimizing a query of the notes by a owner ID. And you're gonna be adding an index for foreign keys to all of the models that have foreign keys, where those foreign keys are not unique. If they're unique, they already have an index that was created for us by Prisma.

[00:19] But if they're not unique, then we need to add an index. So your job is to first understand what's going on. And so you're gonna be using explain query, whether that's using SQLite 3 or the script file that I showed you how to do. And once you understand what's going on there and you're seeing the scan in there and all that stuff,

[00:38] then you're gonna add the index and do the explain again and make sure that scan does not show up and that we can use that index. And this we're gonna do so we can keep our queries really fast. So give that a whirl and we'll see you when you're done.

Data modeling is as a critical skill for the full stack developer. After all, data is the foundation of your application.

If you don't do it right, you'll find yourself stuck before you even get started. A poorly designed database schema can lead to a lot of headaches down the road when you need to make changes or add new features. Inefficient queries can slow down your application, leading to a poor user experience. Analyzing root causes to issues like these can be a time-consuming and frustrating process.

Web developers spend a ton of time into making their UI handle a slow backend well which is great and you should do that too (you don’t control network latency after all). But *****great***** web developers put effort into making it so their users hardly ever experience that great pending UI by making the backend as fast as possible. Your UI can’t be any faster than your slowest database query.

The Data Modeling Deep Dive Workshop will help you develop a keen understanding of databases and how to manage them. You'll learn the same tools and techniques used at the top tech companies to design robust database architectures and optimize query performance.

We'll be using production grade tools like Prisma and SQLite, but the concepts you learn in this workshop will be applicable to other database systems as well.

## Database Schema

A well-designed database schema is the foundation of your application. It's critical to get it right from the start, as it can be difficult to make changes down the road. This section provides a background on SQL before introducing Prisma's suite of tools. You'll learn:

- SQLite database initialization with Prisma
- Prisma's Object Relational Mapper (ORM)
- Defining a schema with Prisma's declarative schema language
- Converting a schema to SQL with the Prisma CLI
- Viewing and editing your database in Prisma Studio

## Relationships

Poorly planned relationships between database models can lead to data inconsistencies and increased query complexities and performance problems. To avoid these issues, you need to understand the different types of relationships and how to implement them. This section provides you with practical experience in recognizing relationships:

- One-to-many relationships (User has many Notes)
- One-to-one relationships (User has one UserImage)
- Updating the database with Prisma
- Planning models for the future
- Generate an Entity Relationship Diagram (ERD) for visualization

## Migrations

As your application evolves, so will your database schema. It's not uncommon for there to be "breaking changes" that affects one or more of your database models. This is where migrations come in.

Migrations allow you to track and synchronize schema changes across environments. Traditionally migrations have been tricky to manage, but this section will teach you how to use migrations to ensure your development, staging, and production environments are always in sync:

- Generating SQL migration code with Prisma
- Best practices for documenting & tracking migrations
- The "widen then narrow" approach for eliminating downtime
- Experimenting with migrations before permanently applying them

## Seeding Data

Manually adding data to a database is not only time-consuming but also prone to errors. Scripting to the rescue!

Here you'll learn how to populate your databases with consistent data for development and testing purposes:

- Initializing a SQLite database
- Idempotent scripting
- Resetting the database with Prisma
- Nesting SQL queries to simplify foreign key connections

## Generating Seed Data

Using hardcoded seed data works great for small applications, but it won't provide the best idea of how your application will perform at scale. You also shouldn't use real user data during development for plenty of obvious reasons.

A better approach is to use a library like Faker.js. This section will teach you how to use Faker.js to generate seed data for your database:

- Balancing realism with practicality when generating data
- Creating realistic user information
- Varying data quantities and randomness
- Validating against the schema
- Enforce data uniqueness

## Querying Data

In SQL, queries are written with the `SELECT` statement. This is one of the most powerful statements available, but it can also be one of the most complex. This is where tools like Object Relational Mappers (ORMs) come in, helping developers write SQL in a more intuitive way. Although it isn't a traditional ORM, the Prisma Client is an indispensable tool for modern web development. In this section, you'll learn how to get the most out of it:

- Understanding how Prisma's API maps to SQL
- Handling Hot Module Replacement (HMR)
- Logging slow queries
- Pre-connecting the client to the database

## Updating Data

Data mutation is a common task, but doing it incorrectly can lead to data corruption or loss. Especially when a single user action results in multiple database changes (for example, transferring money between user’s bank accounts). Getting mutations wrong can be a serious problem for your app’s success. This section will walk you through techniques for working safely:

- Deleting and updating data
- Conditional upserts
- Combining multiple queries into a transaction
- Rolling back to a previous transaction

## SQL

No matter how great an ORM is, sometimes you have to write raw SQL. In this section, you'll learn how to write raw SQL queries and execute them with Prisma. You'll also practice with:

- Wildcard queries
- Runtime schema validation with Zod
- Using joins to enable more complex queries
- Specifying query result ordering for more efficient searching

## Query Optimization

Nobody likes a slow application.

There are many stories of companies spending millions of dollars on scaling hardware, adding multiple levels of caching, and even rewriting their entire application in pursuit of a faster performance– only to find out later that the solution was optimizing their database indexes and queries.

In this final section, you'll learn how to optimize your queries for maximum performance:

- Identifying the index
- Analyzing raw SQL queries
- Multi-column indexing
- Balancing premature optimization with practicality

The Data Modeling Deep Dive Workshop will teach you best practices for designing database schemas, managing relationships, data mutations, and optimizations.

Data Modeling Deep Dive

Kent C. Dodds

Developers working on modern web applications today face a myriad of challenges.

The apps we build are expected to be good looking, fast, and secure. They need to be interactive, responsive, and accessible. They need to be SEO-friendly, and they need to be resilient to errors.

In order to be successful, you need to ensure you have a solid foundation to build on.

Full Stack Foundations is a comprehensive, in-depth workshop that sets the stage for the rest of the Epic Web series. It's designed to equip you with the current best practices for handling CSS, routing, data loading, and more.

This isn't some basic "intro" workshop! Even if you're an experienced developer, you'll learn new skills and techniques that will help you build better apps.

<Testimonial author={{name: "Gwen Shapira"}}>

It was 🤯. A lot of fun, and I learned so much in one day. Even with 23 years as a software engineer, I have a much clearer understanding of how the web actually works. Now, months after this workshop, I’m working on a web project and this gave me the mental framework I needed to quickly ramp up and feel competent. I’m not even using the same tools Kent uses in the workshops! 13/10.

</Testimonial>

## Styling

In recent years, CSS has seen a lot of evolution in its usage and tooling. There are new best practices for importing stylesheets, managing assets, and optimizing styles. Failing to follow them can lead to slow-loading pages and a maintenance nightmare.

Here, you'll learn how to handle styling right:

- Loading external stylesheets & other resources
- Managing assets with fingerprinting and caching
- Compilation with PostCSS and Tailwind CSS
- Bundling CSS for libraries and components

## Routing

The URL is critical for the web, and is closely tied to your application's routing. Making suboptimal decisions affects loading performance, navigation, and overall experience.

This section is a deep dive into routing, from the basic filesystem structure through nested routes and dynamic parameters:

- Conventions for signifying routes
- Layout and URL nesting
- Navigation and linking
- Dynamic route params
- Resource routes

## Data Loading

The web is all about data, and nothing frustrates users more than when loading is slow or broken. This section introduces you to AJAX/fetch and Remix loading features, giving you the tools to build stable, fast-loading applications:

- Working with `Request` and `Response` objects
- Fetching data for initial and subsequent loads
- Accessing environment variables
- Connecting routes to loaders

## Mutations

Interactivity makes apps engaging, but also introduces complexity. You need to ensure that the thing that happens when a form is submitted is what the user expects. Here you will use form methods and actions to mutate existing data in a mock database:

- Creating forms with `method` and `action`
- Storing and extracting form data
- Handling invalid submissions
- Form state management
- Specifying intent

*This module sets the stage for the complete Professional Web Forms workshop.*

## Scripting

The browser is capable of quite a lot on its own. Up until this point of the workshop, the Epic Notes application will work without any JavaScript. However, scripting allows us to enhance the user experience in ways that would otherwise be impossible.

In this section, you'll incrementally add in features like client-side pending UI, prefetching, and optimistic updates:

- Scroll position restoration
- Considerations when overriding default browser functionality
- Server and client bundles & entry files
- Preloading data & code for anticipated actions
- Transitions with the `useNavigation` hook

## Search Engine Optimization

Ignoring SEO is like building a store without a sign— people simply won't find you. If you want to be successful, your site needs to be discoverable and optimized for presentation on those platforms.

SEO is a very broad topic, but this section will get you set up on the technical side of the equation:

- Essential tags and attributes
- Overriding default metadata for specific pages
- Dynamically set metadata for social sharing
- Reusing parent data for nested routes

## Error Handling

Even the best apps can break, but what separates the good from the great is how they recover. Lack of proper error handling can lead to frustrated users and lost business.

This section will teach you how to anticipate, handle, and recover from errors, making your application resilient and user-friendly.

- HTTP status codes
- Using `ErrorBoundary` components to contain errors
- Handle thrown errors when fetching data
- Error bubbling from child routes to parent routes
- Implement useful 404 pages

Full Stack Foundations

Forms are the primary mechanism for interactivity on the web.

But for all of their importance, forms can be incredibly frustrating for developers and users alike.

There are a lot of moving parts to consider: 

You need to validate user input and handle complex data structures, while being secure from bots and data-stealing attacks. The UI should inform users of any issues as quickly as possible, and be accessible for all.

The Professional Web Forms Workshop will equip you with the tools to handle all of these challenges and more.

## Form Validation

HTML has several attributes built-in that help with form element validation. Unfortunately, they're not enough on their own. On the client-side, bad actors can bypass checks by manually adding the `novalidate` attribute or making requests directly. On the server side, there's no standard way to respond to validation errors other than a generic 400 status code.

In this section, you'll implement a base level of form validation:

- Essential HTML attributes for validation
- Server-side validation with JSON responses
- Displaying errors with the `useActionData` hook
- Disabling default browser validation messages with `useHydrated`

## Accessibility

Accessibility is often an afterthought, but should be a priority. While forms are just one part of the accessibility equation, they're a big one. In this section, you'll learn how to make sure your forms accessible and usable by all of your application's users:

- Properly associating labels with input fields
- Adding appropriate ARIA attributes when necessary
- Manage keyboard focus in response to errors and user interactions
- Creating and using a `useFocus` hook to direct the user’s focus
- Considerations for accessibility testing

## Schema-Based Validation

Manually writing conditional checks for every field in every form is tedious and error-prone: *Is there content in the field? Is it the right type? Is it within the acceptable range? There must be a better way!*

Fortunately, the combination of the Zod and Conform libraries makes it easy to define reusable declarative schemas to validate form data on the client and server:

- Safely replace HTML validation attributes with schemas
- Zod error handling & message customization
- Progressively enhanced validation with Conform
- Share type-safe validation logic between client and server

## File Uploads

Forms need to support more than just text. Images, videos, audio files, and other documents need a different type of input. Depending on the size of the file, you'll have considerations for how and where it is uploaded. In this section, you'll learn how to handle file uploads in a secure and efficient way:

- Uploading images from an `ImageChooser` component
- Handling file uploads on the server
- Schema validation for file uploads with Zod

## Complex Data Structures

The `FormData` API does not support representing objects or arrays– only key/value pairs. It also won't allow you to nest forms within each other. This makes representing complex data structures difficult without jumping through some hoops. Luckily for us, Conform has a great solution when your forms get complex. Here you'll practice:

- Infer field types with Zod
- Nesting objects as a form
- Represent arrays of fields with the `useFieldList` utility
- Add and remove items with Conform's `intent` buttons

## Spam Prevention with Honeypots

Spam bots are constantly crawling the web, looking for forms to submit junk data to boost their client’s search engine rankings and find holes in your security. This can strain server resources, contaminate databases, and cause other unforeseen consequences. One way to combat this is by using a "honeypot", which will prevent a form from being submitted by a bot. This section will teach you how to:

- Create a simple hidden-field validation
- Add a configurable honeypot instance to an action
- Prevent form submissions that are too fast
- Use environment variables for encryption seeds

## CSRF Protection

Cross-Site Request Forgery (CSRF) attacks are a type of malicious exploit where a user is tricked into performing an action they did not intend to. This is usually done by embedding a link or form in a page that the user is already authenticated to. Think of a user logged into their bank account clicking a link that transfers money from their bank account to the attacker's. To prevent this type of attack, you'll want to:

- Create a signed cookie in the user's browser
- Include unique user tokens in forms
- Validate tokens on the server via request headers

## Rate Limiting

You definitely want to prevent bad actors from making tons of requests to try to brute-force their way into your app or cause general chaos. However, malicious intent and spam aren't the only reasons you need to protect your site. Even too many legitimate requests can cause problems. Rate limiting is a way to prevent users from overloading your server with requests. Here you'll learn:

- Considerations for different limits for `GET` vs. `POST` requests
- Implementing basic rate limiting with Express middleware
- Tiered rate limiting depending on which form is being submitted

Professional Web Forms

Most web applications feature user accounts in one form or another.

To the user, having a login form seems simple enough. But behind the scenes, there's a lot going on. You need to securely store and manage user sessions, handle authentication and authorization, and protect against malicious attacks.

Without knowledge of the underlying concepts, it's easy to make mistakes (even if you decide to use a third party service or library to do all the hard work for you). Weak authentication systems, misconfigured route protection, and flawed session management all lead to vulnerabilities and negative user experiences.

The Authentication Strategies & Implementation workshop is your guide to implementing industry standard user account flows in a modern web application.

Securely managing passwords, enabling two-factor authentication, and integrating with third-party providers are just some of the processes you will practice. You'll also learn how to handle user preferences, session data, and send emails to users. This will take you all the way to the point where you could easily add Single-Sign On (SSO) support–a must if you’re building a business-to-business application.

There are a lot of moving parts to consider, but this workshop will give you the confidence to tackle them all.

## Cookies

The workshop begins by diving into cookies. This fundamental part of web browsing is often misunderstood, but important to understand. We keep it simple to ramp you up slowly on the web’s original state management solution. This also introduces us to important UI patterns you’ll want to use in other areas of web development as well. It’s surprisingly deep! Topics include:

- Storing user theme preferences in a cookie
- Server-side cookie handling
- Using fetchers for non-navigation mutations
- Optimistic UI updating to apply changes immediately

## Session Storage

Storing user preferences in cookies is fine, but you need to be more careful when it comes to session data. It's vital that users with devtools or malicious client-side JavaScript can't access or modify the data inside of cookies. In this section, you'll learn:

- Cookie-based session data persistence
- Properly getting, setting, and updating session data
- Deleting data after reading
- The flash pattern for short-lived messages

## User Sessions

Depending on the amount of data you need to store, it may make sense to use a different storage mechanism for session data. For this workshop we will continue to use cookies, however we will switch to using a dedicated session cookie. This section will teach you:

- Create a session storage object for storing user data
- Transform form submissions into user objects for storage
- Retrieving user information from cookies then the database

## Passwords

Passwords are the most common way to authenticate that a user is who they say they are. Obviously, storing them in plain text is a bad idea, but even encryption isn't enough. You need to properly prepare to securely handle passwords:

- Create a Password model in the database
- Generate secure password hashes with the battle-tested bcrypt algorithm
- Protection against password attacks
- Update signup route to securely create records

## Login & Logout

At this point, you have the ability to create users and store their passwords. But how do you actually log them in? And how do you log them out? In this one we’re also going to learn about imperative mutations which you’ll definitely want to know for other app features. This section will teach you:

- Verify a user's password hash with bcrypt
- Handling optional vs. required user data
- Guard against timing attacks
- Clearing session data on logout
- Handle edge cases like deleted accounts
- Set expiration dates for session cookies
- Implement automatic logout after a set idle time

## Protecting Routes & Role-Based Access

Most web application have routes that you need to be logged in to access. It's one thing to prevent this in the UI, but the real thing you need to do is protect the data that powers those pages. There are also considerations for different user roles and permissions. This section will teach you how to:

- Ensure users only access data they own
- Restrict private pages to logged in users
- Implement smart redirects for login and logout
- Specify and enforce permissions with Role-Based Access Control (RBAC)

## Managed Sessions

Imagine a user logging in on a public computer, who then forgets to log out. This security risk can be mitigated by implementing a session management feature:

- Add a session model to the database
- Update auth utils, login, and logout routes to query sessions
- Refactor for session expiration support
- Create a page for displaying & managing active sessions

## Email Verification & Password Resets

Email is one of the most common ways to verify a user's identity. If a user loses access to their account, needs to reset their password, or wants to change their email address, you'll need to send them an email. While the actual sending of the email is best handled by a third-party service, the process flow behind the scenes is what you'll need to implement:

- Integrate a third-party email API
- Share session data between routes for multi-step flows
- Style HTML emails
- Generate time-based one time passwords (TOTP) for password resets
- Force verification of email address change

## Two-Factor Authentication

Two-factor authentication (2FA) is a great way to add an extra layer of security to your application. It requires the user to enter a code from a second device along with their username and password. Even if an attacker determines a password, they won't be able to access the account without the second factor. In this section, you'll add 2FA to the Epic Notes application:

- Handle the 2FA enable flow
- Provide the user with a QR code
- Implement a 2FA code input form
- Verify the code and log the user in
- Require re-verification
- Provide option to disable 2FA

## Third-Party Authentication

There are several reasons to use third-party authentication providers. It can be a great way to reduce friction for users, or allow easier access to other data. The most common way to do this is with OAuth 2. Once you’re finished with this section, you’ll be geared up to implement third party auth with any provider. Including Single-Sign On (SSO) flows. We’ll be following these steps:

- Add an authentication strategy to enable GitHub login
- Create a connections model to associate users with providers
- Prevent duplicate account connections & hijack attempts
- Pre-fill user account data based on third-party login
- Mocking third party APIs for testing
- Gracefully handle login errors
- Implement smart redirection to return to original page after login

Authentication Strategies & Implementation

We all know the drill. The code works on your machine but you didn’t realize the impact of the change you made and it breaks in production. This problem grows exponentially as your application’s size and usefulness grows. Unpredictability can be a developer's worst nightmare.

However, many developers find it difficult to effectively test their code. Manual testing gets tedious fast. Complex logic and async code can be tricky to test effectively.

The Full Stack Testing Workshop will equip you with the tools you need to ensure your app works now and in the future. You'll learn how to write modular, maintainable tests that cover all of your code, from the backend to the frontend.

As the author of the de-facto standard testing library for web development (called eh… Testing Library), I have some solid opinions that have led many teams to much more confidence shipping their code. Let’s get you some of that confidence.

## End-to-End Testing with Playwright

With end-to-end (E2E) testing, you can validate a web app from the user's perspective. This is the closest you can get to a real user interacting with your app. In this section, you'll learn the ins and outs of writing a test with Playwright:

- Best practices for setup before running tests
- Simulating user actions like interacting with the UI to perform a search
- Creating a new user in the database
- Cleaning up after tests have run
- Using playwright fixtures for reliable setup/teardown

## Mocking & Authentication for E2E Tests

When your application relies on external services, you need to have a way to test them without actually using them. Similarly, you don't want to fully onboard a new account every time you test an authenticated user flow. In this section, you'll learn how to re-use the same mocks we’ve been using for development to mock third-party services and simulate logging in:

- Use Mock Service Worker to intercept network requests
- Replace email verification with a local file solution
- Attach cookies to Playwright
- Reusing 2FA logic for local testing
- Testing behavior with different HTTP responses

## Unit Testing

Sometimes you’ve got some really complicated business logic in your app. Business logic that is critical to your application. For this stuff, it’s nice to package it up in its own function or module to keep all that logic in one place. This is the perfect situation where lower level unit testing comes in handy. Here, we'll dive into unit testing with the Vitest framework to ensure that each function and module behaves as expected:

- Organizational best practices
- Asserting outcomes with `expect`
- Recording function calls with spies
- Specifying test behavior with Vitest hooks
- Keeping test output clean to avoid cluttering up the terminal

## Component and Hook Testing

When building React components, you should focus on two users: the user who interacts with a component, and the developer who renders it. By using Testing Library, we can avoid mocking React and its hooks and instead test components as they would be used in a real app:

- Render components with Testing Library
- Simulate the DOM environment on a per-test basis
- Querying for missing elements
- Testing hooks in isolation from React
- Stubbing React context
- Cleanup after testing

## Authenticated Integration

Similar to higher-level E2E tests, it is often necessary to simulate an authenticated user. However, we don't want to test the entire user flow every time we make a change. In this section, you'll learn how to test authenticated operations without the overhead of a full E2E test:

- Generating random session IDs
- Associating sessions with users
- Testing authenticated routes
- Asserting user state in responses

## Custom Assertions

Writing tests is good; writing readable and maintainable tests is better. Custom assertions can make your test code easier to understand and modify. Here you'll learn to encapsulate complex checks into simple, understandable assertions:

- Create matchers for specific states
- Share assertions across tests
- Add custom assertions to TypeScript definitions
- Format test output with colors for readability

## Test Database

There are some decisions to make when writing tests that interact with the database. Testing with the development database has benefits, but requires us to remember to delete data after each test. By using a test database, we can avoid accidents while still being able to run our test suites:

- Set up a test-specific database instance
- Seeding a minimal amount of data
- Defining a global setup for Vitest
- Understanding the importance of module import order

Web Application Testing

Kent C. Dodds is joined by expert developers, library creators, and tech industry leaders who share their insights and experience working within the ever-evolving world of web development.

These conversations cover a wide range of topics ranging from the intricacies of database efficiency, the future of authorization, the role of leadership in tech, and everything in between.

No matter your level of experience in web development, these conversations offer a wealth of knowledge and ideas that will inspire you to refine your craft, push boundaries, and become an Epic Web dev!

## Content Management Systems with Alexandra Spalato

Alexandra Spalato, a Developer Relations Engineer at Storyblok, discusses the future of content management systems. She emphasizes the significance of a CMS in simplifying complex processes into user-friendly interfaces and making content creation more accessible to non-developers.

## Leadership in Tech with Ankita Kulkarni

Developer and leadership educator Ankita Kulkarni discusses the intersection of leadership and development. The conversation explores the acquisition of leadership skills, and how to find the balance between coding and leadership roles. Ankita provides strategies for advancing into leadership positions, emphasizing the importance of proactive career management.

## API Mocking with Artem Zakharchenko

Artem Zakharchenko, the creator of Mock Service Worker (MSW), joins Kent to discuss the evolution of MSW from a weekend project into to a powerful open-source tool for API mocking and debugging.

## Enhancing SQLite with Ben Johnson

Ben Johnson is a developer with a 20-year coding career spanning roles from Oracle to creating BoltDB. In this conversation, Ben discusses LiteFS and Litestream, both tools aimed at expanding SQLite's utility. Topics include a dive into SQLite's architectural elements, and how LiteFS enhances scalability on a single node.

## The Evolution of Type Safety with Colin McDonnell

Colin McDonnell, a Developer Relations representative at Bun and the creator of Zod, discusses how Zod has grown from its original usage in medical software into a prominent tool in the TypeScript ecosystem. Colin also touches on TRPC, an end-to-end type safety library, and the challenges it addresses.

## Navigating the Testing Terrain with Debbie O'Brien

Debbie O'Brien, a community advocate for Playwright at Microsoft, discusses the adaptability and versatility of Playwright as a testing tool. She emphasizes its value in various testing scenarios and its role in Microsoft's application testing, including VSCode, Bing, and Teams.

## Simplifying Web Form Management with Edmund Hung

Edmund Hung, web developer at Delivery Hero, discusses how Conform has become a crucial component of the Epic Stack. He highlights Conform's unique approach to form management, which bypasses native browser validation in favor of its own system.

## Scalable Databases and Authentication with Iheanyi Ekechukwu

Iheanyi Ekechukwu, a software engineer at PlanetScale, explore database and hosting options, highlights PlanetScale's scalability and the role of Vitess in the technology. The conversation also covers authentication solutions, and cautions against over-reliance on external services.

## Understanding Web Development with Jacob Paris

Jacob Paris joins Kent to discuss his work on Remix. He highlights some of the challenges of server rendering, including handling user preferences and time zones. One the client side, Jacob emphasizes the importance of server output for correct client rendering, especially when users disable JavaScript.

## The Depth of Software Testing with Jessica Sachs

Jessica Sachs, software engineer at Ionic and a testing & QA expert, joins Kent in a conversation about various testing paradigms. They discuss topics ranging from Test-Driven Development to End-to-End testing, emphasizing the importance of a balanced approach.

## Platform Engineering with Jocelyn Harper

Jocelyn "Josie" Harper, tech lead at The New York Times, discusses her tech journey into roles including platform engineering and system design. Additional topics include backend optimization and the significance of documentation.

## Exploring the Front-End Ecosystem with Mark Dalgleish

Mark Dalgleish, co-creator of CSS Modules and contributor at Shopify, discusses the evolution of CSS, React, and design systems. Touching on CSS Modules and CSS-in-JS, the conversation also goes into the challenges of managing design systems, particularly when it comes to balancing flexibility with standardization.

## Navigating Changing Web Technologies with Mark Thompson

Mark Thompson from Google's Angular team joins Kent to share his career journey, the web's evolution, and AI's role in software development. Additional topics include the transformational role of APIs and Angular's transition from the View Engine to Ivy.

## The Magic of TypeScript with Matt Pocock

Matt Pocock, internet-renowned as the "TypeScript guy", discusses the merits and challenges of the TypeScript ecosystem. Highlighting its benefits in IDE capabilities and bug reduction, Matt also touches upon complexities in managing types and creating libraries.

## Building Deep Skills with Michael Chan

Educator and enthusiast Michael Chan shares his thoughts on the changing React landscape, emphasizing the significance of TypeScript and libraries like React Query. He advocates for building a deep understanding over surface-level knowledge. Both Michael and Kent stress the joy of creating meaningful projects and the importance of a comprehensive web development understanding.

## Examining MDX with Monica Powell

Monica Powell, a senior software engineer at Newsela, dives into MDX, which allows for developers to combine JSX with Markdown for creating rich content. She highlights MDX's role in the Unify ecosystem, as well as its challenges when getting started with CMS integration.

## Efficient Form Management with Sandrina Pereira

Sandrina Pereira, a staff front-end engineer at remote.com, emphasizes the significance of web accessibility using the POUR principles. Additional topics include the complexities of managing large-scale forms dynamically for users across the world. 

## Transitioning from Rails to Remix with Sergio Xalambrí

Sergio Xalambrí shares his experience transitioning from a Rails and Next.js setup to Remix at fintech startup Duffy. Some of the challenges include highlighting challenges in internationalization and adding middleware. The conversation also into remix-auth's authentication strategies and contrasting Remix's modular approach with Rails' cohesive framework.

## The Capabilities and Ecosystem of Tailwind CSS with Simon Vrachliotis

Simon Vrachliotis, a Tailwind expert at Thinkmill, praises the benefits and adaptability of Tailwind CSS, emphasizing its alignment with React's component-level concerns. Simon discusses solving for type safety in addition to recommending tools for enhancing the Tailwind workflow.

## The Crucial Role of Database Optimization with Tyler Benfield

Tyler Benfield, a software engineer at Prisma, dives into the intricacies of database efficiency, emphasizing backend optimization for enhanced frontend performance. Tyler also explores the advantages and limitations of ORM tools like Prisma, the potential of SQLite in production, and the universal importance of SQL knowledge across platforms.

## Product Management with Nevi Shah

Nevi Shah, a product manager at Cloudflare, combined her technical and business skills to  transition from academia and consulting into product management. She highlights the role of a product manager in understanding user needs and introduces Cloudflare Pages as a solution for serverless architecture, while also discussing alternative hosting options like Fly.io.

## Remix Behind the Scenes with Ryan Florence

Ryan Florence, co-founder of Remix and React Training, discusses the innovations behind Remix and its transition from React Router. The framework's compatibility with the web's fetch API broadens its server architecture reach, and its recent acquisition by Shopify promises a bright future for Remix.

## From Tech Sales to Engineering with Shaundai Person

Shaundai Person, a senior software engineer at Netflix, joins Kent to discuss transitioning from tech sales to software engineering. She highlights the importance of recognizing and building individual strengths, especially communication, in large corporate settings.

## Art, Code, and Data Visualization with Shirley Wu

Shirley Wu, an award-winning creator specializing in data-driven art, transitioned from the business sector to web development. She shares how her work offers artistic fulfillment and financial stability, but she emphasizes the importance of ethical considerations.

## Understanding the Future of Authentication with Will Johnson

Will Johnson from Auth0 highlights the limitations of password-based authentication and discusses the potential of alternatives like the Web Authentication API (WebAuthn). Will shares his anticipation for a future where biometrics or digital verification replace traditional passwords, as well as a need for more nuanced access controls in growing organizations.


Interviews with Experts

Full Stack Vol 1

Intro to Data Modeling Deep Dive Workshop

Intro to Data Modeling Deep Dive

Intro to Database Schema 

Learn how to set up your database using Prisma, including initializing the schema and configuring new files.

Learn how to create a SQLite database using Prisma, define models with unique fields, and explore data using Prisma Studio. 

Database with Prisma and SQLite

Setting Up the Database with Prisma

Dad Joke Break Database Schema

Database Schema

Intro to Data Relationships

Create a one-to-many relationship: Users own multiple notes; note model handles the relationship, user model stays unaware. 

Learn how to establish one-to-many relationships between models in Prisma, including configuring relationship attributes and avoiding common pitfalls.

One-to-Many Relationships in Prisma

One-to-Many Relationships 

Optimize image storage in a DB: no polymorphism, future-proof for web apps. Learn why it's best for new projects.

Master Prisma relationships: one-to-one, one-to-many, foreign keys, cascading, unique constraints. Dive into DB schema design flexibilities & limits.

One-to-One and One-to-Many Relationships in Prisma

Database Design

Dad Joke Break Data Relationships

Data Relationships

Intro to Data Migrations

Master Prisma migration: create tables, modify props, spin up DBs, deploy across environments with confidence.

Prisma migrations: Seamlessly evolve your DB schema, reap benefits of flexibility and easy updates.

Database Migrations with Prisma

Creating Migration Files for Database Management with Prisma

Dad Joke Break Data Migrations

Data Migrations

Intro to Seeding Data

Configuring Prisma for seamless data seeding using seed scripts in your application.

Creating and Managing Data with Prisma Seed Scripts

Configuring Prisma for Seed Script Execution

Leverage Prisma nested mutations for efficient data handling, query optimization, and seamless retrieval. 

Efficiently manage relational records with nested queries. Explore benefits and considerations of nesting in DB ops.

Nested Queries in Database Operations

Optimizing Queries with Nested Mutations in Prisma

Dad Joke Break Seeding Data

Seeding Data

Intro to Generating Seed Data

Automatically generate realistic fake data for efficient testing with powerful tools.

Use Faker.js for realistic random user and note data, automating database population matching your schema. 

Generating Random User Data with Faker.js for Database Seeding

Generating Fake Data for Efficient Testing

Effortlessly generate dynamic web app data: user profiles, images, notes, with time-saving techniques.  

Create dynamic seed data with Faker and Prisma: random users, notes, images. Boost data generation efficiency and flexibility.

Generating Dynamic Seed Data 

Dynamically Generating Data

Enforce Unique: Seed user data with unique values, reduce collisions, and optimize queries.

Create seed data with unique valid usernames, enforce integrity, and safeguard against failures.

Generating Unique and Valid Usernames in Seed Data

Creating Unique User Data with Enforce Unique Library

Dad Joke Break Generating Seed Data

Generating Seed Data

Intro to Querying Data

Learn how to migrate from an in-memory database to a real database using Prisma and SQLite, enabling the loading of actual data in your application.

Optimize Prisma Client: Lazy connections, custom logging, and speed up app with query performance.

Optimizing Prisma Client for Efficient Database Operations

Connecting to a Real Database with Prisma and SQLite

Transition from in-memory to real database with Prisma, efficient SELECT queries for user data, type safety advantages.

Shift to Prisma SQLite DB, type-safe queries for specific fields, end-to-end type safety in web apps. 

Using Prisma to Retrieve User Data from a SQLite Database

Transitioning to Real Database with Prisma API

Learn how to optimize note loading by combining multiple queries into a single query and migrating to Prisma for real database integration.

Optimize data retrieval with Prisma, subselects for efficiency, TypeScript error-catching, and migration guidance. 

Efficient Data Retrieval with Prisma and Subselects

Database Queries and Migrating to Prisma 

Dad Joke Break Querying Data

Querying Data

Intro to Updating Data

Delete data from the actual database using Prisma. This exercise introduces a straightforward mutation before diving into more complex concepts.

Discover how easy it is to delete data using Prisma's syntax and APIs. 

Deleting Data with Prisma

Mutating Data with Prisma

Mutations for updating images and notes on a page, including cache control considerations and utilizing helpful utilities for easier data manipulation.

Learn how to update and delete data in a form using Prisma, while handling image updates, removing deleted images, and creating new images. 

Efficiently Updating and Deleting Data in a Form with Prisma

Updating Page Mutations for Images and Notes with Efficient Cache Control

Learn how to use transactions to ensure that data operations within your application either succeed completely or fail entirely. 

Learn how to use prisma.transaction to ensure a group of database operations either all succeed or all fail together

Implementing Transactions with Prisma for Atomic Database Operations

Implementing Transactions for Reliable Data Operations

Learn how to use nested queries in Prisma to combine multiple database operations into a single optimized query, reducing the number of calls made to the db. 

Learn how to optimize database updates using Prisma by combining multiple related queries into a single transaction, resulting in cleaner, more efficient code. 

Efficient Database Updates with Prisma's Nested Queries

Optimizing Database Calls with Nested Queries in Prisma

Dad Joke Break Updating Data

Updating Data

Intro to SQL

Master advanced user search with Prisma: Write raw SQL queries, iterate from basic search to SQLite database display.

Search users with Prisma raw queries by criteria, handle TypeScript hurdles, prep for adding images.

User Search with Prisma and SQL

SQL Queries for User Search in Prisma

Tackle TypeScript errors in Prisma raw queries, ensure type safety with Zod runtime checks. Weigh Prisma's trade-offs.

Embrace runtime type checks in Prisma for robust DB interaction, handle schema changes and parsing errors.

Runtime Type Checking and Parsing in Prisma with Raw Queries

Handling TypeScript Errors and Runtime Type Checking with Prisma and Zod

Efficiently combine data with SQL joins: inner, left, right, full. Get user profile photos without performance issues.

Master left joins in Prisma/SQLite, resolve conflicts, enrich schema, and improve UI with extra properties.

Left Joins with Prisma and SQLite

Working with Joins in SQL

Sort users by most recent note updates using Prisma raw SQL queries. Follow step-by-step instructions.

AI assistants like GitHub Copilot for SQL queries: benefits, best practices, code review, productivity, accuracy. 

Leveraging AI Assistance for Writing SQL Queries

Sorting Users by Recent Entry with Raw SQL

Dad Joke Break SQL

Intro to Query Optimization

Enhance slow user search with single index in SQLite, sidestep caching complexities, grasp query plans, and boost DB performance.

Learn how to analyze query plans and optimize query performance by adding indexes to improve speed and avoid unnecessary scans and temporary data structures.

Data Modeling Deep Dive

Data Modeling Deep Dive

Transcript

Optimizing Query Performance with Indexes in Prisma

Transcript