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