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

[00:00] To get us started, let's run npx prisma init, and we're gonna provide the flag url so that it knows that we wanna make a SQLite database, and we wanna add file colon .slash data.db. That .slash is gonna be relative to the Prisma schema that it's gonna create for us,

[00:16] which will live inside of Prisma, the directory. And so it's telling us, yeah, don't, like we already have a .gitignore file, don't forget to add the .env. You'll notice we actually don't have the .env in our gitignore, and the reason for that is because we're in a workshop and I want to commit that.

[00:35] So in your actual apps, you definitely don't want to commit that .env because it will contain secret things. But it also created a new file for us, and in addition to creating that new file for us, it also added some stuff to this env file. That's the database url.

[00:54] And so Prisma will use this environment variable to know where our database is. And because we're using SQLite, the url to our database is a file. If we take a look at the other file it created, let's look at the Prisma directory. We've got our schema.prisma file right here.

[01:11] This is our Prisma schema file. And of course, you can learn more about it at those docs. We have a generator. So the client generator, this is our Prisma client JS, is the thing that's responsible for turning this schema into a thing that you can use. So there are actually a number of generators,

[01:29] and you can even make a custom generator if you so choose. But yeah, we're only gonna use this client generator for creating our Prisma client. Then we also have our data source. We've got SQLite as our provider because of the url that we provided. It interpreted that as a SQLite provider.

[01:48] And then our url is gonna be this env database url. Now, of course, you can provide a actual database. You could say data.db if you wanted to just provide that. But it is really nice, especially in a production environment, to be able to control where that database lives based on this environment variable,

[02:06] which is why that exists. So the next thing that we need to do is add some stuff to our schema. So we are going to create ourselves a user schema, or a user model in our schema. And oh my gosh, look at this. Copilot decides that that's what we want. So we're gonna look through this though

[02:27] because it's not gonna be quite right for what we want. And in fact, you know what? I'm gonna help Copilot a little bit here by copying all of the instructions and then Copilot will be able to know exactly what we want and I'll kind of walk through each one of these as we go. So here's our model and we've got our ID. We want that to be a string.

[02:46] I have no idea why anybody in the world would want an int, but Copilot really wants our IDs to be integers. So just watch out. If you're using AI assistants, don't let them give your IDs integers. No, no, we don't want that. And then we have at ID. This indicates that it's the primary key for our model, which is exactly correct.

[03:05] And we want to default to a CUID. That is a collision resistant universal identifier or unique identifier. And there are various others. You can do a UUID and these will look slightly different. Pretty much it's sixes on what you end up using.

[03:24] I like using CUIDs because I like to deploy my app to multiple regions when I have multiple instances of my app running. And I just really liked the idea of collision resistant in that way, especially when two multiple systems can be creating IDs at the same time.

[03:44] Now, the other thing that I like about CUIDs is they're a little shorter and I think they look a little nicer than a UUID. But you do you, I will do CUID. All right, so then we have our email for all of our users. That's gonna be unique. We can't have two users with the same email. That would be kind of confusing for most cases actually,

[04:04] but our case for sure. Username is how our users are gonna log in. So that also needs to be unique and that's gonna be a string. And also it's required. All three of these fields are required. And then we are going to have a name, but this one is gonna be optional. Users don't have to supply their name. So we add that question mark after the string. And then created at.

[04:23] So created at is pretty much a field that I add to most of my records, created at and updated at. They're just kind of metadata about the data that I have in my database. I not always do all tables necessarily need to have a created at and updated at,

[04:41] but I find it pretty helpful for tables, especially those that are managed by the users and users can create their own records and things. So created at is a date time defaults to right now. So you don't ever have to supply the created at. And then updated at has its own directive here called updated at.

[05:00] And so Prisma will automatically keep that updated at updated for us. So anytime there's an update to this particular user, then it will get an updated at. And that is our model. So the next thing that we need to do is get our model in the database. Now we don't actually have a database yet

[05:20] in our file system. We're about to get one. So let's come over here and we'll say NPX Prisma db push. And that's going to take our schema, convert it into some SQL statements and then execute some SQL statements against our database. Given that our database didn't exist yet, it created a new one. Now I've got a cool extension

[05:40] that is part of the extensions that are listed in the recommended ones for the workshop. And this is going to display all of the data that is currently in my database, of which there's none except for the existence of a table. This extension doesn't allow me to make any changes to the database,

[05:59] but I do have something that will. NPX Prisma Studio. And this is gonna pop open a new tab. So let's come over here. There we go. That's a spoiler alert on the dad joke, sorry. No worries. Okay, so with this, we can open up the user.

[06:19] We can add a record. And in my instructions, I told you to create a record of a user with the email Cody at kcd.dev. And then the username is lowercase Cody. And then the name we're gonna provide is uppercase Cody. Now with these italicized bits, that's just saying,

[06:37] these will be automatically generated for you. You're welcome. So we'll save that change. And there it is. It's right there. We can refresh and we'll find that data is still there. It generated these values for us. If I come in here and open up the data to be again, then it's showing up in this extension as well. But we can take this even further.

[06:56] So I have a tool, SQLite 3 installed on my machine. There are instructions on how to get that installed on your machine that you can take a look at if you wanna do this too. It's called SQLite 3, not 2. And we're gonna point it to our database at DataDB.

[07:13] And we're gonna call the .dump command. This is gonna dump out all of our data into a SQL file. So we'll send that over to data.sql. And that shows up right here. So this is everything that it would take, all the SQL commands it would take to replicate our existing SQL database

[07:33] as an entirely separate file. So it's kind of interesting. We have our begin transaction and then commit. And we create this table. If it doesn't exist, we're gonna insert this user into the database, this user data. And then we're creating some unique indexes.

[07:51] So this is something that is actually created for you automatically by Prisma, based on the fact that we signified that these are unique fields. And with that, we are able to, like if we were to apply this or run this same SQL against another database file, then we would actually be able to create

[08:11] our entire database from scratch. This can actually be kind of helpful if you want to seed a production database with some initial data or something like that. You can do it all locally and then push it up and import it into another database or something like that. But if at any time, as you're going through the workshop, you're like, what in the world is in my database?

[08:30] You can always execute that command, which we executed right here. SQLite 3 Prisma db.dump, and then point it over to SQL or data.sql. And that will give you the SQL that is generated. SQL, to be frank, not my favorite thing

[08:49] to spend a lot of time doing, but it is an extremely powerful language and quite declarative, I must say. So there you go. We have an environment variable set for our database URL. We've got our schema set to accept that environment variable to know where our database is.

[09:06] We have our client to generate our client, which we'll talk about soon. And then we have our model and we're able to run the Prisma Studio so that we can look at things and actually make mutations in our database. And then we've got our output data SQL just to explore things.

[09:26] So that is our first look into creating a new database with Prisma.

Setting Up the Database with Prisma

[00:00] Let's get our database set up with prisma. We're going to be running a couple prisma scripts to get things going to Initialize our schema, and then we're going to add a couple things to our schema So there's most of this is going to be new files that we're creating and stuff So you're going to want to follow the instructions that kind of outlies

[00:18] Outlays the files that you're going to be creating and the scripts that you're going to be running we're actually not going to be pulling up the Application for a little while until we get everything all Situated with our database and then typescript is happy and then once we get all that then we can start pulling up the app But yeah for this first one you're going to just run a couple scripts

[00:38] Open it create a couple files, 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

Full Stack Vol 1

Intro to Data Modeling Deep Dive Workshop

Intro to Data Modeling Deep Dive

Intro to Database Schema 

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

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

Optimizing Query Performance with Indexes in Prisma

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

Setting Up the Database with Prisma

Transcript