Add a PostgreSQL Database to React Query Builder

[00:00 - 00:12] Now we will add data to the database and serve it with an API. We'll use a local database for testing, so be sure to have PostgreSQL installed on your machine and have a database created.

[00:13 - 00:26] There are many tutorials online for how to do this, but I've linked to the official documentation in the transcript. Once the database is created, create the sales table and insert the sample data by running the provided sales.sql file.

[00:27 - 00:38] You can find the link to the file in the transcript. You can add data to the database using the command line or by using a database client like PgAdmin.

[00:39 - 00:55] The database connection string will be stored in an environment variable called "database_url", which Heroku has already set up for us on the remote server. We must do the same locally with a .env file in the root myapp directory.

[00:56 - 01:10] We can also set up a dev mode environment variable to distinguish between local and remote deployments within the code itself. You can see here I've got my .env files created already and set up.

[01:11 - 01:25] I'm not going to show it on the video because it has my password in it, but you can look at the transcript to see the format of the file and the format of the connection string. Now we'll add the API endpoint.

[01:26 - 01:47] First, we'll import a few more dependencies into index.js. Right here below the required.env config line, we're going to say const pool types equals require Pg.

[01:48 - 02:19] Then const format query equals require query builder. And one more const process SQL equals require the local file process SQL, which we'll create in just a second.

[02:20 - 02:45] The process SQL function will take the parameterized output from the format query function and prepare it for use by the Pg package, namely converting the question mark bind variable placeholders to $x, where x is an integer, and preparing any date fields to be properly processed as dates. Create a file called process SQL.js in the server folder.

[02:46 - 03:04] So in the server folder, we'll create process SQL.js. And this is going to contain a function called process SQL.

[03:05 - 03:26] It's going to take a SQL parameter that is a string, and then we're going to return that string with a couple of replace functions attached to it. First, we need to declare a variable called i, set it equal to zero.

[03:27 - 03:41] This will be our integer counter for the bind variables. So we'll return SQL.replace.

[03:42 - 04:02] And we'll find the question marks with a global regular expression. And we'll process that by giving it a function that increments i.

[04:03 - 04:25] So in the first iteration, when it when it first matches the question mark, i will become one. And we'll return a dollar sign, and then the i variable.

[04:26 - 04:46] And the next thing we want to do is replace the order date and ship date fields , plus their operators and bind variables. With the same thing with colon colon date on the end of the bind variable, so that it converts it to a date.

[04:47 - 05:07] So the way we're going to find the clauses is another regular expression. We'll start off with a word boundary, and then capture with parentheses, order date, or ship date.

[05:08 - 05:30] Close parentheses. And then we'll find our operators, which are equals or not equals, or less than , or greater than, or less than or equal to, or greater than or equal to close parentheses.

[05:31 - 06:01] Open up another capture parentheses set, and we'll find dollar, and then backsl ash d for digit characters. Close parentheses, and look for another word boundary.

[06:02 - 06:18] And then we're going to replace that with dollar one, which gives our first match, dollar two, our second match, and dollar three, and then colon colon date. And that should do it for process sequel.

[06:19 - 06:31] Now we just need to export this. And then I'll format it real quick, make sure everything's okay.

[06:32 - 06:43] Save it, and go back to index.js. Next, add a configuration for the numeric data type coming from PostgreSQL and set up the connection pool.

[06:44 - 07:13] The PGE package outputs a number of columns as strings by default, because Post greSQL numbers can have a greater precision than JavaScript numbers. So right here above the app equals express call, we'll say types.setTypeParser for the data type 1700, which is the numeric data type in PostgreSQL.

[07:14 - 07:32] We're going to parse that by taking a vowel and just parse float vowel. Okay, and that should turn all of our numbers into actual JavaScript numbers.

[07:33 - 07:53] Next thing we're going to do is set up the connection pool. So new pool, and that takes an object with a connection string, which we'll get from our environment variable.

[07:54 - 08:20] database URL, and then the SSL key, if this is dev mode, then we just want to set this to false. Otherwise, we'll set it to an object that has reject unauthorized set to false.

[08:21 - 08:37] Finally, add the API sales endpoint just above the app.listenline. So down here at the bottom, we'll do app.post, since this is going to be a post call.

[08:38 - 09:10] root directory API slash sales, and that's going to take a function. It's going to be an async function because we have to await some of the calls. It takes a rec and a res parameter, and we're going to first parse the query out of the body.

[09:11 - 09:41] We're going to get the SQL and the parameters from format query. This is going to be a parameterized type.

[09:42 - 10:21] The where clause is going to equal process SQL, SQL, and our select statement, which we'll call select raw data, is going to equal select star from sales, where, and then we insert our where clause here. We'll give it an order by of order ID ascending.

[10:22 - 10:40] So on, we're going to log those to the console. So we'll log our select statement and our params.

[10:41 - 11:00] Okay, now let's get the, let's run the query against the database. So we're going to start by declaring a variable called data. And we need to try catch the query run.

[11:01 - 11:12] So we're going to say data equals, we need to await the query execution. Pull dot query.

[11:13 - 11:22] Select raw data and params. And we'll get the rows attribute from that.

[11:23 - 11:57] Now if we catch an error on this step, let's log it to the console and return in the response dot JSON data as an empty array chart data as an empty array. And the error as is, and then we want to return because we're done if we catch an error in this step.

[11:58 - 12:30] And the next thing we want to do is get our chart sequels set up and execute the query to get our chart data. So const chart sequel is going to equal select, we're going to do date trunk and truncate it to the month of order date.

[12:31 - 13:01] And we'll convert that to a date because it returns a date time object and we want to, we want to date, we'll call this order month. Then we want to get the sum of total revenue and we'll call that revenue.

[13:02 - 13:39] We also want to get the sum of total profit and call that profit from, that the from clause is just going to be the same thing as we selected before the select raw data function. So select, select raw data and we need to call that sales raw.

[13:40 - 14:19] Then we need our group by clause so we're going to group by date trunk by month , order date, and we'll order by one, which is our month. Okay. Now that we've got our chart sequel set up, let's declare a variable called chart data.

[14:20 - 14:39] That's going to start out as an empty array and another try catch block for chart data. Same thing again, we await the query execution.

[14:40 - 14:52] We pull that query chart sequel and same params as before. And the dot rows attribute of that.

[14:53 - 15:15] If we catch an error here, we're going to do the same thing, log it to the console and return this same thing here. So I'll just copy that and return.

[15:16 - 15:33] Otherwise, if we haven't caught any errors yet, we'll just res.json. The data object, the chart data object or array rather.

[15:34 - 15:44] And for error, we'll just set that to null. Okay, save that off.

[15:45 - 15:55] Since we're running the server with nodemon, it should restart automatically after we save our changes. Just view the application again at localhost 5000 and see if everything still works.

[15:56 - 16:01] So let's go to our application. Refresh.

[16:02 - 16:12] And it looks like everything is still working. We shouldn't notice any difference here because we haven't really changed the user interface.

[16:13 - 16:19] We've just added some stuff to the server. So I just wanted to check that it was still working.

[16:20 - 16:27] In the next lesson, we'll configure the client application to retrieve data from the new API and display it on the screen.