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.
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
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
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
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
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
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
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
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