Summary of SQL Table Creation and PGAdmin Use

Okay, now that we have learned SQL language, let's jump into practice with building our own table for the app. So I'm going to use Postgres. You've got to have Postgres installed that and that should add come with the PGI being graphical user interface client as well. Looks like that. If you remember, this is the repository I've been using. Web client is where this React application is and basically there's a messages page and people can comment here by saying whatever and click comment. Right now we are loading this data from an external back end. And we're not starting to refresh is not persisted. Basically, all this information will be starting in the database. Now for that, we need to create a table. Now this would be a table of messages. So we can call the table messages. Then we've got to figure out, okay, what information do we need to keep track of? Well, there's the comment, I'll call it comment, which is the text somebody types. Maybe you want to keep track of when this was created. We can create a column called created at that's basically a date with a time. And there could be the column for Okay, who created this. So we need a column for the author, author ID. Remember, we usually never store. If you need to store information about a person that goes into the like a user's table, for example, but we're keeping track of messages, what we do is we point the to the ID of the person in the user's table, right, we got a foreign key, and that's going to be the author ID that would point to the primary key in the user's table. And I think that's, that's about it for now for a very basic thing. Of course, you could add more stuff, more columns with time as your feature grows, and you add more stuff. And obviously, we need the primary key to why uniquely identify each comment, somebody could write the same message Hello, how do you keep track of who said what right, the same person could write Hello twice. And how do we distinguish between them? Well, we got to have the ID column, the primary key, which is unique, right has a unique constraint. So enough the talk, I'm going to use PG admin. Let's go. Like I said, when you install it might have to set up a password for the interface itself to unlock the thing. And then there's a password for the Postgres. That's the default user row. Now if you go here, I already have my servers. I don't know if you have one, if you don't have one, right click, register server. I give it a name, like whatever. And then you want to go to connection. Use the default part, default user postgres. If you have a password, you have to type it here. And I think that's about it. Save. And you should have one of them like that. For example, let me add one more. My server two. By the way, sorry. Host name should be local host. Or 127.0.0.1. I think my password is necessary. Save it. And yes, working. There you go. Anyway, I can use any of these. So if I expand that, I should see databases, login group roles and table spaces. So the databases here are basically the databases. Usually we make a different database for each product and environment. And we have to login or user or rows. So let's not use the postgres user and not use the postgres default database. Let's create our all. So typically we would go here, right click, create, login or group role, and give it a name. See full stack user. I already have one, so I'm going to say two, okay, because I did that before. Now the definition, you can give it a password. Make sure you remember that. Dummy password. And privileges, you have to have can login. Otherwise it won't be able to do anything. So you need that. And that's about it. One nice thing about PG Admin is if you click the SQL tab, usually at the very end on the right hand side, it tells you what's actually going to run. Because understand that PG Admin is just a graphical user interface. It's just a front for what's actually doing behind the scenes that is just running SQL queries like we were doing before. So the reason for using PG Admin is convenience. We can do things with clicks. And this that I just did is basically going to run the create role command with what we wrote there, login. And here's the password. It doesn't show. But yeah, so if you were to do this manually, you would have to run this command. Okay, save. And that should create a user here. If you ever need to edit that, you can always right click properties and change it accordingly. For example, if I press three here, instead of two, I can look at the SQL. And this is the command that it would run to rename that user alter role renamed to. So if you ever need to find out how to do things in SQL, you can just click in PG Admin and click SQL here to find out what the command is. And you don't even have to run it. I can click close to cancel. Yes, changes will be lost. Yes. Great. Now let's create a database, right click databases, create database, and give it a name. Go underscore stack DB two, because I already have DB. The owner here, let's choose that user that we created user to my case. definition. I don't think we care much about these. Right now. Yeah, I think that's pretty much all if you click SQL, this is what it would run in the background, create database with the owner and other stuff, save it. Now we got a database here, we can expand. And if you go to schemas, expand public expand, you can see there's tables here, there's sequences and other stuff. So let's go to tables, expand, there's nothing, no tables, let's create one. Right click, create, let's create that table for messages. messages. By the way, the way I name tables is for Postgres, I follow lowercase. And if I need have multiple words, I put an underscore between them. And I usually like the plural form of words. I you're free to use other naming conventions, maybe if you're doing another implementation of SQL like SQL server from Microsoft, they'd like to use Pascal case, which is basically every word has the capital letter, the first letter. And they have no underscore or whatever, between the different words. Anyway, owner is going to be full stack user two. And schema can be public. And click columns tab. And here we can add all the columns, press plus. We need an ID, right? That's the primary key. Now the primary key is usually integer. See here, all the data types, if you ever wonder what data types are for the columns, here's the whole list. And I can type to filter. So it could be integer, or it could be today, people like using big ends, which is allows for more values than integer. But for the purposes of this exercise, I would use integer. But there's a catch of this. It doesn't have auto increment. So for the post grass, what we usually use is serial. Now I notice serial or big serial, big series to create a big end. Serial is basically integer with auto increment using a sequence. Okay, so we choose serial instead of just playing integer. And then you don't want that to be no and it's primary key, right? And that's what the default is being taken care of. We don't have to do anything. By the way, not no means. Can you have a no value, which is like basically empty? No value, basically no value. This is saying I don't want to allow an empty ID, you always have to have one. And they can plus plus to add more. Maybe you want to comment, which is basically what the user types, it could be fair care. But if you type fair care doesn't find it why because you got to say character varying. And then 255 for length, that's the parentheses of fair care. And if you want to make it not know, do you want to allow empty comments? I wouldn't think you would. So I'm going to say not no, it's not primary key. We don't need the fault value. And you can keep going maybe I want to when was this created? Okay, created at is usually what we use for the name. Notice I use underscore to separate the words. And everything's lowercase. That's my convention. And this would be what's called timestamp with time zone. Okay. This one. I don't need length or anything. Not know you could. Yeah, you can add not know there. Sure. We don't want an empty value. Finally, that's add author underscore ID, which is foreign key to the user ID. Right now we don't have users. So but let's let's make believe there is a user stable. We can create later if you want. This one would be just integer. If the ID of the user stable is an integer. And if you want to always have somebody belong to a comment, you have to put not know here. Otherwise, you could have comments without an author. And that's it. Let's go advanced. I think already have primary key here. So the constraint is already added. This is the constraints, you can make things unique and other stuff. You don't care much about right now. And that's it. If you click sequel, this is the command that would be executed. As you can see, it's create table and public dot messages. By the way, there's a namespace. We didn't do much of that. But basically, there's a schema called public. That's the default. But even if you remove that, it would work either way. It'll be chosen automatically. And like we did before, a definition is one per line. And this is not not no thing. You always add not know at the end before the comment, if you want to prevent any no values. And you see the primary key constraint here, saying that the primary key is the ID column. And it's adding the owner here with alter table change owner. Yeah, so click save. And that should run that command. And now we got messages here, if you expand, you'll see, we got other stuff columns, we got constraints, which is the primary key, and other stuff. If you ever need to change the table, right click, and you can choose properties. Often you find that it's, it's the last thing. So it's hard to if you're like scroll down here, it's hard to do it. Like in the middle, it doesn't let you do it. Oh, where is it? Where is it? So I usually either you do like this, and it goes up, or you do like this. So I can reach it. And I go can change it. And if I do is to comment to here, just for the sake of example, I changed the name of the column, quick sequel and see alter table rename, comment to comment to do to do something like that, I'm not going to do it so close. Okay, so PGI means very nice, I can do many things. Before I move on, I want to show you there are certain tools you can leverage. If you click tools here, there's the P SQL tool, there's a query tool, the P SQL tool is like a command line interface. And if you click that, it's basically like this, it's connected to this database. And you can run SQL commands here. For example, one P SQL command I could use here, backslash D. And it tells me all the tables and sequences. So I got the messages table here. And then I can do select star from messages. And it will do it. Obviously, there's no rows, right, I can do the insert right insert into messages. Let's see what we got ID one, right. I think we don't need to say the ID here because it's auto increment, let's try without the ID. Comment, let's say hello, well, in quotes, created at you can use the current date by saying now, parentheses that we should take the it's a function for the date. Author, I'm going to assume there's a user author one. And yeah, now it's complaining about syntax. Let's see. Probably the ID, I think if we put the falls here, I wonder for the ID. No, what's going on? What did I miss? Oh, I forgot something right? What did it forget? What's the keyword? I always forget it. Every time values. That's why let's try again without the ID. Still, let's put default. There you go. Yeah, so this one expected the ID. And to do the auto increment, you can put default and it will pick the next available from the sequence. Basically, the way IDs work is if you notice here on the left hand side, there's the sequences thing. If you notice going back to messages, like property, just for the sake of explaining in this columns, you see the column ID is now integer instead of serial. And there's now default value next vowel. And this is basically from messages sequence sequence. But basically, it goes to this, what's called a sequence here. And it takes the next value right now current values one, increment one, blah, blah, blah, the maximum value there. So it keeps taking the next integer in the sequence. Now we can do select star from messages. And there you go. One thing I find very nice about PC, I use it a lot. If you find there's too many columns, it's going to be a mass to read. So I like to enabling the vertical mode by saying backslash x, expanded displays on if I do the same query, by the way, I press up up in a keyboard to look up what I did before. It's now like vertical. So much nicer to see if you ever have that problem reading too many columns, of course, can I select specific columns to get around that problem. If you want to disable that go back to original backslash x. If you ever need trouble and you need to remember the commands of PC, backslash eight will tell you all the I think these are SQL commands, right? I think there's a help or something. Let me try help. Yeah, there's help. So backslash eight is for SQL commands, and backslash question markets for PC, cool commands, which is basically what the ones backslash things. And you can press in not answer but spacebar to go. And I think big, let me see, control. It doesn't go back just spacebar, I guess. And I think q to quit, yeah, q to quit, rescue. Anyway, this is the one we use backslash D list tables views and sequences. And you can do that with the table name like backslash D messages and it will describe all the columns. So basically p sequence like a command line interface to whatever we do through other means, right? If I want to describe the columns, obviously, I can expand here in the PGA main I can right click messages properties, click columns to visualize them. But basically, it's doing this behind the scenes. Okay, so that's p SQL if you like that tool use that. There's another one called a query tool, which is basically the previous version of this. If you do query tool, you can type the queries here like we did in the previous session, like select star from messages, let me call and click play or f five. And the result is at the bottom. So it's much nicer here, right? graphical. And then you can investigate stuff. I can even think add new records if you click add row. I can add number two. Hey, there. And you can use now parentheses, you saw observed the the author, let's try another person to any click this save data changes icon, that six. And now we got a second row. And see the date became the current date. Basically, the date, space, the time. And I think the time zone, my cases, I mean, minus four from the, the center used to be called G green image time. Now it's called UTC by GMT. I'm Easter time. So that's why it's minus four. Yeah, so very powerful, you can delete rows, I think the trash can will delete it. And you have to save it. And you can copy, paste, even other functions, your save results, the file, a lot of stuff going on here, I can load a open file if you want to external SQL file that we wrote some queries, it can click here and it will copy and paste, basically, to this. So it can do insert like we just did graphically, right, inserting to messages, values. Now I don't forget. Let's see. I think if I were to say like comment, created at author idea, don't have to say default. Let's try. Another comment created now, author, let's put one. Yeah, this one. Let's say what happens here. Key already exists, violate constraint. Yeah, so this didn't work. Let me try ID default. There you go. Now I can do, by the way, all the queries you do, there usually has tabs here at the top. It's kind of hard to see because it's very cramped. But if you click these arrows, you can see if I go back to this previous one, it's PC equal and this one is the query tool. And there's other stuff here going on SQL, blah, blah, blah, and so on. Anyway, you can do a select or another way to select is right click at the table, and click view added data, and you can select all rows. And that should give you like, automatically fill out select star and order by, I didn't talk about order by basically can order the output by a specific column. And this is a sending it could be d e s c for descending. And there you go. So if you right click tables here, you can have scripts to do other stuff. If you don't remember, you want to create something select insert update delete, like insert will give you a boilerplate already written for you just got to fill out these question marks. Likewise, you can do try to create script. I think that's to create the table. I do create that's pretty much tells you what's the the SQL to create this specific table. See create table and other ones. Update. And it's there. So you can never forget how to do SQL, because PG admin is powerful. If you just click something as well as refresh your memory about what keywords you have to use. And see the constraint here can also properties. This is a unique for the ID, and so on. Okay. Yeah, so not much. What else can I tell you about PG admin? I really love PG admin. So if you're using a different secret implementation to have similar programs, like my SQL workbench, and other stuff like SQL server has its own Microsoft graphical user interface. So it's not much different. To be honest, they kind of the same. Let's see all other tools we got. You can do the other backups and everything here. They got that. I think you got to click database or something. Let me see. Yeah, backup. If you click the database, whoops, whatever. I don't want that out. I don't know if you can see it, but accidentally moved my thing that we reset layouts. Reload. There you go. Increase my font size, you can see better. If you click a database, I think the tools backup, you can try that if you need to save a backup database and there's other stuff. Okay. Yeah, since we got more time, we can kind of create the user stable for the program. If you recall, we kind of had a form here to sign up. We can try to create that as well. Using PG admin. So we got email password, data bar. Let's try, let's try. So PG admin full stack to be true schemas public tables, right click, create table, I users table, owners going to be full stack user to columns. Okay, we got primary key. Let's do that ID serial, because we want to add auto incremental integer. It becomes integer later with a default value taking next valve from the sequence. Not knowing primary key. What else we got for you is email, right? I think that's character varying right email. Is there a mayor greater length of 255 got to do some research? What's the maximum length for email? I don't want it to be no not primary key. Password not a password. Usually we don't store the plain password in a database table. What we do is we always encrypt and hash right so that if it's ever compromised, they won't have access to the actual plain password, but just the hashed version that's very might it's kind of hard to go back because hash is basically a one way encryption. You cannot go back. So the only way they could kind of try to do something as if they build like a table of hashes and compare them, something like that. Anyway, we don't have not talking much about security. So be character varying with the hash. If you want don't want it to be no and what else we got data birth, right? Oh, we know called it's called birth dates. This one we just a date right no time. I think there's a date and the format is year like why why why why dash month month dash day day. Not know. So it can pretty much make them not know or if you want to allow any field to be empty you mark you take this toggle off. We got biography, which is basically character varying biography, character varying, I would say 1024 characters at most, but if you really really need unlimited text, you can use text that's pretty much unlimited. Obviously, in practice, nothing's ever unlimited, but but I'll use character varying and keep it at 1024 characters at most a favorite programming language. Oh, we had a favorite programming really long name. I don't know if it's great to have but that one I'll just use a string character varying. It could be HTML CSS or JavaScript. You look at the form just a silly thing to add. It's not really serious. But we have it here in the form. So three choices. Pick one at 255. I'm going to let it be empty and a biography be empty. Favorite color, same thing is kind of tedious, right? Favorite color character varying 255. What else we got, we got to agree to the terms and subscribe to newsletter. We can add maybe terms agreed. This is basically a bulletin right true or false. Yes or no. So that's the value Boolean. And you can make here a default value if you want to be false. And then we can add newsletter subscribed. It's also Boolean true or false. And if you want to default, make everybody be false by default, you can put it false here. And all the existing records will take that or if you don't specify this value this column, it will take this default. Okay, I think that should be enough. What else meaning for users? I'm sure there's a lot more you could add but I think this is getting too tedious. We already have the constraint for primary key. And here's the sequel that's going to be run. Save it. Now we got a users table. And we can start adding new records insert script. I write click and click scripts insert by the way. And it can just substitute the values here. Let's use the fault ID, which is the next in the sequence. Email. Let's say Joe and mail.com. Fast words. It's going to be a hash. I don't know what to put whatever here. It's going to be a hash. Biography. Hello, it's me. I forgot the birth date. Sorry. Let me take this out. Birthday is basically a date. You can put a string like 20, like 1980 dash the month, let's say January dash the day the first zero. It's always this format. And then favorite programming language, HTML, baby color, red terms agree. Did I agree? Let's say I agree true. And I didn't subscribe to the news letters of false. And yeah, that's it. Right click, view added data. All rows. I now have this here. And I can scroll. And I found you to change anything. I can go here and I think you can change and okay and press save. Let's say I like green. Okay, and you have to save it. There you go. Okay. There's also things called associations that you can kind of associate. For example, I could, if you notice I created messages, but I put author IDs. But these users, user two doesn't exist. But you could add some association to check. Okay. There's a user stable. This is pointing at referencing user stable ID. So you could add that as well. I'm not going to do that much of that, but left as an exercise when you study model SQL. There are companies who don't kind of don't use that because of the scalability because it gets so big and they want to avoid. They have trouble with they have to pretty much split the database into different instances and spawn many of them. Usually what happens you have millions of users accessing the database and it's overloading it. So what they do is they create replicas of the same database and usually there's one database where you're supposed to write or add information and the others just for reading information retrieval. So that's all that for scalability if you have a lot of users, but if you're just building a normal app, you don't have to worry about that. Just a small app, not many users. Okay. Yeah. So I think we went really fast. No. Nothing much to say on that. We're going to use this tomorrow. Basically connect the front end with the database through a backend server and express JS. And if you recall, this is the app. And let's focus on messages, click comment, press here is going to call on the backend we're going to build. And that backend is just basically going to make a query. Okay, I'm going to go and insert into messages and here are the values. And then once that is complete, the database going to say, okay, good, inserted. And then we take that and reply it back to the client here. Hey, you're all good. I was able to create it. And then you can the next time we refresh, we're going to see the new comment persisted there because it's going to ask the back end. Hey, I want to see all the posts and the back end is going to ask database select a star from messages and then take that and then direct back to the client. All right. Yeah, if we have any questions, let me know. Otherwise, I'm going to call this session.