Today we'll do Postgres, we'll add the Postgres database to an existing application that we built in the previous lecture. We built it in the front end using React.js and the back end with Express.js. So we use a simple file here in the server, close.json, where we store the posts, but instead of doing this like this, we will leverage Postgres, the database. Make sure you have installed Postgres. It also comes with PG admin, so make sure you also have that. Okay, so let's get started. We got some more people joining. Let me just copy the link to Postgres again. Hello, everybody. We just got started. Please make sure you have Postgres installed. I post a link again to zoom and make sure that you clone this repository here because we'll need the code. If you don't already have this code, make sure you get that. For those who are in the previous lecture, you probably have this already. Anyway, so first let's get the repository. So this is the repository. You can click here and you can copy this. I do SSH already, so I copied the SSH one. So you can go to a terminal. Like me, I have command prompt and let me cd. So make sure you're somewhere and then you're going to do get clone like this if you don't already have this repository. And then once you do that, I might already have it, so that was our result. A new directory called for further introduction till react will appear. You can cd to that. Make sure when you do get branch, you are an express.js.backend because the master branch only has the react application. The one of the express backend is this one. So you can do get checkout express.js dash backend to change or switch that branch. And if you do get branch again is to see a star next to express.js dash backend instead of master. So I open my directory code space dot this will open visual studio code in my current directory. If you have another ID use data, it doesn't matter. Okay. Here's my code. Visual studio code. And if you have the right branch, you should see a server directory here with index.js and post.js up. Okay, to make sure everybody has the repository and everybody's in the correct branch, express.js dash backend. Like I said before, the goal of this class is if you recall, we had a react application here. This is like the wireframe. It's like a chat or message bar. We have messages and we have a new message that you can send. We can run this quickly just to see what is going on. Going back to the terminal. Now I'm going to have two terminal tabs. Okay. One is for the front end server. Another one is for the back end. So here in this one, let's do the back end. So you can do node dot slash server slash index.js. I'm using Windows, so I use a backward slash. If you're a Mac or Linux, that's going to be forward slash. Yeah, another requirement is you need to have node.js installed. So make sure you have that as well. So now the server is running localhost 3001. So if you look at that in the browser, open a new tab in your browser. I'm using Firefox, by the way, but you can use any localhost colon 3001. If I refresh, you're going to see cannot get slash. That means the server is running. And that's the back end. Now let's go to the front end. Back to the terminal. In another tab, okay, leave this one open. Another tab, the same directory, right? CD to the directory if you're not there. Now this one is simple. NPM space start to start the development server for the front end. That's the react application. Oh, by the way, if you have cloned this, you probably need to NPM install. So let me talk about that. Open a new tab in CD to the directory of your project. The introduction. So you notice here, there's a package.json file in the project directory. So that means I have to do NPM space install. If you get any problems, you don't know. If you just clone this repository, you probably don't have the packages installed. So make sure to do NPM space install first before you run all the servers. If you're a for some reason have the server running, just press Ctrl C to interrupt it. Okay, I already run this. So I don't need to do it again. In any case, let's admit more people. If you have any trouble so far, please let me know. We need to get everybody on track in the beginning because it will require all of these things. Okay, we got some people joined. Somebody raised their hand. Do you have a question? You can post in the chat. All right. For those who have just joined, let's reiterate what we have done. We will, we will need Postgres, make sure you have installed Postgres. And then this is the repository that we are using. Okay, make sure you have this branch express js dash backend, because the master band doesn't have the change. All right. So let's go. Going back here to the terminal. Make sure that you do NPM space install. And then going back to another tab, make sure you do node dot slash service slash index dot js. Okay. All right. Everybody okay so far? Again, if you're not serious about learning, I'm gonna have to remove you. I'm sorry, but we're not open to messing around. This is not the right place. Okay. So if you're here for that, just please leave or I'll have to remove you. This is your warning. Okay. Any case, let's continue everybody. So we got the backend, we got the front end, and then let me check the browser here and see what the front end looks like. I think the front end is in local host colon 3000. Okay. All right. Now that I've got people out of the way for being patrols, we can keep going. So this is what the front end looks like. If you go local host 3000, it should be working fine. Here's a message you can type and click send. And it should be added here. And if you refresh this, it should remember because it's in the backend that file. If you have done this, let's go here. You have been in the previous lecture. Remember, the server is in server slash index.js. And if I go here, you see the route to create a new post is this one. This function is called and it writes to the file posts.json. That is this one here. And you can see if I scroll all the way down, it adds the post that I just added to the end. All right. Now somebody is asking to enter the room and I'm not going to let anybody else enter from this point because I think they're just trolls. Okay. So keep that in mind. If you're trying to do something bad here right now. Anyway, let's go. So you should have already installed Postgres. It comes with this application called pgadmin. So make sure you open that. If you don't have that, please let me know. But I already have that open here. That's very important because we need to create the database and the tables using this graphical user interface. This can also be done in the command line using a command like pcql. But I'll focus just on graphics because it's more convenient and the pcql will probably take more time to do. Let me make sure to open pgadmin. In my case, it's four. If you don't have it, or it's not working for you, let me know. I will assume that you have it if you don't say anything. Okay. Now I'm going to use the default settings for Postgres and basically you should already expand servers and have one here. If you don't have for some reason, you can right click servers, register server and give it a name. And under connection tab, you can add local host and all the rest is default. Okay. Postgres maintenance database and the username and the port is 5432. Now the password, if you have set a password for the Postgres user, you have to type it here and then you click save and you should have it available here like one of these and you can just expand and you will see all the databases. I have already some you probably don't have one if you never use this. Okay. So far so good, everyone. So let me just go back to Visual Studio Code to let you know what we're going to do. So right now we take all the data and store in this file, but that's not ideal. We want to store that properly in a database. In this case, we're using Postgres SQL. Now each of these objects will actually become what's called the table, right? And that table have the columns user ID, ID, title and body just like this. Now I think we're not really using body. So we could omit it, but for the sake of just demonstrating how this works, we can include all of these. Okay. Now first, we need to create a database going back to PG admin. We're going to right click databases, create database. Now give her a name, I'm going to call attack role and score development. Now the reason I put underscore development is because I want this database to be only for development and not for production. So you want to make a distinction there. If you, you have a database for your production websites that everybody's using, and you have one for development that just you as a developer is using, you don't want to mess with the production one. So I'm going to choose the Postgres owner by default and click definition. I don't think there's anything else special to add in the other tabs. Now one thing to note about PG admin is if you click the sequel tab, this is going to appear not only in this panel, but in other panels as you're creating tables and doing other kinds of operations. This is very convenient because it tells you what this graphical user interface is doing behind the scenes. So it's actually calling the database and with this command create database. So if you were to use the command line to create this database instead of the graphical user interface, this is what you would write. Okay. So PG admin is really nice because we can select everything graphically, but actually it all boils down to this command here. So it's going to create database, stack role development, with all these stuff, owner postgres, encoding utf-8, that all these defaults is fine for me. So once you're satisfied that you can click save and that should create the database here. If you want to edit the database, you can always right click and click to properties. Let me see here. I think the thing is hard for me to go all the way up. Let me see what I can do. Make the screens bigger. You see properties there? That's the last one. And you can also access everything again. In any case, I'm going to close and I'm going to make it as big as it was. Let me see. Like that. Any questions so far? Were you able to create the database? Okay. So in the database, we have a bunch of stuff. What we're interested right now is creating a table. And for that, that's under schemas, expand that and then expand public. And you're going to see tables here. Expand that there's no tables right now. So that's why you don't see anything. So to create one is very simple. We always follow the same pattern, right? Right click tables. That's the resource create table. Now we have that same kind of panel. We can give it a name. Now this is the table for posts. So I'm going to give the same name I gave the file, right? Remember posts.json. So I'm just going to call this posts. So typically in the case of Postgres, a good naming convention is all letters lowercase. And if you have more than one word, you want to separate them like this with an underscore. And we typically, you can either choose singular or plural. I like the plural naming convention. So that's why I call it posts. Now the reason I do that has to do casing problems and all that kind of stuff. So depending on the database, SQL flavor, you might have a different naming convention. I know Microsoft SQL server, they like to use Pascal case like this with the, you know, the first letter of each word capitalized. So you follow whatever you're working with. In my case, I'm doing Postgres. I'm going to use lag. All right. Now somebody asked if it is, is it a best practice to use that main account to own the database and create another user account? Well, typically you want to, you want to create that separate user for your, you know, different databases. For example, if I have this database here, I'll probably create a different user. That's not the Postgres one. And just allow that user to only change this database and nothing else, right? That user cannot create other users. That user cannot do all these privileged stuff. So that's a good practice to always narrow down and start from a zero privilege and only give permissions as needed. Okay. But for the sake of our, our quick introduction here, I'm just going to use the default, but you probably want to choose to make a different one. Yeah, that's a good question. Thank you. Okay. Now we want to go to the columns tab. And this is where we'll be creating the columns. Now I'm, we could do this all in the command line, right? The commands like add column, alter table, add column, whatever. But for the sake of using the graphical user interface is more convenient in this case, but you're going to see in the SQL tab what the commands are for this. If you want to practice it by hand later. In any case, let's just copy what we had for the Visual Studio Code. Remember, we had user ID, ID, title and body. ID is the primary key here, meaning it's the one that's going to uniquely identify each row in the database. So let's start that. Going back to PgAdmin, going to add the ID first. So you click the plus button to add a column here and type the name. Now data type here. So for the ID, it could be, it's typically an integer. And in recent times, because we have so many records, people like using big int, because it allows a greater number to be typed than the integer. But for the sake of having this as a primary key, I'm going to choose instead serial. Serial is just a special thing that's actually creating an integer that's auto incrementing. Okay. But it's going to create an integer in the end. It just creates a sequence in Postgres that the ID is going to be auto incrementing, meaning every time you add a new record, the ID auto increments. For example, if you start from one, and you try to insert a new record, the next ID will be two, and then the next will be three, and so on. So I choose serial. Serial is maps to integer. If you want a big int, it's big serial. Okay, I'm just using integers a bit serial there. Now I don't want it to be, you mark it not null. True, because we don't want this to be nullable, right? We always need it to have a value. And I make it a primary key because this will be the identifier for each row. So that's it. But then we got to add the other ones. Don't click save yet. Let's add. What's the other one? We got the title. Now that's the character varying thing. So we can use character varying here. Make sure to select the one without the brackets. Okay, the square brackets, a different one. I have made that mistake once and I didn't know what I was going on. But character varying without the square brackets. Now I can give a maximum length here if you want, or the standard seems to be 255. So that seems okay to 55 characters max for my title. Now I don't want it to be nullable, so I'll mark not null. It's not a primary key. So I don't toggle that. And then we do the same for body. Yeah, the brackets means array, correct. Okay, body, same thing character varying. And maybe we can give it 1024 characters. How about that? And then not null. Although if I could not know here, I think it's going to mess up our program. Maybe I'll make it nullable just for the sake of example. I'll let it be no. Okay, so we don't have to specify a body. That's optional. And finally, the user who posted the comments, user underscore ID, notice I'm using underscore instead of chemo case. So be careful with that. And for this one's going to be an integer, usually this would point to an ID, maybe you had a table for users. So that would point to that, but we don't have it right now. So I'll just leave it like that. And not no, true, let's let's just add a random one, if we need to add a user. All right, or this would point to somewhere else in identity service, some ID string or whatever. So in my case, keeping a simple integer, but it could be a string. Okay, so I think that's all. I haven't missed anything. Four columns. Now, if you go to advanced, there are other properties, I don't care about them right now. Constraints, if you need to add in constraints, they're here, uniqueness, primary key, foreign key, we already did the primary key before. And partitions don't care. And so on, go out of the way to SQL. This is important. Because this is the actual SQL command, the query that will be executed when I click save. So if you're see this create table. And then every line is one column definition. So ID of type serial, not no, title character varying to 55 not no, follows the same pattern. So if you want to learn how to create a table, copy this down. And at the end, it defines the primary key as the ID column. And it's I think it's setting the owner to post grass here too. In any case, let's click save. And now we have a table posts. If you ever need to change the table, add columns, remove columns of any other other properties. Again, you can always right click, click properties, just like we did for the database. And you're going to go and change whatever, for example, change the color. Now, if you notice here, look at the ID. Now it's integer. Remember, I said about serial. So serial just makes it integer with an auto increment, which is done via this default value here next valve of the sequence. If you can read this, let me try to post underscore ID underscore seat, as a cumin sequence. So post grass has this thing called sequence that keeps track of the next ID value. In any case, I'm going to click close. And if you notice up here, before tables, there's a sequence, you can expand that you can see posts underscore ID sequence, that's what I was talking about. And if you right click properties, you will see under the definition tab, the current value increment, minimum maximum, you see this is the maximum value an ID can take. And it will increment by one and right now is the current values one, as we insert, it's going to go up, right? It's going to two, three, and so on for the current value. Anyway, that's the mechanism for the ID primary key auto increment. Any questions so far? Were you able to create the table of all the columns? All right, let's see. Yeah, if you're curious about doing all this in a command line, you would use, for example, the P SQL command. When you install post grass, you should have access to that at somewhere in the post grass installation folder. If you type P SQL in your terminal, it should open it. If you get a nerve, that's probably because that command is not in your path variable. So you have to add the director where it is, it is placed in your path so it will see it or you can type the whole path. If you're using Windows C colon slash whatever, or if you're using Mac slash user slash something or whatever. All right. So now let's insert some records. So let me teach you how. Let's add some records. So we're going to go to tables. So highlight the table you want to insert records, right click, and you're going to go to the script and you're going to find the insert one. So this is a nice thing. It has a pre filled query for you and you just have to change the values. So if you ever forget how do I insert in SQL, you can just click there and it will automatically write it for you. And all you have to do is substitute the values here. So insert into a post table. These are the columns and the values in parentheses separated by comma. Somebody said I've used pcql before and the UI is not very user friendly. The GUI pgad means so much more friendly. Yeah, pgad means it's very nice. I typically use pcql when I'm working on the server. If I have a server that doesn't have graphical user interface, that's when I would use it. Or if I need to run a query from somewhere else like remotely, that's an option as well. You can make it, there are some options to make it more nicer. If maybe you're trying to do some selects and it's all messed up because so many columns, you can actually make them appear like in a one liner format for each column. I think you can do backslash x, enter and try selecting. It will be much nicer. In any case, let's continue. So let's insert just a sample title here. So you might have noticed the ID is present here. You actually don't have to specify it because it's auto incrementing. So if you can remove it, it's going to change it accordingly. Or if you really want to keep it, make the value default here. Okay, that's another option. Because this corresponds to the ID and the default will be the next value in the sequence. If you remember for the properties of the column. In any case, I just remove it. Oops, by ID, it's gone. Now let's put the title in the question mark. Hello world, something whatever. And this is the body for the next one. And then user ID, let's just add one. Let's assume there's a user ID one. And yeah, so when you're ready to submit this query, click the play button. That's the hot key of five. And you see it successfully inserted. Now if you want to see the data that was inserted, right click the table name, view added data all rows. And you should see them here nicely. And they tell you what query they used. They did select star from public dot post and order by the ID ascending. That's nice. They tell us what was run. And then in the bottom is the actual rows. And you can see ID is one title hello world body. This is the body. And the user ID. And what's nice about this is I can actually click to add another row here without making a query by hand. So I can click here and just type the title. Another message. Okay, I don't need a body user ID one. And I can click save f six is the hot key. And it automatically fails the ID for me because it's auto increment. So that's nice. And I think you can also delete if you highlight. There might be some options here is this trash can if you don't like that delete and then make sure to click save there otherwise it doesn't happen. Any questions so far? Were you able to insert records? If not, we can move on to the JavaScript code and finally integrate with our express application. All right. So now going back to express, we're going to need to install library and that library is called PG. If you go to npmjs.com, that's where all the node package manager registry entries are. Look for PG. And this is the web page. So we're going to use this library to connect. They also call it a driver, I guess, yeah, to connect with Postgres using JavaScript or Node.js. Okay, let's first go to the terminal and install that. You can kill your server if you want to control see npm install PG. If you recall, I combined the front end and the back end in the same repository and the same package.json just for convenience right now. But in a, you probably want to separate that into different things because one is for server, one is for clients, one is for clients, one is for clients, we probably wouldn't have two separate npm projects, one package.json inside the server directory and another for the client directory. Some people even like having separate repositories all together. One repository just for the back end, one repository just for the front end. Okay. There's also the monorepo people who like everything together. So you choose whatever you're, you like. Anyway, we just installed it. Let's go back to Visual Studio Code. Where is it? Show all windows. I've been minimized. Let me share. Okay, now it's showing server slash index.js. So what we're going to do, if you recall from the code, or we haven't seen this code, all we do is require express, put in a variable, and then we call that as a function and make this thing called app. And then we can set middlewares here with the use function, parse express.json. This one is to parse.json requests into the rack.body variable. And then we can use app.method.http in this case get and pass the path. For example, if you make a htp request, get slash post, it would hit this function here. If you do post slash post, it would hit this function here. So we use the get slash post to get all the posts, right? We return all the posts. And we use the post post to create a post based on what the user gives us through the request body. And this case just cares about the title. That's what we type in a chat box. And then we are writing to a file if you can see here. And the file I think it's red. Let me see where is it red. It's red here in line two. If you do a require in Node.js with a JSON file, it automatically do a file, fs file read, and parses as just on parse. Very convenient. So we don't have to do that separately. And yeah, that's pretty much the two endpoints that we have. Now we got to make sure to remove, stop doing from the file and actually do it from the database. So before we do all the routes, let's set up the database here. So we're going to need a PG client. So we're going to require a PG at the top, put that in a variable, you can call it PG. And then PG has a property client that we can call like, like this new PG client to instantiate an object. And you can give some options with an object as the argument, put that in a variable, you can call PG client or anything else you want. Now the options here are important because we need to define, okay, what's the database you want to connect to? What's the password? What's the user? So the database would be the one that I chose there, they can be K tech world, or tech world only underscore development. And then the password, I have a easy password, I don't care about telling you about. And the user was postgres. Now very important, if you need any more settings, you cannot specify here, port, and all the other stuff. Okay. But I think if I don't specify, it's going to use the default port and all the other settings. Okay, typically, we don't commit passwords and all these sensitive data to the code. So we have either a configuration or secret management solution, the simplest way you can do it just have a file called dot amp, right? Yeah, we can do that right now. So if you're concerned, and let's just have it working, then we'll do the dot amp. Yeah, dot amp package. Exactly. Okay, then we got to call PG client dot connect. Now this connect here is asynchronous, meaning it returns a promise. So if you really want to wait before you define all your routes, you would have to use dot then here, and do all your route definitions inside. Or you can use async await, that's another way. But the problem we're using async await, in the top level of this module, is that node will complain about it, you cannot use it outside an async function. So to get around that, you can make an async function here, set up app, and put everything inside. And then later you call it just for the sake of being able to use the wait, because you can only do that if it's an async function, or you can go to promise dot then routes. Up to you. I'm going to do async like this. Async await. Now I'm going to move all my stuff here, all this stuff, I'm holding Alt in my keyboard, and the press Apple arrow, that's what I can move all the stuff. All we can just cut and paste. Okay, make sure it's under the block for the setup app function. Like that. I'll say that let's try to make a query for all the posts here. So I'm not going to send this post anymore, like that. I hope I didn't make a mistake here, we'll find out later. Let's go. So pgclient dot we're going to make a query to see all the posts. Let's see. Query, and then you pass a string is the argument select star from posts. And that should get us everything. And I think this might be, yeah, this is asynchronous. Again, you have two options. Can do that then, or async await, let's do that then for this. So let's take an example, you put a dot then there, result as the argument parameter, you can call it whatever doesn't have to be result. And then here you're going to get an object, something, and that result will be an array, okay, by default, an array of objects. What's nice about pg, this pgclient is it converts all the rows into objects already. So you don't have to do, you know, convert from an array to object, that kind of stuff. So here, let's do result dot rows, that's the property you want to access the rows. And we can do res and that. Let's see what we get. All right, let's go back to the terminal. Now I have node mon here, if you haven't noticed, npx node mon dot slash server dash index.js node mon is the package we installed so that I don't have to keep killing the server and restarting every time I make a change to the code. No one will automatically do that. So let's try it out. Okay, I got the client running to let's just go to the browser and just hit the API without hitting the client, go to localhost 3001 slash posts, see if it's working. This is what we got. This is the live preview in Firefox, but the raw data is like this one liner, I don't know if you can see it. I can pretty print like this. So it's an array of objects. And every column is a property of the object. That's very convenient. We don't have to do any change. But if you notice, my user ID is underscore here. So maybe if you have more than one word for your columns, you might need to map it to chemo case, if you really care. Because typically in JavaScript, people like use chemo case, user, capital ID. So we would have to kind of every time convert this transformed from one property type to another. So keep that in mind. I think we can also do an alias for the database to avoid having to do this. Now that I think about is you can actually do an alias user ID underscore ID as user capital ID. I think that's how object relational mappers do. So we don't have to do it. Adapter in the code. So you could try that. Any case, I'm not using that. So I'm going to skip that for now. All right, so it's working. Now we got to hook it up to the app. It's actually just already hooked up. So just go to the front end, refresh. It's already there. Right. Same request, nothing changed from the point of view of the client. It's the same kind of response, except for the user ID underscore ID, instead of capital ID. But we're not using that in the front end. Okay. So far so good. How's everybody doing? If we're all good, let's do the dot m to avoid having to commit sensitive information to the version control. Let's go back to text editor where we connect to the database. We don't want to do this. So we want to take that from let I'll just do the password and you can do the other ones. Okay. It's the same kind of process. Process dot m dot database password. This is how we want to do this. I don't want to have it here. I want to have it in the variable database underscore password that's been injected into the process dot m. And to do that, we need a dot m package, like somebody mentioned before. So going to the terminal, kill the server, control C, npm install dot m. This is just so it parses the dot m file and into and injects the property and values into process dot m. Of course, good. I've done it yourself, but we don't want to reinvent the wheel. Just do that package. Okay. Now I got that. Let me put back no more in there. Just so I have the server running. Going back here. Actually, I might need to kill it because dot m because it doesn't list to dot m. In any case, I'm going to put the dot m in the project root of the repository, not in server. Okay, because I've been calling everything from the root. So dot m is the name of the file. And I'm going to define database. Oops, underscore password. And then the value is separated by a equal sign. And then you put it there. You don't have to have quotes optional. Up to you. And then you define just like this, you have another one database, username, postgres, and then database, database. I want to prefix everything with database. I know it's kind of redundant. It could be just database, or whatever. I like having the prefix. I think the database back for all development. If you want to put everything in m, this is how you would do it. Yeah, database database. In any case, going back, I choose whatever names you want. This is just an example, whatever prefix or name you want for your variables. Now going back here to be able to access that you want to inject that into process dot m by calling, you're going to require dot m here. Now you can put that in a variable. And then call dot m dot config function like this. If you want to do everything at once, it's fine too. So you don't have to have a variable dot config like that. That's what they do in the documentation, I think. Either way, this will make sure to read the dot m file inject all the variables in process dot m. That way you have access to them here. So if I want to do the database same thing, put them like this database on the short database and user process dot m dot database underscore user. That way we don't commit anything to version control. Now I have to tell you, don't commit dot m, right? The purpose of dot m is so we don't have to commit it. So you probably want to add a dot get dot m to the get ignore file here. So that it's ignored by get and never accidentally committed to version control. Now the downside of this is what if I clone this repository from a fresh start that that's not going to work because I don't have a dot m. So what people do is typically create a dot m dot example or whatever. And they tell the user, hey, you should copy dot m dot example to a new file dot m and fill up filling the blanks. So I'm just going to copy dot m in here and put dummy value your password like this your user your database. And then you would replace these with your values after you copy this file to dot m dot example. One good practice to add that to the read me. Let's do that. So how to run I'm going to go to read me dot md. I'm going to add how to run. And I'm going to say copy dot m dot example to a new file dot m, then fill lean the values for each variable. So that way they know what to do in a fresh start. And then for the sake of reading me, I don't have this yet run. I'm going to add this install dependencies. Why we're doing this npm install. And then you can add all the other stuff, right? Have to terminal. This is just a practice of writing read means one for a server node us. We could add all these commands to npm scripts because this is kind of local. It's to be cross platform. So it's not very good. The way I'm doing it. So to do make npm run that or something. That'll be nice to have. In any case, I don't want to talk more about the read me. I just add the other stuff quickly. All right. So going back to we got the example. So users remember now the example five can be committed because it's nothing sensitive. And it's so signal to the users that they have to copy this and make a dot m file. Well, all that said, going back to index.js save that now got everything using dot m. Let's go and kill my server here. I don't think it listened for changes in dot inf. So I kill the server and restart. And let's try going to the browser. I don't have to have the front end. I can just go to the back end directly and refresh. And I can still see the same thing. If it works, no problem. The change we made the refactoring using dot inf worked perfectly without an issue. Yeah, now let's finish off with the creation of a post. Let's go back here. Now that we have the database set up. And we can even remove this post JSON here. If you don't want it, remove it. And where's the other one, this line. And this right file sync is going to go right, we don't write to files anymore. And we don't push to posts. Now we got the new post here, rack dot body dot title coming in. And then we send back the new post information. Okay, so this is what we have to do. Let's do a sync now. I did like using dot then. I'm going to show you how to do a sink a wait, make the function handler here a sync. And then here we're going to do a wait, pg client dot query. And this is going to be what insert into posts. And then we got to specify the columns, right? So ID title, body. Well, as the other one, user ID underscore ID, we don't have to put the ID is optional values. Now this is important here, don't put the stuff interpolation, please. That has a lot of risk for SQL injection. We're not going to interpolate or concatenate anything here. We're going to use dollar one, dollar two, dollar three separated by comma. And then the second argument to query will be an array of all the corresponding values that will be substituted in these placeholders to avoid SQL injection. So this is going to be coming from rack body. Now typically, we would we would typically filter out undesired properties and validate them before doing anything with database, okay, to avoid any malicious problems. Some people are going to try to put a lot of stuff here in your request that has nothing to do with the actual use case. They're going to try to do malicious stuff. So we haven't done this, but I leave a comment here that we typically do that. Okay, so rack body, what's the other one? Oh, actually, we have new posts, right? You can use that variable. Assume it was sanitized. It isn't sanitized, but we would have assumed. So dot title and then comma, new post dot body, even though we don't have it. So it can be nullable for the sake of it. Just put it there. And then the user, we don't have a user right now. So you could say current user that ID, whatever that is coming from, typically rack dot user or something like that. We can just define it here for the sake of having something. Assume it was defined somewhere else in the authentication pipeline. For the sake of this lesson, hard code here. Okay, now I want to add something back here, returning star so that we got back from the square with the actual row. So we can return it just for the user to have an ID included in the object. I think that's how we do it, returning. Yeah. In any case, you can do this await query. Now, if you want to deal with errors, you would do try block. And then you catch here, the error and do something report error, handle error, etc. We don't have anything right now for the sake of time, we're out kind of out of time. I just console error. Okay, but you would report to your whatever error servers you have, in any case, and put that in a variable. Let's call it created. Actually result. But we got to get rows. And then here, instead of sending new posts, you would do result dot rows, sub zero, because rows is an array, you know, it's going to be only one, right? That's the assumption here. So we do sub zero. And I hope that works. We didn't forget anything. Oh, I put the variable I have to do outside, right? Because the try block, it's not in scope. So let result be outside. Or it could have moved this inside either way. Okay. And I'll have to do it. The problem with this console error here is I probably want to send a return, res, status, something, maybe, I don't know what the error be, let's for the sake of the example, just put 500 with nothing information. If otherwise, if we don't do this return, okay, the return is important, it will try to send the return results row, even though there isn't one, because of the error occurred, and it went to the catch block. So make sure to put a return there. I always put return before my rest through because I don't have to remember because God, what if somebody accidentally did something here? It would still be executed. It's not like other framework. So you have make sure if you put return, we will return right away. So I always have to have it there. Very important. Okay, let's test it out. Going back. I think my server has been restarted by no non going back to the app. Hello to send refresh still there. Let's go to PG admin. And I'm going to go right click post view added data or rows. And I can see it here. Hello to and the users one like we hard coded nobody because we don't have any and see ID three. And I think the front end returned. If I look at the dev tools, we have 12. Let me see if I can do it again, little three send. I see the posts here, you see that this is the options when I see the other one, we see the other one response has an ID four, because the front end submits just the title, but it doesn't it is it assumes like, oh, okay, everything went well. Can you give me it's usually good to get the ID back so that we know exactly what this message is about. So in case you had to delete it added or something like that, you would specify the ID of the resource. Otherwise, you don't know which message in the database you just got, and you cannot operate on it. In any case, it's working fine, here's the raw. Yeah, and that's, I think that's pretty much it, you can go ahead and do more stuff with it, add more stuff to the front end to handle body, maybe handle the filtering out of undesired properties on all these endpoints in this only one right now, you can add an end point to edit. If for some reason, when I added your messages, delete, and so on follows the same pattern. Now I want to tell you, we typically don't run queries. This manually, we use what's called ORM object relational mapper. And we will we will write JavaScript code that automatically generates the queries, all this stuff for us instead of us having to delete that sorry to write it all manual, although in some cases, if you have care about performance or have a special query, people would write the manual. One ORM I typically use with node is called SQLize. If you want to interested more in this case, and you don't have one, I recommend SQLize to try out. Yeah, and with that, I think I'm going to end this lecture. Thank you so much for watching.