Create Tables with Drizzle ORM

[00:00:00]
>> Speaker 1: All right, so in our source, we're going to make a new folder We're going to call it DB, and inside of DB, we're going to have our schema

[00:00:00]
Cool, let's make some schema So the first thing is we're going to import a bunch of stuff from Drizzle ORM

[00:00:00]
We'll import from Drizzle ORM slash PG Core We'll get a bunch of stuff, so we're going to get PG table

[00:00:00]
This is how we make our tables We'll get a UID This is how we make our UID primary keys, Varchar We'll talk about that in a little bit

[00:00:00]
Texts, timestamps, Boolean, Integer Cool So, we'll have all those and then we're going to import the relation from Drizzle ORM

[00:00:00]
This will help us make end-to-end tables and other relation tables First, let's make our user tables

[00:00:00]
We'll export the Drizzle tables so we can reference them when we query inside the code I always pluralize them and then use PG table

[00:00:00]
The first argument is always going to be the name, which I keep the same as the variable in most cases, so in this case, users

[00:00:00]
The second argument is an object in which we will add our fields or columns I'll say I want an ID I want this to be a UUID that's going to be a function

[00:00:00]
Most column functions take a first argument, which is a string What you call it here is what the ORM will refer to it as and what will be in the database

[00:00:00]
So I typically keep those the same I want this to be the primary key, so I'll set it as primary key with a default of random UUID

[00:00:00]
Email, I want to do Varchar Varchar is text with the ability to constrain the length I'm putting this here to show what it can do

[00:00:00]
I don't necessarily think you need to constrain people's emails I'll set a length of 225, not null by default

[00:00:00]
I'll also put a unique index on this An index is a separate place in a database where keys and locations are stored, which can be quickly retrieved

[00:00:00]
Without an index, you'd have to scan the whole database to find something Indexes help you quickly locate the position of a piece of data in constant time

[00:00:00]
It's like trading off scanning the whole table for quickly scanning an index table Putting a unique index on email means two users can't have the same email

[00:00:00]
It allows the database to quickly determine if an email already exists without scanning the entire user table

[00:00:00]
I'll continue creating fields like username, password, first name, last name, created at, and updated at fields, following similar patterns and best practices for database schema design

[00:00:00]
I'll then create habits, entries, and tags tables, establishing foreign key relationships between them

[00:00:00]
For example, the habits table will have a user ID as a foreign key referencing the users table, with a cascade delete option

[00:00:00]
The entries table will have a habit ID foreign key, and the tags table will have its own structure The many-to-many relationship between habits and tags will be managed through a join table that creates connections between these two entities

[00:00:00]
This allows querying relationships in both directions - finding tags for a habit or habits for a tag

[00:00:00]
Each relationship will create database entries that link the related entities, allowing flexible and normalized data storage across the schema.