✨🔴 | 02 - Implement a SQL Database for a Home Inventory App with knex.js, docker and PostgreSQL.

[Music] hello friends welcome to coding garden with CJ look at all these drops welcome everyone in this episode we are going to implement the sequel database that we designed last time we're gonna use connects Jas to implement it welcome to the show and if you didn't tuned in last time we created this diagram if you look in the description of the video there is a link to lucid chart and you can see this diagram in real time as I work on it I'll say hello to everyone before we before we get too deep into it but I'll also review the diagram that we created and feel free to ask any questions you may have about this diagram and there are also a few things that we'll need to fix on it but we'll get to that let's say hello to everyone hello wheels gaming welcome hello Justin hello rich hello may hello scrimpy good morning Justin I'm glad you made it hello come all hello poach oh hello lizard keeper hello Himanshu hello dominicano hello are you but hello I use hello Fetty nice people got the notification yeah so I have a mobile app that's in alpha testing right now that'll send a push notification when I go live so hello agree see root hello Andrew in feet are you sure about that because I'm pretty sure I fix the typo hello zoom john welcome nice refactor I got the iOS notification that's good hello flu if you do exclamation mark project you'll see what we're gonna do but basically we're gonna take this diagram and implement it typically you could implement this directly with sequel code for creating all the tables and creating the relationships but we're gonna use a library called connects Jas which is my preferred library for working with sequel databases in JavaScript and ojs gone was it I think the typos guy yeah welcome Morpheus hopeful hopefully I could take your mind off things for for an hour or two um oh yeah and I'm I'm pre-empting how wants to take bets on how many people are gonna ask what theme I'm using but if you exclamation mark theme you can get a link to the theme that I'm using I'm trying a new one out and it's pretty cool I like the color scheme it's called just black you'll get a better idea of what it looks like when we start writing some code but yeah who wants to take bets how many people are gonna ask about my theme Alana got way too many notifications well let that's not necessarily a bad thing and hello Sardar welcome hello Scott yeah it'll be totally from scratch so of course feel free to ask any questions you have about connects but I'm gonna try to explain it from the ground up and yeah that's the plan so yeah feel free to ask anything connects genius so I'll talk about it in a second but connects GIS is a query builder so there are things called ORM S which are object relational mapper z' is then there's something like connects which is simply just a query builder come on and it's also a tool for creating your schemas for doing the data definition language part of sequel but something like sequel eyes it can be done like model first what we'll talk about it and we'll talk about the differences yeah and hello gurgling let's see if I can get all the hellos in hello I ollie and all the other isolated developers hello pintura derp hello Jeb hello yes I'm doing pretty good I got plenty of sleep I'm feeling good ready to write some code hello Kamal hello Freddy bugs it's been a while welcome hopefully you're doing okay hello Kuno hello I'll tube hello in 32 zero-day hello the Ox hello Sebastian hello pumpkin freak pumping freak hello oak it's Ron hello for me good morning wassouf good morning eternal dev coder hello phantom hello Lois hello Marie she Diego says I didn't see the first stream but what's the purpose of the database just food inventory or everything in your house the plan is to do everything in my house typical like food inventory works really well because if you have a look like a large stock of food and you want to keep track of when there are things gonna expire or how much do you have and we'll use it for that but I also have a lot of other random things like electronics components and laptops and devices and so really the way we designed this was to be very generic and just talk about things as a product and it's almost like we're designing an inventory system for a store it can really hold any types of inventory but yeah so that's the plan and hello are you good morning el hello eternal deaf coder again hello Linus it's been a while welcome hello Ali hello coding pasta it's also been a while welcome back hello Rodon good morning agent kee spread the love nice welcome Satyam your first stream hello Shawn hello Edie disuse hello us yeah hello mine up hello flout was that a bad word I don't know hello game step hello George hello sue John um hey hey welcome everyone hey Joe mama what's up okay so that's about all the hellos thank you I got a few more hello zacchara hello Marcos hello Vince if you have Legum engine if you have any questions feel free to ask them we'll try to get to all of that but before before we get too deep into it I am going to review the database that we designed and actually while I'm reviewing it we're gonna do a poll so we're gonna implement this and we can use pretty much any sequel database so one of the cool things about connects jeaious is it has adapters for all of the popular sequel databases my sequel or Maria TBD Postgres sequel light Oracle Microsoft sequel so you can use the same library to talk to any of those databases and so I'm giving I'm putting it in the hands of the viewers what are we going to use and you have two options my sequel or Postgres because when we set this up we're probably gonna do a docker container to spin up that database I mean technically we could do sequel light and it would actually make it a lot easier because that sequel light is just a flat file database but two options my secret or Postgres so go here to vote and whatever has the most votes that's what we're gonna try to use and we will attempt to set up that particular database with a docker container so that'll be fun to do as well and thanks for the follow for beachy Oh welcome ooh Microsoft sequel I don't so I worked with Microsoft sequel a long time ago I don't know if you could put that in a docker container doesn't it have like licenses and stuff like that but yeah will I do a live stream where I build a front-end that displays information about the coronavirus I didn't plan on it I'm doing some stuff with api's that I may talk about or stream but yeah it's like Microsoft's we go so I can't use it and hello everyone from India yeah pendulous saying India is locked down for 21 days so I live in Denver Colorado and they just issued a like a shelter-in-place stay-at-home protocol so we are not allowed to leave the house unless for a good reason like it you can go grocery shopping you can go for a run or a walk but for the most part you have to stay home and there's like a thousand dollar fine if you don't do that so people are or governments are starting to take it very seriously and oh but we'll see you later Andrew hope you enjoy the class I guess it's an online class right and hello everyone you can run Microsoft sequel and docker okay but we're not going to do that only because it's been like years since I've used it hello tats oh it basically means chives low Sheen it low it sounds like a curse word in English I've used Redis and I've looked into memcache yeah I vote Oracle cool and the notification came through but it says no what's up with that Oh a system UI what um twitch says you can chat in 11 hours oh it may I don't know I don't know there are too many things on the screen we're gonna disable the drop game once we start talking about all this stuff could I explain multiplicity in UML no because I've never heard of it we can look it up though this is technically an entity relationship diagram it's not a UML but it's somewhat similar to a UML I think yeah New Zealand is locked down for four weeks no fast food shops open at all Wow yeah so in Denver restaurants can't have people sit down to eat but they can do takeout and delivery yeah yeah Nepal South Africa honestly the United States could could issue a lot more restrictions they're not and they probably should and it's only gonna get worse if they don't yeah - is in Switzerland all stores are closed and you also have to have a good reason to leave your house yeah it's crazy oh nice that's good to hear my just a guy and hello are from Pakistan what about so I mean technically you could implement this model in but and we didn't really talk about this last time but is a no sequel database and we have created a sequel design and the thing about sequel databases is that built into the database is a way of creating relationships between tables and enforcing those relationships so when I talk about the database model in a second when I talk about these tables being related a database management system like my sequel or Postgres is going to enforce those relationships if you use something like technically you could manually create relationships between document stores but MongoDB doesn't care it's not enforced at the database level and thanks for the follow Lily good to see you as well kiss my pixels yeah I like Postgres a lot oh you're welcome greetings from Poland very cool do I have a good resource for database design um check out my last video honestly it's not like it's not a very structured lesson we kind of just talked about what do we want and then I show you how I would model that definitely check out sequels ooh it's like a free website to learn sequel they have a bunch of exercises sequel basics I believe yeah so you can they have like a sample database that you can create against and you can learn all the basic syntax for querying if not about modeling yeah in Australia we are free oh nice that's good to hear and thanks for the follow hack run I vote rolodex cards we can't trust computers during these troubled times you're so right John sugar we should just do it by hand okay Netherlands has a similar soft lockdown the thing is it's only a matter of time before they do a full lockdown they should just go ahead and implement it honestly I don't know and thanks for the follow demons ETV it's a more of a curfew okay thanks for the follow of the PW it meant what did my not say send your message again mengapa don't think I it might have been too long ago and that's probably not how you pronounce your name I don't know how to pronounce is it's Kurla Cyrillic yeah and hello Kevin hello honey yeah that's a little bit off topic for today have I messed with neo4j I have I haven't done anything big or official with it yet and yes I am working from home Topher says I've used my see Microsoft sequel for so long but the last time I tried to use my sequel it was very difficult yeah hello Topher welcome it's the same thing with just a guy like I stay home most of the time anyway so this is no big change for me in house part yeah that more than two people can yeah and so there's similar restrictions in place where you have to be at least six feet away from other people no gathering no gatherings at all yeah okay let's do this we're going to disable the drop game great drops everyone good work eventually we'll build a front-end for this app but we got to do one step at a time we designed the database today we're gonna implement it eventually we'll create an API on top of the database and then eventually we'll build a front-end or a mobile app to talk to this app okay but let's talk about what we built here so we designed a a system to store inventory and I'm calling it a home inventory system because I'm most likely just gonna use it to keep track of all the things in my house last time we showed baked beans and talked about the idea of having multiple quantities of something and there's different aspects like the size of this thing and the manufacturer and the expiration date and all that kind of stuff and so I'm gonna keep track of all the food in my pantry as well as other things so one thing to note about this database model is every single record will have a created at time and in update at that time and for this database model we're doing what's known as soft deletes so we will never ever actually remove anything from the database if we need to update any for like remove it we just set the deleted at time as like a date time a timestamp and our system like the front-end if we eventually build one won't show anything that has a deleted at date but that gives us the potential to restore things that were deleted and also because we're dealing with a lot of relationships here if you were to delete one thing if it has certain dependencies you would need to delete those other things as well that's known as a cascading delete but it could result in a lot of things being deleted so we're doing soft deletes and ultimately it's a home inventory system so we have users multiple users can live in a household and manage the system we have items item is like a product it is a very general thing that describes any item in our system we'll have an item type things like canned goods Electronics dry-goods all sorts of item types we haven't added the comment table yet but eventually we'll add that so users will be able to comment on the items in our inventory we have a manufacturer entity so every item in our system has a manufacturer we have an item location so that's a location in my house whether it's the pantry upstairs or my living room or the pantry in my basement every item will get a location and I don't believe we added the purchase location yet but I think we're gonna implement what we have and we can always add new tables later so let's look at this database model first up you have the user entity and um this has an email a name and a password and last time we added this last login field here and I'm thinking we could potentially have a separate table that stores the date/time of every successful login instead of just the last login that could complicate things we'll leave it there and then any user can create an item so an item is the general description of an item so this here is a can of Bush's baked beans and this would have one entry in the item table and the item table has all of the information that's general to every single can of big of Bush's baked beans so it has the name it has the description it has the weight the SKU which is like the barcode on the back and so we have that table and there's only gonna be one entry for Bush's baked beans but as you can see I have two two cans of Bush's baked beans and so to keep track of that we have a separate table called item info and so for every instance of this particular brand and flavor of Bush's baked beans we're gonna have a row in the item infinite info table and the item info table is information that's explicit about each individual can of beans so each can of beans will potentially have a unique purchase date an expiration date a unique last used date a unique price and so for every unique can of Bush's baked beans we're gonna a row in this item in info table and we can see that it relates back to the item table so it has a foreign key reference to the item table so any entry in here we can relate back and see what is the general information about that item like its description and different things like that we can see that an item also has an item type and the item type like I mentioned are things like canned goods electronics other things like that and so one item can have one item type and that's a foreign key relationship to the item type table one item can potentially have multiple images attached to it so we can see that the item images which someone commented I forget who it was but one thing we were doing is all of our entities we're naming them in singular instead of plural because an entity describes one instance of a thing whereas multiple records or multiple rows in the table would be plural so this should be singular we're gonna call that item image but for every image we have for this I could take one picture of the front one picture of the back etc each one will be inserted into this table and it has a foreign key reference back to the item table so every image is tied to a specific item and then we also allow to add related items so you might say that Bush's baked beans original is related to Bush's baked beans bacon flavor because they're very similar so we can link those and then every item also has one manufacturer and a manufacturer has a name logo a description and a type a website and the manufacturer can be something like Bush brothers and Company P o box 5 2 3 3 0 department C and so we'll have an entry in the manufacturer table and that relates error has one address and so there was a comment on the last video that mentioned shouldn't this address to manufacturer be a one-to-one relationship instead of a one-to-many relationship and that's probably true but one thing that could happen is you could potentially have two different manufacturers that have the same address I don't know exactly why that would happen but it might actually happen so this could be a one-to-many but otherwise it's probably a one because one address relates to one manufacturer and you don't really repeat an address potentially but we're storing the address separately and it has all the info and then we're also storing the states and the countries in separate tables and so the state is going to be a lookup on the state table or the state ID and the country is going to be a the country ID will be a lookup on the country table and so this is a lot of tables and there's a lot of relationships here but eventually as we get further and further into this you'll see why we have these separate tables like if you ever go to a website and it has you enter your in your address but a manufacturer can have more than one address I guess that's true I'm going by this one has one man one address on the back if it had multiple addresses we would need a join table instead of just a foreign foreign key I'm gonna go with the idea that items can only have one manufacturer but I think about this you're on a website and you're entering your address if you live in the United States there's a state drop-down that lists all possible States and it's good to have that because you want to do some validation right you wouldn't want every user to be able to enter in their own State and because you could result in different capitalizations some people not abbreviating it correctly so if you have a source of truth which is like a list of all possible States you can then show that in the drop-down and then just store the state ID and I see killer just just ask the question shouldn't the state would be related to the country probably and we could technically add a foreign key reference on the state table that says country ID I don't know we're gonna do that we could and in that case I might even put it into a single table like Country State I don't know we're gonna leave it and there we talked about it before you potentially could have had a separate table with a list of all possible cities at some point you have to decide whether or not you want to get that intense with your database model and I've decided that we're not going to so that's most of the model let's see item a specific item also has a location this is the location in my house that's why each instance can have its own location like this one could be in my pantry upstairs and this one could be in my pantry downstairs and then we also have shape information one thing was on the item table we actually have this weight column but there is a size table that has volume which I think can account for weight so I'm actually going to remove weights from the item table because it already exists technically on the size table it's a lot of tables and so what we're gonna do next is we're actually gonna write code that implements these tables in a sequel database and adds all of their relationships now there's a lot of chats that happened I'm probably not gonna be able to get to all of them or just gonna do a little bit of a scroll and I will acknowledge all the followers that happen thanks for the follow classic uncle thinks we'll follow for breaching oh thanks for the follow Lele thanks for the follow hack Ron thanks for the follow the PW thanks for the follow Jagan dust thanks for the follow rock man thanks for the follow heavy-metal and Pink's for the follow dysfunction just to sum fanuc looks like dysfunction cool let's see doo-doo-doo can you give us some insight are we going to use documentaries now so kubernetes is typically used for production systems that scale where you want to have multiple instances of docker containers that are communicating with each other or that are load-balanced we're just gonna use docker so that we don't have to install connects or Postgres directly on my machine so that's what we'll use it for yeah so uh setting up docker is gonna be part of this stream so you you will see it in hello Emraan what do I want to use for entity mapping we're using this tool to design it and then depending on what so this this tool is called lucid chart and there is a link in the description if you want to see this particular chart but depending on which database we choose we'll use that to actually implement the relationships let's see how the results are doing it's very very close so go here and vote now and yet so I will allow that message to happen but my overlay here does some sanitizing so that wouldn't actually work you can put your batteries yeah so absolutely so think about it like if you're if you're preparing for an apocalypse and you have an inventory and you want to keep track of all your things you could store what batteries do you have you can store how many flashlights do you have you could know when you need to buy new batteries different things like that yeah seek will take serious amounts of time to master which a lot of front-end devs struggle with don't read my name I'm too lazy to change it yeah you're right like this is serious so somebody commented on the last video as well that some people literally have entire careers where they do nothing but model and implement sequel databases but in the fairly recent trend I don't know the past three four years this idea of a full stack developer is someone that can do it all and I'm someone like that and I learned about sequel databases I learned how to implement them I can build api's to talk to those sequel databases I am by no means a sequel database expert I am a programmer centric sequel database person in that you can get really intense with sequel you can create highly optimized queries and do highly optimized joins and things like that whereas when I'm implementing things a lot of times I'll actually reach for application logic that would potentially do multiple versus just one query there's a lot of pros and cons to that but you're absolutely right like sequel is hard and it takes a long time to master yeah we're going to use a connects for sure and what type of cases would you choose softly instead of an actual drop really any system where you want to have a history of what has happened and so if we ever delete an item from the system that's potentially because I don't have it in my pantry anymore but let's say down the road I buy that item again and I want it to show back up I can essentially undelete it so there'd be like an unarmed an archived feature and that's really what we're doing here we're not deleting we're archiving so that in the future we can unarchive and it's there might be some up some applications where you definitely want that but it's really up to the implementer and and the person that wants this application like how do you want it to behave I would say most of the apps that I have in production actually do soft deletes so we only allow the users to archive and that's really because sequel databases are meant for a lot of data and so you don't really have to worry about removing rows unless you're dealing with massive massive data and that's a whole another problem but yeah are you able to build in auditing to your databases I don't know what you mean by that I'm using a piece of software called OBS I also have a video capture card if you look at coding gardens SlashGear not that you need a list of all the things that I use to stream and record my screen so you can check that out and it's tied right now between my sequel and Postgres so keep boating voting here I'll share the poll again okay let's keep talking yeah I'm using OBS till reasons I heard that Bush baked beans dog passed away I think that happened yeah oh nice Trey's working with sequel databases today honey says I've seen people using MongoDB as a relational database management system like modeling relations I think there's not was not monk what was made for is it a good way to use why should you not use it our DMS yeah the reason a lot of people choose to begin with is that it's very flexible in that you don't have to have these rigid relationships in your document stores like you do with sequel however that's how a lot of people end up using anyway so they would use a library if especially in JavaScript you use this library called Mongoose Jas which can actually implement schema validation and foreign key relationships but this is implemented at the application level this is implemented with a JavaScript code the actual database doesn't care how you insert or relate things but this library allows you to do that and potentially people like to do that because what I'm about to do which is implement the sequel database it takes a little bit of work whereas with Mongoose it's kind of model first and that you create your schemas and your models in JavaScript code and that's what you used to interact with the database you would never have to create a diagram like this and then implement it with sequel and then this is a lot more work honestly that's I think that's why the like is so popular in JavaScript stacks because it's less work doesn't mean it's better it's just less work yeah so game step is asking is connects better than sequel eyes what's the difference so let's let's talk about that right now so I mentioned connects is a schema builder and I'll show you an example connects lets you do things like this you can say connect start select from books and this javascript code gets turned into this sequel code so it is a query builder in that the code you write with JavaScript is very very close to the actual sequel code that gets generated whereas something like sequel eyes equalise is known as an ORM or an object relational mapper and this is much higher level so you in in sequel eyes you really never write anything that looks like a sequel query you call methods on objects and that's how you add things to the database or update things instead of saying like update where you'll just set a property on an object and say dot save so that's the major difference it's equalizes an object relational mapper let's see if we can see an example of just like a simple query modeling a table query okay user dot find all it's very human readable and very abstracted because really what's happening here is like select star from user and that that's that's the underlying sequel code that would get implemented user dot create and you pass in an object whereas with sequel that's like insert into table these values so sequel eyes is much higher level and connects as much lower level and what I like about connects is it allows us to do migrations so I'll talk about that in a second but basically we can have JavaScript code that describes how the database changes over time whereas with sequel you can do that but it's also very model heavy in that you're creating these models that represent the tables and if you need to change the database potentially you update the model and you can sync sync it with the database whereas connects is much more explicit where you literally say add this column or drop this column and what I like about that is I have much more fine-grained control over the sequel database and in the future if we want to change the types of these columns if we want to add new tables or new relationships we can do that with connects migrations there are a lot of other OEMs out there I'll mention that objection Jas is my favorite JavaScript ORM and it actually uses connects Jas under the hood and I really like this and when we implement the application like the API I will most likely use objection Jas but objection is cool because it doesn't care how the database was actually implemented it just needs to know how to communicate with that database so eventually I'll probably use objection but for now we're just going to use connects which is much lower level and is going to allow us to model the tables there's a problem pulls on twitch twitch chat only shows chats that were sent after some connected to the stream oh yeah I mean I think we were doing this straw poll thing though looks like Postgres is winning that's good hello mr. Boyson hello Aishwarya thanks for the follow Rahman do I need to watch the previous video first um you don't have to I mean I basically did an explanation of of what we created here but if you want better reasoning for why we did things we did this we did here you could watch the last video because I explained a lot of that um our states identified rather short codes or full names I'll probably go with short codes yeah why do we care about the manufacturers address in the first place what if I want to create a map of where all of my canned goods come from I could create I could use that information to store it also if this were actually like a store inventory system you would potentially want to keep track of your manufacturers so that you can place orders to those manufacturers also based on the location of the manufacturer you could determine like shipping time and stuff like that for a home system it's probably not that important but you could do some interesting things like how many of my products are from X manufacturer so you can really see like oh wow most of that food is made by one place I'm not sure I'm not sure what you mean by data dictionary and hello mayor Shawn welcome can I create a trigger that soft does deletes a soft deleted entry after 30 days yeah you absolutely could and that's what some systems do as well so initially you do a soft delete or an archive and then you have some sort of system process that runs on a regular interval that decides okay if anything has been deleted for 30 days we're actually going to delete it for real and remove it from the system but you still potentially have the issue where all of the related things will need to get deleted as well and you may not want that yeah ready for lockdown in South Africa yeah I bet that's scary being at home with with kids hope you're doing well and hello Destin and thanks for the follow remixer all right we're way behind on chat let's see I see killer says how do you add many too many or one-to-one relationships with lucidchart so right now all of the relationships I've added are many two or one too many I actually don't think I have any many to many relationships yeah we don't actually have any join table although related item is technically a many-to-many relationship but if you want to do a mini 2r1 let's say we want to do a one-to-one relationship you can click on this and then you can just change how it gets connected so I'm going to change the connection point to be that and this is now a one-to-one relationship but I'm actually going to keep this as one too many but yeah you can see all the different possible things you have in here and you can look up any D relationship diagram symbols to know what all these means this is like zero or mini this is one or zero or more one or more one to one yeah there's a lot of stuff yeah we don't know the database driver yet it's most likely gonna be pro stress I'll let a few more votes come in if you haven't voted yet but we're probably gonna use Postgres for this honestly the only the only difference at least right now is how I set up the docker container because modeling the database is going to be exactly the same regardless if we're using my sequel or Postgres the cool thing about the Connect site is you can choose your database and then when you look at the different query builder functions it'll show you the syntax for that particular database so with Postgres you make queries with double quotes whereas with my sequel you make queries with backticks and connects J's handles that under the hood depending on what database you're using my sequel please and hello duper sick yeah I'm safe I'm doing good um yeah Robin has a good point you need to use some API so you can scan the Barker barcodes autofill up everything absolutely that's the plan eventually we'll have a mobile app where you can just scan it it can do a lookup check to see if that manufacturer exists if it doesn't insert it into the table yeah all that good stuff but that's that's down the line what do I think is a non-trivial opie project Oh probably a I was gonna say like a bookstore that's really generic though I don't know I don't know oh yeah so we're restoring SKU which should be the barcode and thanks for the follow-up SX thanks for the follow windin we had a developer that made an app with Microsoft sequel using nothing but store procedures yeah and so like that's that's another thing about people that are that are dedicated sequel developers is a lot of times people have the opinion that you actually shouldn't let your applications run direct sequel code at all you should create stored procedures which are basically like sequel functions and you should only allow your applications to call those stored procedures I'm not gonna implement it that way we're actually gonna write JavaScript code that runs the sequel queries but yeah okay um my shoot says I love your work just started developing my skills your channel helped a lot that's good to hear welcome in which case you should choose soft awake it's true so with the gdpr people can request that you delete items and you would have to implement deletion if that is the case all right we're ten minutes behind on chat I'm sorry to say it but we're gonna we're gonna just we're gonna scroll past all these and ask your question again if I'm if I'm looking at the chat and let's acknowledge thanks for the follow remixer thanks for the follow bosch nexxt thanks for the follow the xqw thanks for the follow of elephant thanks for the follow min Dex's thanks for the follow Lucario thanks for the follower khaliv thanks for the follow it seems like Mario thanks for the follow crew Mookie I think's for the follow Serb all thanks for the follow a chubby elf and thanks to a follow amongst FPS much appreciated is there any study that shows the performance increment by using primary Keys 6 you stop it we're about to write some code what does the record database I recommend either my sequel or Postgres because or my sequel or Maria DB they're both open source you can get up and going locally let's see next J is front end soon I probably should and it's not it's actually not that hard to get set up alright I probably missed a lot of questions I'm sorry but we gotta go we got it not go we gotta get going okay it looks like Postgres won so here's we're gonna do we're gonna start up my daugher on my machine and we're gonna look up the Postgres docker machine oh no I need an update remind me later docker Postgres you doo-doo-doo-doo this is seems to be the official post grass docker image I probably want to I want to use a docker compose something like this using the Postgres image we can set the Postgres password what is admin or I've never heard of admin err oh this is like the PHP myadmin for for Postgres that's pretty cool maybe we'll set that up yeah why not okay let's do this so directly from the docker web page we can see that they're describing this yamo file here's what we're gonna do in vs code let's go ahead and create a folder for our call it back in and in that folder we're gonna create a docker compose dot yml so this is a file that you can use with docker to basically create several different containers that all communicate with each other so what we're doing here is we're spinning up a container or we're spending up a container from this Postgres image called DB and that's gonna spin up our Postgres container that's great and then there's this other container called admin ER which uses the admin or image which gives us basically a web page where we can look at the tables that have been created inside of the database and do different things like that so that's great one thing we need to do is we need to expose the port of the database to my system so that way I can connect to it directly and I just need to make sure that I don't have Postgres running on my machine don't know brew services stop Postgres because there are different ways you can do this you could have Postgres or my sequel to install directly on your development machine if you do that then anybody else that runs your code needs to make sure that they have it installed locally but if we set this up with a docker container when someone else pulls the code down they don't need to have Postgres installed locally they can just spin up these containers and they're ready to go and thanks for the follow this so CFO Dyess PHP myadmin has a much better UI than admin or does PHP my admin support Postgres though yeah potentially matters for certain things that we might have to do raw but it's yeah I mean technically we could have just used sequel light if if that's the case but yeah ad minor isn't advertising use the docker generator vs code has I've never used that before [Music] and punch all says why is it said that MongoDB is the best fit for nodejs I think just because it's easier than c-cold I think that's really OPH G admin ok we're gonna use what they describe in the doctor and the doctor Doc's here and so this also tells us what environment variables we have available to us so there's Postgres password and what we're gonna do is we're gonna actually pull this in from a dot E&V file because whenever we start to create our nodejs application and whenever we set up connects to do the migrations it can use these environment variables to connect to the database so we're gonna need the post grass password that's gonna be it and then I believe in a docker file you can do something like this hope that works cool what's the best thing about Abilene even if you're wrong you're only off by a bit that's good you know hello Greg welcome are there any character limits on the fields in the table you can add them so in in the diagram that we created we didn't set any limits directly anything that was text we just set them as text but when I implement them I'm most likely will and in Dayton sequel databases this is known as a bar chart or a variable character limit and typically you can set that this field cannot have anything longer than 50 characters and we'll do that when we're creating all of the models yeah how do I know what to extrapolate into a table I would say watch my last video where I talk about that but essentially each table represents an entity you could think of each table representing a tab in a spreadsheet anything that you would need to have multiple rows multiple instances of that is a table yeah how do I access local Postgres database from outside and for card operations so what I'm about to show is will a be accessing a Postgres database inside of a docker container but you can use this exact same method if you if you have it running locally can anyone confirm if I have the right syntax here for my Italian V Postgres user recall this admin Postgres DV will call this inventory app so what I'm doing is I have this dot env file and I have all of the actual values in here and then in my docker compose I'm setting these environment variables for this specific container so that it will look at my dot env oh it's doll I'm missing the dollar sign thank you cool thank you everyone okay so we have posters password Postgres user poker Postgres database [Music] and then yeah I think we're gonna want to set up a volume so here's the thing about using a docker container or a docker container for a database if your database is actually storing things and that docker container goes down you lose all of the data so we will want to set up a volume so that any data that's stored in the docker container will be stored in that volume and that way if we bring it down or bring it back up you all are sitting silly if we bring it down or bring it back up we'll always have access to that data so I'm just gonna create a folder called docker data and then inside of that create a folder called DB data and then inside of that create a file called get keep so that it actually keeps that directory and then we can set up a volume and we just want to say this should be docker data / DB data and so what we're saying here is that inside of this docker container var Lib PostgreSQL data this is where all the database data is actually stored we're gonna map that to this actual folder on my local machine so that should be good [Music] what else I think we're good to go see if this works does anyone know you get bonus points what is the default Postgres port if you can tell me before I can find it you get 10 coding garden points by port I do thank you so here's another thing we can do is we can say ports and we're gonna map port 54 32 on my machine to 54 32 inside of the container ok I think I think I think we've done everything correctly we may need to look up admin err maybe not everyone has it they Google that's fine I didn't do it nice yeah 5432 is the default port everyone's got it great work everyone great work and thanks for the follow Tony man 5 4 3 2 yeah you all get 10 points and thanks for the follow alia cabin ham okay admin ER is the web console it's like PHP myadmin and I was thinking we need to set these environment variables as well but I guess not because you'll probably just enter the username and password and so by default when I start this up it should look at the dot env and pull in this info so let's try it so here we go I'm gonna say dr. compose up is invalid I did mean volume so this should be volumes all right docker compose up it's gonna pull those images so it'll pull the PG image in the admin or image they don't work because of security issues so it would be very possible to cross-site script my my chat manager here if you were to able to add that stuff oh I know depend on your a good point win32 zero-day we can add that so in is it depends on but in amo file you can say admin or depends on DB so that way this container will not start up until this container has started up let's kill it so I'm gonna bring them down depends on should be an array cool so you can see that this this DB data folder you just got filled up with a bunch of stuff that's all of the internal files that Postgres uses to keep track of data and now it says that admin ER is listening on port 8080 so if I go to port 8080 in my browser we should see oh that overtook my chat manager though I should have I should have mapped it to a different port all right I'm gonna map it to a different port kill it we're gonna map it to port 1890 this is a very big button yes great work okay all right 80 90 there we go so we can say the system is Postgres the server is DB because this is inside of the internal docker network the username is admin password admin in database is inventory DB log in inventory DB does not exist okay did I call it the right thing inventory app let's see inventory app login no password supply okay cool it works that's amazing yeah admin or best UI ever you're right having your credentials as admin admin is likely putting a gate before an open field with no fence it's a good point but this is pulling it in from my environment variables so if I were to deploy this in production with kubernetes or something like that I obviously would have a much much more much much more secure username and password okay so that's awesome so the Postgres database is running now and we can start connecting to it and creating tables so I'm gonna leave that running technically you can run a docker compose in the background but I'm gonna leave it running so we can see the logs as things happen and then in a new tab we're gonna start doing all of our connect stuff so I'm gonna NPM in it this folder so that way we can install our nodejs dependencies and then I'm going to install connects I'll also need to install the database driver so you can see in getting started with connects let's see the client you specify which client connects should use and so in nodejs with connects well in no DJ asks which Postgres we use this module called PG it is the most popular one there are probably other ones but this is the one we want so I'm also just going to say NPM i PG crowns yes Corona is a crown and thanks for the follow avoid much appreciated that face when the username is more secure than the password yeah please use PG admin for I guess I could I kind of just want to I want to get going and the for whatever reason the official Postgres docker documentation pointed me to admin ur so I'm fine with that we're not really gonna use it for much we can use it to verify that the tables got created and we can look at their schema and stuff like that and thanks for the follow purses how often do i nuke in format my machine um maybe once a year a little longer than that that's not the clear command okay so we now have a package.json we have our dependencies connects and we have our dependencies PG so the first thing we'll do is we'll create a connects file which describes how we connect to the database and actually I'm also going to install a dot env because we want to be able to read in that dot env file to use it inside of nodejs hash tag switch back to firefox there's just there's several apps that I haven't logged into that I would need to log into again I don't know you found a new editor for me to use that sounds like a preface to a rickroll so I'm not gonna listen to that okay petition procedure switch back to firefox okay so we have da T and V connects and PG we will do npx connects in it I think there we go and that's gonna create this connects file which tells connects how to connect to our database so let's look at it I don't know it's better the stream oh thank you thank you linkie okay so that creates this file which describes how to connect to the database and one thing we'll do is up at the top here is we'll bring in our dot env file so that we can pull in the Postgres password Postgres user and Postgres DB so we can use that to connect to the day bass now the connects file describes different ways of connecting to the database and so you can have different environments so you can say in development we're using sequel Lite but in staging in production we're using Postgres or my sequel for now I am actually going to remove all of them except for development we're going to use this information here you just a bunch of extra stuff it's pretty much all we need so client I'll say is pg the database is going to be processed en VDOT Postgres DB the user is going to be processed env dot Postgres user and the password is going to be processed env dot post rests is it PW or is it password its password cool while I'm thinking about it I'm actually going to go ahead and install yes lint because look look at these crazy people they're lining up the property assignments I hate it I like to keep it like that but I'm gonna install yes went for that this is a dev dependency I just wanted to try this awesome good job lizard keeper random question what is the main purpose of a service account lockdown a user create a user give permissions to a group of users allow for service to first service interaction with GCP are you doing homework I would probably say number four so like if you have some sort of internal process that needs to do something it's good to have a service account so that the stuff is happening on behalf of that service instead of out happening on behalf of some user yeah what theme if you do exclamation mark theme you'll get a link to the theme that I'm using is this is a new one I haven't used this one on stream before I think lined up looks neater you're right but I don't know okay and then we're going to create our yes link file for me it's morning it's only 8:48 a.m. okay we're gonna set up our use lint file and we'll be good to go am I using format unsaved I don't know maybe hello code welcome Roberto says what about developing an API in another language and then building an application which essentially only does requests to the API that's absolutely possible yeah so I mean I like nodejs and that's what I build my AP api's with but technically you could build them with any back-end language as long as they provide like JSON any front-end application could communicate with them hello Diego welcome omegle oh great work I have heard of the Kari coding standards I have not okay so we havee eslint one thing I learned recently is I believe you can say root true and what that allows is the connects file to let's see is it working or is it root false you maybe I need to reload vs code you how is that Turkish I don't know yeah so yeah I mean you could do like any sort of uniform API whether it's JSON or XML or any of that good stuff that's pretty cool and thanks for the follow Bassam I haven't worked with Python much I worked with Python like five years ago but it was mainly for like data analysis I really didn't build any api's with it my es lint is in Arastoo I see that I wonder why yeah Phil deload plugin import I think that's because there was this root false maybe [Music] does anyone know what I'm talking about like I learned this the other day like if you have a nested folder that has an es lint file inside of it you can put this root property on there and then it makes it so that it doesn't have to be in the top folder all right I'm gonna google it oh I was using a razor blade for my streaming computer so I actually have a multi computer set up I use them a MacBook this is still a MacBook I've always used a MacBook as my coating computer but the HDMI goes to a capture card that's plugged into a Windows machine but yeah I used to use this razor blade because I my stream setup had to be portable but now I just have a dedicated gaming desktop computer that handles everything does anyone know what I thought I'm talking about all right um and hello Luka welcome thanks for the follow race on that huge NPM is joining you yeah okay wrong working directory I really I did this the other day I'm really feeling like it's just the word root you thanks for the yeah thanks for the follow every song on Diego thanks for the follow and afro main yeah you're finally welcome welcome everyone I shouldn't be spending time on this don't use the eslint I mean the other solution check it is all I have to do is open vs code inside of that back-end folder and it will just work get rid of that for now it should work yeah see it works I don't know we'll fix that some other time good morning Brooks what's up okay so we have our connexx file we've told it how to connect to the database and now we can start creating migrations and such so one thing that I like to do is I like to create a folder now let's call it DB and this is where I store all of my migrations and all of my seeds because the thing to think about is eventually this folder is gonna have like my back-end code for my API and Express and all that good stuff and so I want to make sure that the the top level isn't too crowded so in this DB folder I'm going to create a folder called migrations and migrations are the things that will actually change the structure of the database can I explain indexing in a database when to use it and when not to use it if you want to search on a specific column that column should be indexed otherwise your queries are gonna be very very slow that's the mate that's the main reason if you're doing lookups by a column very often you should probably index that column okay so we have this folder called migrations and now we can create our very first migration and what I'm going to do is I'm going to create a single migration I'm gonna call it the initial migration that describes this entire structure like technically you could do separate migration files but I like to do it all in one because we can create the tables in order of their dependencies because you'll you'll notice that certain tables will need to be created first because they're dependent other tables are dependent on them so you can see that the item table is dependent on the user table and the item type table and the manufacturer table so I actually can't create this table until those other tables exist so in this one big Micra and file I'm going to create the tables in the order that they need to be created and that will be our initial migration essentially so here's what we do in the folder here I can do npx connects migrate mate and then you give it a name and I'm gonna call this initial oh and you'll notice I forgot to set up one thing so you'll see that it actually created the migrations folder at the root I don't want that I'm gonna delete that and in our connects file I believe we can set where the migrations are folder maybe I don't know I'm gonna have to go look at the docs to make sure I'm doing this right but basically I wanted to put the migrations in that folder not not at the root so let's just search for migration folder is it directory oh here we go I think okay we are looking for configuration options migrations table name read the migrations section migrations directory so we can specify it from the command line but I want to do it in the configuration file custom migration source sort of what's happening I can use directory alright let's just try it um directory okay I'm it's weird that I can't actually find that um I I do work from home today I'll be streaming for roughly one more hour so hopefully we can get this database implemented in about an hour alright let's try that again and there we go it put it in the right folder so thank you thank you yeah where's it at Andrew can you tell me in the docs is it just the migrations API you query builder schema builder migration API here we go directory a relative path to directory containing with migration files yeah I basically will never ever click a link that Andrew provides but I appreciate it Andrew that's it cool we figured it out so what that did was it created this file in the migrations folder and with migrations there there's this concept of up in a concept of down so V up is what should happen when we're actually creating this database and that is we're basically going to create all of those tables and create the relationships between those tables the down in this scenario is to just drop all of the tables cool so what I'm gonna do right now is I'm gonna change these to be an async function because the query building methods to create tables and and add columns and things like that those are asynchronous they return a promise so we're gonna do a bunch of async await stuff inside of here uh one of his functions yeah it's good and bad at the same time there's a lot of good stuff in there but some stuff is hard to find hello John hello Shannon welcome is galvanize the last time I use connects no I use a connect set my at my job I use it in production see you later Destin thanks for hanging out and thanks for the follow Crusader thanks for the follow anyone else that followed okay so this is our migration file one thing that I like to do is create a file that contains the names of all of my tables so let's create a source directory and inside of that I'm gonna have a folder called constants and inside of that I'm gonna have a file that's just called table names now you absolutely don't have to do this like technically when I'm creating a table I can just pass in a string but if we're reusing table names I like those to be in one place so that I don't have any typos and so we're gonna do that here so we'll say module dot exports is an object and we're gonna put the names of our tables in here so let's go to our diagram and now what we have to do is we have to decide which tables do we create first I am going to create the user table first because it doesn't have any foreign key references so you'll notice there's a primary key and then just the properties now because there are no foreign key references this table is not dependent on any other table meaning I can create it first so we're gonna create this one first and so on my table names I'm just gonna have user as user just like that and what I can do now is in this migration file is I'm going to bring in the table name constants so that I can use them so we'll say table names equals require go up a directory go up a directory go into the source directory go into the constants and then grab the table names file cool so now that I have table names vs code should actually do some autocomplete so if I do table names dot I can see that I have access to users so this is the main reason that I create these constants is I won't have typos with the names of the tables and so now we can actually create the user table so here's what I'm gonna do we say oh wait connects dot create table you pass in the table name so I'll say table names dot user and then you get a callback function that allows you to specify all of the columns on the table so this this does return a promise because it's gonna need to actually run the sequel code that creates the table we pass in the table name now again I could have just passed in user like that but like I said I like to prevent typos so I create a lookup object and now inside of here we can actually describe the table and the way that works is you just say table dot and then you can specify the column type so I'm gonna say table dot increments this is built-in to connects to automatically create a column that is an ID that is the primary key and that that is indexed sorry for spam tory's so if we do table increments it's automatically gonna add that ID column let's just look up increment it's here and right now we're working with schema building so in sequel there's two different concepts there's this idea of a data definition language so this is the sequel code that actually creates the tables and creates the relationships and then there's the other abbreviation or acronym which I forgot which is a sequel code that selects from the tables and inserts into the tables does anyone know the other abbreviation DML data modification language sequel DML data manipulation language that that has the nice ring to it cool yeah so there's data definition language which is what we're doing right now and then there's data manipulation language which we'll do later to insert things into the table and things like that Thank You buggy man for that and thanks for the follow Elmo open door good call yeah so what Andrew is showing us is actually a pretty sweet is once we do this will actually get Taipings so how can I select it all I can't select it all can I select it all but once we get Taipings when we do table dot it'll tell us all the things that's available to us so let's do this well we need to get rid of and we need to get rid of these I could have just typed it out I really could have just typed it out you well that give us the Taipings how do we get the Taipings it's a rickroll is it no oh hello op d welcome exports set up this this is a connects thing so the idea is that this function is going to be the one that runs whenever we run the migration and this is the function that runs if we ever try to roll back a migration and thanks for the follow fettle yeah hello chica put it before exports that up [Music] all right you work on that syntax we're just gonna keep doing things cool and so this will like I mentioned this creates an ID table now what I like to add to it is the fact that this column cannot be null so you can say not nullable and this should it's showing and so that makes it so that this column can never be null and we want that because it's a primary key I'm trying to think is there anything else we want on there I think I think that's mainly it and so by default this creates a column called ID if you wanted to call it anything else like user ID which you probably shouldn't because this is the user table you could pass it in there but by default that creates the ID column so that one's good to go next up we want the email so we can say a table dot text email and this is not nullable this is also unique because you can't have two users with the same email and instead of table dot text will probably also we'll probably set the maximum length what do you all think what should be the maximum length of a of an email in our system 255 characters is that too much coding so what I'm looking up right now is the table commands because there is table dot text but you could also do table dot string I believe yeah so this is does a bar chart 255 20 characters 603 characters 120 characters are there any limits 128 let's see email character limit 384 characters what's the maximum length of a valid email address must not exceed 254 characters there we go so what we can do is we can say table ringg email 254 so and technically if you just do string by itself I believe that defaults to varchar' yeah defaults to varchar' 255 which is just one away from 254 so technically I could do that and the max length would be 255 but I'm gonna specify it so that's great what else do we need we need the user's name I think similarly we're just going to use the default 255 characters the name is not nullable but the name does not have to be unique so two users can have the same name that's totally fine alright let's try it I think I potentially need the type for table I actually am I'm interested in finding the right type options table options his table options exported looks like that type isn't exported I don't know weird okay regardless let's just let's just build the tables we don't have much time okay so we have the name we also need the password what should be the maximum possible password length so one one interesting thing about Postgres is it actually does have the text type which doesn't have a limit and so it could be any any length I don't want to restrict I mean people that have want to have like a hundred character length password like why should I prevent that from happening so I don't know let's go 500 characters realistically yeah there is no hashing your eyes it takes it definitely takes up more memory like if you use the text type which doesn't have a limit it's absolutely gonna take up more memory yeah uncrackable password so this is the maximum possible length I'm fine with that like 500 characters is cool you would use a password manager I don't know I mean it's probably realistic to do like a hundred but again I don't want to prevent people from creating very secure passwords and then what else do we want we want a last login which is just a date/time so we'll say table dot date/time last login and this is also well this actually can be nullable because if you create a user and they've never logged in before this could be null Windows has a maximum password length of 127 characters storing passwords as plain text no so the actual storing of the passwords is in an application implementation so with no js' we will use bcrypt before we insert those passwords into the database there are native modules for these different database systems that could technically encrypt the passwords but I do it at the application level not at the database level okay so I'm gonna add what's the windows limit 127 we're gonna do it as 127 when do we roll back so we would roll back if we if we want to undo some of the migrations that would happen so here's what I'm gonna do I'm gonna this is the only table I'm gonna create right now and I'll show you how this works and and see if it actually does work so what we'll do is in the down we want to do the opposite of creating this table so 10 coding garden points in the chat if you can tell me what's the opposite of creating a table and thank you for the the follow abdullah abdullah abdullah oh yeah Oh as he connects that schema that create table drop a table yes great work everyone so in the down we will drop this table that we just created now I had that wrong this should be connect scheme about create table now that I've done that oh wow look at the autocomplete so we can see all of the possible column types that we can create that's great thank you Andrew thanks for figuring that out yeah so in V down we want to drop this user table so I'm gonna say oh wait Connect Scott schema dot drop table table names Scott user cool and so if I migrate my database that's going to create this table and then if for whatever reason I want to roll that back this will run which will actually drop the table we're using Dockers so I didn't have to use my local instance of push press docker is running inside of the container okay so let's let's try to run this let's see what happens so we're gonna do NP X connects migrated colon latest and what this does is this will look in that migrations folder and any migrations that have not yet been run will run against the database and so I'm actually gonna add this as an NPM script I'm gonna call this migrate so I can just do NPM run migrate and that's gonna try to do connects migrate latest it says batch one run one migrations and so now if we go back to this admin thingy and we refresh look at that we have some tables this is good oh yeah oh you're right you're totally right we want to add timestamps too but this is a good good way to talk about rolling back because we're gonna roll back so that way we can add the migrations technically we could have added a new migration but I prefer to create my initial database structure with everything we need and then once my app is in production then we start to add new migrations it was all plans yeah but so now we're using this admin earthing and we can see that multiple tables got created so for one there's this connects migrations table and this is actually what connects uses to keep track of which migrations have run so if we select all the data you can see that there's one entry and it actually has the name of the migration file that ran and so next time if I were to try to run migrate again it would say already up-to-date because it looks in that folder and sees that this this migration has already been run great and then we can see there's the migration log it uses that internally but then we see the user table so if we click this we can see we'll got created so ID is an integer in Auto increments email is character varying and there's an index on a primary index on ID and there's a unique index on email I'm curious why this isn't showing us like not nullable oh you're right yeah so in the script I don't need objecting get rid of that and if we run it again it should say already up-to-date great and thanks for the follow-up bottlenecked who asked that question yeah opti this is uh this is a JavaScript nodejs so the library we're using is called connects and this is the library that allows us to create the tables and add new columns and things like that and then we're using Postgres which is just running inside of a docker container okay but the table got created that's great but what if I want to roll that back what if I accidentally created the table in the wrong way I can roll back my migrations so I'm gonna add another script here called rollback and we'll say connects migrate rollback and what rollback will do is it will roll back the last migration so if we look back at that migrations table right now there's only one migration that has run there's only the initial migration so when I do rollback it's going to rollback this initial migration eventually like in the future when I have a ton of migrations rollback is only going to roll back the last one that ran but for now that is just technically one and thanks for the follow draw anchor shows null not nillable did we see that oh I see yeah so last login says that this column can be null and because email and name and password don't show no that means that they cannot be null thank you for that not that it matters but your up function doesn't need to be icing it it will in a second because I'm gonna be creating multiple tables in here and I need to wait for one of them to be created before I create the next so right now it doesn't make sense but once I have more in there it will and so now I'm gonna roll back we're gonna do npm run rollback and that's gonna run the down which should have dropped at the table that's gonna run this and if we look back over here and we look at our table we'll get a refresh the user's table is gone it's not there anymore because we dropped it and if we look in migrations there are no longer any migrations listed because we rolled them back so that's great now we'll add the rest of the stuff thanks for the follow fade in old and Zizi art is asking how do you connect a post press in note we're using the PG module so PG is a module we're connecting directly to Postgres technically you could use PG and just issue it's equal commands directly connects is a wrapper on top of that that gives us these nice JavaScript functions for creating tables and query in the database so we're using connects for that is a good idea to example for example to docker eyes and Express REST API and your react app as well and have them talk to each other you can definitely docker eyes the REST API I don't really like to docker eyes front-end applications mainly because they have to build constantly and if that's running in a docker container it's just a whole lot slower but docker always hopes when you're working with multiple developers because you don't have to have your local environment set up and in a very specific way you can just use the docker file and you're ready to go but yeah I typically have my API in a docker container please don't go to theme Fredman er is that a thing what the heck is he doing can I can you go to ten fast fingers I don't know what that means hello Deena true the lock table is used internally by connects when the migrations are running I believe you can look at like I've never had to touch that table but I believe it's to tell it that this migration is currently running so don't run any other migration something like that what are the advantages creating tables with JavaScript for one it's all in one code base if you have someone that's not totally familiar with sequel they could look at this code and somewhat deduce what it's doing I like the fact that my code base is in one language and I can use my code formatting tools and lint linters and checkers for all of my code versus having that potentially have separate checking tools for like sequel code and there's just a lot of cool nice functions built into connects that you wouldn't get if you were just writing raw sequel and because it is JavaScript you can create a lot of reusable functions what I'm about to show actually is let's do this we need every single table is gonna have a creative that updated that and deleted that column so we could create a function that automatically adds those columns to any table that we pass into it and that's one of the benefits of using javascript so we'll do that I don't know anything about in flux DB and hello show starsin welcome how do we insert a record when we have primary key and foreign keys I'll show you that not today in probably in the next episode we'll actually see the database with some initial data and I can talk about how do you insert related data but yeah we'll do that anything any ideas of what I can code maybe something related to coronavirus that's always pretty popular I don't know thank you yes sir and thanks for the follow Ural doctor or get both yeah both use okay so I'm gonna create this function that says add default columns this takes in the table and we are going to say table date time created at not nullable and one thing that's cool that you can well that's not cool it ultimately results in sequel is we can say default and I believe it's just connects dot now what will look this up but basically we're saying create this column called created at and by default if no data is inserted it is going to set the date to be right now and so we want a created that column we want an updated that column which will also default to right now and we also want a deleted at home which is nullable and does not have a default so by default deleted at is null and if we ever set the deleted at value then it doesn't show up in our results let's look at the connect stocks to find that default to is there connect Scott now now I think I saw it connects F n dot now there we go [Music] this is what we want ultimately this gets turned into sequel code which will set the date to be the the date when it was inserted yeah daemon ZTV is saying we do have timestamp and time stamps I believe a timestamp is is just a time without a date let's see though oh cool cool yeah so timestamps will automatically add created at and updated at right so adds created that and updated at columns on the database setting each to date time types when true has passed as the first argument a timestamp type is used instead both columns default to being not null and using the current timestamp when true is passed as the second argument cool I think I want a date time rather than a timestamp though right don't die yeah so with connects user input is sanitized by default you don't have to pass in like a separate array to do the parameterization like you do it would we need to do with like the raw sequel driver yeah and I what we always use soft deleting at my work yeah thanks for the follow 32:4 be - I don't know what y'all think should I do daytimers what I do timestamp you I know to do time stamps because that basically replaces these two lines of code here I can just say table dot timestamps and let's see what else it said so when true is passed as the first argument we want true past as the second argument so use the date/time type and alway in default too now all right that should be it but now this function I can call it right here like this and that will automatically add those two columns to the table and I can reuse this function in the other tables that I create as well it's not a timestamp it's actually like a literal date/time type let's try running this and see what we get we can see the type described so we're gonna do npm run migrate and if we look at it we should see the type current timestamp current timestamp timestamp T's timestamp time zone i think is what that stands for this is a Postgres thing I don't think I want a timezone though I want it to be you TC you yeah so salt with the GDP our little rules you can do soft deletes but if someone requests that their data is removed you will actually have to remove it for sure cool alright nobody has in the opinions we're gonna use timestamps it's using timestamp Z which seems weird to me but that's okay we're actually gonna roll back because there's more tables that we need to add okay let's keep going so we have our user table what's next so if we look at our diagram we can create any other tables potentially that don't have foreign key references so things like item type we could create that table we could create the state table or the country table I think I'm gonna go ahead and create the item type table this is gonna be an easy one so it's going very similar so we'll say a table names dot item type which doesn't exist yet we need to add it so in my constants over here for an add item type like that and it has an ID and then it just has a name and we'll default that to varchar' 255 it's not nullable and it actually should be unique because item type will be things that we don't want replicated right we want canned good microelectronics different things like that and we wouldn't want to have two entries in this table that both say canned goods so I think it's okay to say that the name on this on this table is unique so that's good and thanks for the follow at van reloaded yeah Dana true says my opinion is you should use whatever you think is best well thank you so do you think Facebook and other apps really delete your data when you request them in certain countries by law they have to but you never know text is better than varchar' in most cases people were mentioning earlier that text will with Postgres potentially take up more memory and I'm okay in in for these columns I do technically want to limit how much I can store cuz it doesn't have to be infinite especially like name it should really it's not gonna be huge canned goods you know water bottle I don't know and thanks for the follow Marvel ooh okay so we have item table I'm just going to color code these as we finish them let's go with blue on that one and we'll make the lines blue as well and then we just created item type and we'll make it like a pinkish color and it's line will be a pinkish color cool let's go ahead and create any other table that doesn't have a foreign key reference let's do state and country text is for long content and varchar' is for short content by default by default it's 255 characters and thanks for the follow gnarly I still don't understand the difference between varchar' index its how it stores it internally because with the text type there is no limit you can literally store things of infinite not infinite but a lot they can be very very long where as varchar' by default will limit like the it's enforced by the database if you try to insert an email that has 255 characters because it's longer than the 254 character limit the database will throw an error and say you can't do this if you use the text type specifically with Postgres then there is no limit on the length my sequel has like short text medium text and long text which is similar yeah that's the thing how would I implement sequel triggers I've never done it before way I can look into it yep hello phone hello cherry we're not using type scripts now listening to your twitch I feel like I'm at university again well it's always fun to learn right yeah so with text you potentially can set a limit regardless let's keep moving so we have state and country you know what here's another cool thing about using javascript this this function right here creates a table that has an ID and a unique name I want to do that same thing for both state and country so I'm gonna create a reusable function so let's say function create name table so this is a table that literally only has an ID and a name and we want to do that right here or like with this and we'll pass in the table name cool table name is not in camelcase is probably what its gonna say I'm gonna turn off camelcase I for some reason I really don't I don't think I have a reason but in my databases I like to use underscore case instead of camel case I just do yeah okay so now that I have this nice reusable function we're gonna call it multiple times we're gonna await it and we'll say create named table with table names dot item type and then we want to do the same thing with country and we want to do the same thing with state cool so just like that we have created three tables that all work in a similar way yeah we could shorten this I'm not gonna create Const functions here but yeah technically we could make it an arrow function and do like an instant return that's okay with me thanks for the follow cts cts couch goals yo gnarly thanks for following what's up and a varchar' is actually an int plus a character array thanks for the follow helpin L open l luma y yeah and so that's what we're doing here we're setting the maximum length of these things to save on memory and also thanks for the follow a for you five in six yeah if you do exclamation mark theme you can see what theme I'm using I like it it's called just black it's it's very vibrant and differentiating which I like about it and thanks for the follow code show thanks for the follow monkey business you all are great - thanks for being here theme counter plus plus that's oh that's a good point Brooke says someday not all databases respect casing so if you were to use camel casing so if you snake case you don't have to worry about that I like that that's why I do it I just do it because I prefer it but that makes sense good morning Noel welcome and thanks for the follower dr. habits the size of the string and Bart's butt so uh specifically with this connects library it is so if you look at the what was I using string the length here is the maximum length of the string behind the scenes it might do something different to change the number of strings and byte string and the number of bytes in the string or whatever but at least for this function that I'm calling it's setting the maximum length of the string I wants to change primary columns to tiny and medium int and the size of my DV dropped three times cool I mean if you don't if you know that you're not going to need that many rows you can definitely do that there's a link in the notes even on twitch there's a link it says show notes that's the live database model that I'm working on snake case is greater than camel case okay can you run this for our entertainment I won't count it as a rickroll let's just look at the source code because it is an SH file let's see I've only got about 25 minutes left I think we can do it that like we've we've done the bulk of the work right we set up the docker container we set up the connection file we're starting to create these reusable functions we'll have the rest of the database implemented pretty soon rick astley in your terminal yes you just curl it you I don't want to do it I don't I don't want to do it and I will push all this code up to github when we're done and you can see my my docker file and how I set all that up it plays the audio in the terminal okay about that we need the actual URL I'm probably about to get hacked but here we go let's turn up the sound internal speakers you've convinced me doc created something super cool I don't think it was I don't know if it was this but he literally created a thing that could run in your terminal that actually rendered the real-time twitch video view using Unicode colored characters it was insane so we curl this file and pipe it into bash let's see what we get Wow [Applause] [Music] Oh let's see audio GSM and audio overall yeah it's probably detecting my operating system and then actually like literally playing the raw sound yeah on Mac OS it yeah cool hacks and Yan Brooks I am using a VPN so okay so we have item type we have state that one's done now we'll make that a light purple we have this one make that a light green was there anything else that was as simple as just a named table shape technically we could do shape too let's make this green and just like that you can do this with table names dot shape I need to make sure that shape exists on my table names constants cool so I think this is probably the the best reason to use something like connects versus just sequel because with sequel you'll have a lot of repetitive code right if you have similar looking tables if you have columns that exist on every single table you're gonna have a lot of repetitive code it's just the nature of sequel whereas with JavaScript we can create functions and do stuff all day okay so we've created all those tables now let's go to like one level deeper so I believe every other table that we need to create has a foreign key reference so except for location let's go ahead and create location we will do that right here so and one thing I'm thinking about is technically because all of these tables don't depend on each other we could create them simultaneously with a promise that all let's just do that because that'll make the migration run faster so I'll say promise all pass in an array we can do this so we could do that and then we could pass all of these in there as well and it will technically create all of these tables simultaneously which is totally fine because they don't actually depend on each other let's do this oh man what does happen promised all oh thank you I'll fix that there's definitely a typo prom missed at all so technically all of these tables are going to create it be created simultaneously and then one other table that can be created at the same time is the location table so we'll do that right here let's create another location so table name is that location what columns do we need so it needs a name which should probably be not nullable and unique very similar to this we need a description the description can be null and doesn't have to be unique and I'm actually gonna limit this to 1000 characters because maybe we want to write a paragraph maybe we want to store it as markdown or something like that so this will be just a little bit longer than like a then 255 so we have our description and then image URL now there is a maximum length of a URL URL maximum possible link what's the maximum length of a URL to fact a limit of 2,000 characters if you keep URLs under 2000 they'll work virtually any combination of client and server software there's no standards around it search engines like you regardless okay so we're actually gonna say the image URL is up to 2,000 characters so that creates the location table cool yeah I think that's good to go I there we need to create the dependent tables as well so at this point we've created all tables that do not have any foreign key references but we will then need to create all of the tables that depend on them and that gets a little more interesting so stop it maybe you all did it at the same time cool but one thing I need to change about the the down function is it needs to drop all of these tables not just the user table however am I currently rolled back so with connects you could run into a scenario where you want to roll back but the table situation is not the same as when you initially ran the migration fortunately right now it's not so right now I actually don't have a user table so I'm not going to run into an issue but what I do need to do is drop all of the tables so let's do this wait promise all we'll have a list of all the tables which is table names dot user table names dot item type country state shape and location is that all of them I have one two three four five six tables one two three four five six tables okay and so for each of those we're gonna map that table name to connect that schema drop table with that table cool all right let's try it and we're gonna do connects no sorry npm run migrate we have an error cannot read property create table of undefined on line eleven what did I mess up oh I didn't pass in connects yeah so this actually needs connects is the first pram the screen went black for a second there we go so the migration completed and if we refresh the database look at all the tables we've got and if we look at like item type it just has a name there's a unique constraint on the name column it's looking good we have our created ad updated a deleted out columns yeah this looks great so now let's create the tables that require some foreign key references let's try to do things simply first yeah so let's create the address table because it has two foreign key references but that's okay we'll set that up so we want the address table can I just like copy the text no if I can so at this point we need to wait for all of these tables to be created because we're about to create tables that are dependent on those previous tables so we have this promise dot all which will wait for these to create and then we'll create our next tables how can I just copy the text yes I can't I don't know anyone can send images when calling the create name table function aren't you yeah yeah I was that's the error that we got thank you versus thanks for the follow shines love right-click control click I don't know I think copying actually copies the image is okay oh you know what here's an idea one thing I didn't show last time is the booster chart has this export feature and so if you click this you actually get a sequel version of your tables right now some of the types that I have are like totally wrong but I can definitely copy and paste this and we'll use it as a reference yeah so that's our reference for the address table so I'm going to say oh wait Connect schema dot create table and we'll pass it in the table name so I want a table names dot address I'll need to create that or to find that access to the table and then on table names we will add address like that and we can do our thing so we'll say table increments I will say not nullable so that gives us our ID column that's great we'll need to add the street address is 255 characters good for a street address I don't know but the street address should be not knowable because we at least require the first line of an address of an address but street address 2 can be null because you could have someone that doesn't have a second line to their address at least with the US based address and thanks for the follow of floppy keyboard the DB design tool is called lucid chart there's actually a link to the right below the livestream if you wanna click it what's happy phone have to say I'm trying to embed audio that will not work okay yeah any any any ideas what is the maximum length of a street address 255 is probably a good limit what's the character limit for usps labels address line has a limit of 46 characters Wow let's go with we're gonna go with fifty characters okay so we have the addresses we have the city that's the wrong thing so the column name is city we'll have to figure out again what's the maximum possible length of a city name when will make this not nillable because it must have a city though it looks like for us PS the city maximum character count is 50 yeah longest street address in the US v waterfront trill Toronto Ontario m5j - h1 Canada and so the thing is the address line one would just be five waterfront trail longest town name in the world okay well defaulted to 50/50 characters not nullable we then need to set up the state ID and the country ID we'll do that in a second because that requires a foreign key reference and I'll show you how to set that up and then we need a zip code and that's five characters potentially with a dash followed by up to five more characters let's call it like a maximum of let's just call it a maximum of 15 to be safe yeah we're doing North America for sure and thanks for the follow her mesh well well the longest street in the u.s. is in Canada you're right I was thinking like North America maybe that's what they meant yeah North America alright we have our zip code did you did you find my IP address let's see what you got um computer hope what are you talking about happy phone calm down I'm using a VPN and all of my overlays use image proxies okay and then we need a latitude and longitude oops zip code latitude now latitude is probably going to be a float I'll have to look at the connect straw docks to make sure that float exists but I think that's what we want latitude longitude someone mentioned last time that certain databases actually have like a point type built in which can handle like an XY or a latitude longitude I'm okay with storing them separately though let's just look up in the connect stocks float adds a float column with optional precision defaults to eight that sounds good for a latitude longitude up to eight decimal places okay so this is our sorry this is our address table now we need to relate this to both the state table and the country table so here's how we do that we'll say table dot references we'll put the will first relate it to like state reference I don't know I have to look it up but you can spent you'll specify on the table that we are referenced and relating to I guess you can also do a table dot for in let's look at that you and we want to do these other things as well so I'm gonna use their example that they have here so for one thing a an integer ID in a sequel database is always a positive number right it's you can never have a negative ID so part of adding this foreign key reference is saying that it is unsigned so I'll say table dot integer state ID is an unsigned integer meaning and it can only be positive and then we'll say it references state ID so I have a column I'm sorry I have a table called state and it's ID column will be the foreign key reference for this state ID yeah so table integer unsigned references I mean you can also do in table yeah in table state like this you could there are multiple ways of doing it you can say table integer unsigned references ID in the table state yeah connects is definitely a back-end framework for communicating with sequel databases but yeah my IP address is absolutely localhost or 127.0.0.1 okay so that should set up the foreign key relationship to the other table and if we look at our diagram that is this line right here so that we've just written the code that creates that connection from the address table to the state table and we want to do the exact same thing with the country ID so what I'm gonna do is create another little helper function that creates this reference reference column so say references will take in the table what else we're gonna need we're gonna take in the column and then we'll take in the foreign table and it will default to ID in the other column so we'll say table column four in table one thing we could do actually is automatically generate that foreign column art started the column name based on the table name [Music] so here we could say this is a string with table underscore ID so it's always going to default to the table name ID why is this complaining argument name oh this should be a table name that should be foreign table name like that cool so wait there's a plug oh thank you okay we're plugged in I have to go pretty soon anyways nine percent battery left oh you silly people um right now I'm but using choosing between two JavaScript frameworks reactant view why choose why not choose you don't choose view if react is more popular in terms of the job market in your area don't choose view if you want to write a lot more code in react don't choose view if you want to have to know more about JavaScript and learn more about how JavaScript works because that's what you're gonna deal with in react that's my reasons you shouldn't use view and thanks for the follow clunky yeah the charger isn't in my inventory system yet and I believe oh it's just don't know it Oh No Oh No stop it everything's gone horribly wrong oh man come back come back HDMI please let's try unplugging it and plugging it back in there we go okay I used duct tape to hold our in place should be charging now it's charging it's lit up and thanks for the follow jr. Rogers thanks for the follow for backbreaker do not don't choose view if you don't want good coach and thanks for the follower Thomas parsley why should I choose view if you want less code if you put if you don't want to have to deal with immutable data if you want something that's potentially more consistent when you when you switch between view projects yeah yeah we got a lot of followers the stream thanks everyone much much appreciation appreciated it's forbidden to multi stream if you are in a twitch affiliate I am NOT a twitch affiliate so I can do it okay so we created this nice useful reusable function and now I can say I can replace this and say references will pass on the table will pass in state oh that's all I need actually we're just gonna put in the the table name yeah I'm gonna become a twitch affiliate soon so if you're watching on YouTube you should probably go to twitch instead you get and get used to it twitch TV slash coding garden because pretty soon I am NOT gonna be streaming on YouTube I'm still gonna upload videos to YouTube but I will not be streaming to YouTube the theme is called just black eye I'm running low on time a bar cot so I can't really answer that question but if you look at the beginning of the stream there's a whole section where I talk about why you would use connects versus something like sequel eyes okay so now that we have that we can say state and then also country and so that should add a state ID in a country ID yep and we're gonna add our drop table here to table names address because we would want to drop that table before we drop the table that it references joy does on twitch welcome everyone thanks for the follow Casey's okay here we go I kind of have to go but I think I think we're at a point where we have written enough helper functions that it'll be pretty easy to create these other tables right right right at this point let's go ahead and create the manufacturer table because it just depends on address so we'll do that I'm gonna copy this I realize fun fun function is live as well but you don't have to tell people because then though they'll leave my stream why would I want that to happen we'll probably just go read fun fun function after this anyway it's depending on who's live I don't know whose life table names dot manufacturer well I bet here and get rid of all this stuff let's see what we got it is cool so we have our ID column the manufacturer name I'm not gonna say that on this column the manufacturer name is unique maybe you have two manufacturers with the same name but I am gonna make it not knowable because we will require a name logo URL is an actual it's very similar to image URL I think I'm gonna create another little helper that creates an image column column name you do something like this and then we can reuse this and all of our all of our URLs that we're storing in our system will always be a maximum length of 2,000 so now I can use this image function say image width table and image URL like that and then similarly I can add the logo URL right here we also want description we're gonna default we're going to give it a maximum length of a thousand and this can be null because you could have the description as blank how do references work so the references are creating a foreign key relationship to another table so basically it's just the sequel code that adds a foreign key constraint to the given table so when I say references state this is actually saying references table state ID references table state for the column ID in references is just a helper function I created that actually does the connects code to create that column so there's that we actually have the manufacturer type as text I think I would want that to actually be a be a foreign table with like a lookup of the different types because you could have like a food manufacturer or an electronics manufacturer um I'm just gonna make this read for you we'll come back to it and fix that we also want website URL and so actually I should I think I should rename this function to be URL because it applies to image URLs and website URLs yeah so website URL ugh is there any specific reason why I would use table names that manufacture instead of declaring just the name of the table as manufacturer I talked about this earlier but the main reason is to prevent typos so technically and I mean if you look at the connection documentation you literally can just pass in a string right here right I can actually just say like that manufacturer that would work just fine but I'm referencing that table name in multiple places for one I reference that table name whenever I create the table I reference that table name whenever I'm dropping tables I later on will reference that name whenever I'm selecting data from the table using connects and the other cool thing is by putting it in that object I get autocomplete so I can just say table names dot manufacturer and then I don't have to think about the spelling or potentially have a typo in the table name or anything like that that's that's the main reason all right we have website URL what's the maximum possible length of an email yeah very similar to types and redux you technically could have all cap strings all over the place but you create them as reusable like kant's so that way you don't have to retype them everywhere in your code same reason 250 for an email address must not exceed 254 characters well that's good to know I should probably set that up on my user table as well well yeah we did that earlier didn't we let's create another helper function let's call it email column name 254 not nullable and unique no I think the not nillable and unique is dependent on where that email is let's do this so now I can say email the column is email not nullable and unique but then down here I can just say email well I need to pass in the table forgot that Oh buddy Table email cool and one of the things I just did there is this this function actually returns that so that way I can chain it so I can say email dot not knowable and dot not unique or not unique or as down here this is the email for a manufacturer so potentially two different manufacturers have the same email or maybe the email is missing so that's why I'll allow it to be nullable yeah cool and then we need to reference the address table so I'll say references table and reference address so that's good I think that's it so that's it for the manufacturer table I do have to go though so I think I'm gonna stop there but you can see where this is going at the end of the day I'll just have to implement these other tables I'll have to add the foreign key constraints and after that our our tables will be ready to go and we can start potentially inserting in sample data and then eventually create a an API on top of it but let's just do this real quick let's let's run the code I'm gonna do a connects rollback real quick just to make sure that this actually runs yes I need to get rid of that cool so we rolled back and now we're gonna do a migrate latest cool and if we look at our database structure ah where's where's that that tool thingy where did it go is that localhost 80-90 isn't this you oh it's not my sequel its Postgres let's go okay cool so now you can see we have all ten tables and let's look at one of the tables that has foreign key references so if we look at the address table we can see these foreign key constraints that got created so there's state ID which targets the ID column on the state oh one thing I totally forgot is we're actually gonna add on delete cascade so you can see right here on delete no action but what I want is if I were to delete something in the foreign table I want all of its references to be deleted as well because then technically it's still data we have ten tables yes oh yeah I'll push to github for sure so because I have this handy references function I can update them all in one go and I'll just say on delete cascade and that will say if I delete something in a foreign column if it has any references to other columns those should be deleted as well now we talked about earlier that we're doing soft deletes so technically this shouldn't really ever happen but if it does I want to make sure that the the constraints are still in place and we don't have any stale data so now that I added that let's roll back and then we'll migrate again and now if we look at this table we see that when I delete a state any addresses that we're referencing that state will technically be deleted because I have on delete cascade so that's good to go all right yeah let's push this up to github um let's see great new repo inventory you make sure I have a good ignore file so I want to ignore my node modules I want to ignore this docker data so I would never push up this DB data because this contains all the fee [Music] what do you call it the data for the database in the docker container and I don't want that on on github so let's add a good ignore this little tool generates that by default it's going to ignore like my node modules but I'm gonna have it ignore the docker data [Music] folder is there anything else oh yeah I mean I think by default we are ignoring Dottie and V so that's good I'm gonna create a sample dot env and let's create a readme an app to keep track of your inventory of items set up will say back-end set up docker compose up I'll say create a and V with your values run docker compose up migrate the database npm run migrate install dependencies does the order really matter not so much needed this database thank you thank you coding pasta I think so the fellow said thanks for the follow-up pants for birds um tomorrow is DJ's birthday so happy birthday DJ the point of the E and V dot sample is this file I'm gonna push up to github and that way when someone pulls down the repo they know what variables will need to exist in the dot env file so technically well not technically I am NOT going to push this dot env file to github because there could be some very secret values in here because it's information for connecting to the database so that won't go to github but this will go to github and in this scenario both my sample and my daddy and ve they're not special in any way but eventually I might actually put some like production information in here and I would never want to push that to github which is why I have the sample file what did I call it yeah okay oh I designed the database last time and that's when we mainly normalize things there's still some things that need to be fixed but we have the the basics of creating the tables there's still more tables we need to create we'll do that next time and then we can start to create some basic API and pull no before that will seed the database so like we'll see the database with states and counties or start countries will see the database with locations in my house item types different things that are gonna be fairly the same after we actually build the application will will see the database so we'll do that next time I don't know when that will be I most likely will be streaming again tonight for code got us start to noon for that good package looks good to me try too hard to rule um a data warehouse database what do you mean by that I'm not familiar with that term what am i doing okay github github we could we could raid fun fun function we've rated him in the past though and he doesn't really seem to pay attention to raids that happen so I don't know if we'll do that we'll see who else's life you [Music] is insta fluff what yeah well we'll reinstall ah cool ok the codes on github check it out here and if you are watching on youtube you should absolutely head over to twitch.tv slash coding garden there it is head over there we're gonna do a good old-fashioned rate which is where we take all the viewers of one stream and put them into another stream oh yeah masterminds is life that might be good to rate let's say use life and there you go andrew has sent the raid message go ahead and copy and paste that message that's the message we're gonna send whenever we go into that other channel oh that's wrong the only thing about mastermind is I believe he has followers only chaton so that's a little tricky for a raid let's see is it followers only I don't know let's see what what is in stuff working on star do you mod that sounds fun and what is fun fun function working on he has this really cool check-in app I was I was watching a stream yesterday see oh he's adding colors this will be fun we're gonna read fun fun function just because yeah and so copy and paste that raid message get ready you're ready copy and paste the raid message be nice ask good questions be friendly when we go over there and like I mentioned I will be streaming look at all the drops I will be streaming again tonight around 6:00 or 7:00 p.m. mountain time which is 5 or 6 p.m. Pacific time which is to look at the channel for notifications of when I go live and things like that we'll be doing code Coty's thanks everyone for hanging out thanks for all the the questions and the chats and just just just in general thanks for being here this was fun so wherever you are in the world have a wonderful morning afternoon evening or night and until next time here's this you you

Channel: Coding Garden

Views: 31,651

Rating: 4.9168243 out of 5

Keywords: web development, full stack, postgres, knex.js, vscode, live coding, mechanical keyboard, mysql, database, sql, learn node.js, educational, learn programming, migrations, full stack web development, css, learn javascript, backend, html, programming, full stack javascript, knex, node.js, beginner, live streaming, docker, lesson, javascript

Id: tLPYc3hJ2Cw

Length: 155min 46sec (9346 seconds)

Published: Wed Mar 25 2020