Complete Intro to SQL & PostgreSQL

Ingredients API Exercise

Brian Holt

Brian Holt

SQLite Cloud
Complete Intro to SQL & PostgreSQL

Check out a free preview of the full Complete Intro to SQL & PostgreSQL course

The "Ingredients API Exercise" Lesson is part of the full, Complete Intro to SQL & PostgreSQL course featured in this preview video. Here's what you'd learn in this lesson:

Students are instructed to use node-postgres to implement the ingredients API in the application. Instructions are located in the code comments in /ingredients/API.js. Installation instructions and troubleshooting tips are also included in this lesson.

Preview
Close

Transcript from the "Ingredients API Exercise" Lesson

[00:00:00]
>> So, there is a repo here that I'm going to need you to go clone. It's called SQL apps. And I'm going to give you just a little tour quickly of it. There are two different parts of the projects. We're just doing the ingredients now, then we'll do a little bit more, and then we'll do the recipes one later.

[00:00:18]
But for now, you can just worry about ingredients, okay? Click on that, there's a couple flat files in here. There's an index.html that basically sets up a bunch of stuff. There is a client side JS, you don't have to touch this one at all. I did all of the client side work for you.

[00:00:37]
But basically, it's just adding and removing event listeners. And it's setting up some like nice kind of carousels. Everything that you are going to write for this particular exercise is just an api.js. This is all using Express, I just did it because that was the simplest thing and I imagine that's one you had, there's a highest probability that you're familiar with.

[00:01:01]
There's this part here where it says student code starts here. And then there's a part where it says student code ends here. So everything you have to modify is between these two comments. And I kind of left you comments of what I expect you to do here to make everything work.

[00:01:17]
And then you'll have to delete these res.status(501) things, because 501 means it's not implemented. You are going to have to turn these into 200's. Basically, you are going to have to write a part where you connect the Postgres, you are going to have write a part where you return all ingredients of a type, and the type here is coming in from the query.

[00:01:35]
And then you're gonna have to write a part where you do a search on some sort of term and some sort of page, right? So, you can return five rows at a time of page one, page zero, page two, page three, so on and so forth, okay? So let me just show you a little bit how to get started here.

[00:01:55]
Open a new tab here, okay, I'm gonna open this in code. So, I've already cloned this repo from GitHub. And, I'm gonna open API JS from ingredients, so the same thing that you should be seeing here. Right there api.js, okay? This is all the code that you're gonna write.

[00:02:25]
There is, if you get lost or you want to see how I did it. I have the completed projects inside of the completed folder, so you can look here inside of ingredients there's another api.js. That is the code that I wrote that and everything worked. Works as long as your database is working right and it has data in it.

[00:02:45]
There's no cheating on this. This is just a project, whatever you need to do to learn. Do that. If you want to look immediately at the code and that's you feel like that's the best way you're going to learn. Then do that, I don't care. I care that you learn.

[00:03:00]
In my opinion, the best way to learn, the way that I learn, is I take a crack at it and I hit the wall and I struggle, and I learn a bunch of things and then I go look at the solution. But if that's not how it's gonna work for you, it's better than getting discouraged, right?

[00:03:14]
If you're gonna get discouraged, then just look at the answer. Okay? So if you're not familiar, the first thing to do when you clone this, you're gonna say npm install. Just like that. It'll go install a few packages. Express, you don't even need the image-downloader but it's there.

[00:03:37]
pg, and then nodemon and prettier. Okay, and then you're just going to run npm run dev. Okay, this is going to run like a little watcher in the background. So every time that you save a file, it'll restart the server for you, so you don't have to do that yourself.

[00:03:54]
And then you can just open this in localhost 3000. You should get a site that looks like this. Okay, you should click on Ingredients it should be relatively empty, right? Because none of it is working. But if you click on Answers, you should see something, this is your end goal of what you want it to make it look like.

[00:04:15]
This is what it will look like once you have all of the things populating. So, this is populating all the fruits, this is populating all the vegetables. You can see that they have little tags on them that identify what types they are. Okay, it's pulling all the images correctly from the images.

[00:04:36]
Notice that if this one, I don't have very many meat things in here so it loops back around. And there's a lot of other ones, other ones. No, we don't have that yet, which is fine. Okay, and then down here you have all the search ingredients. This will go through all of them.

[00:05:02]
And then you can search for things like berry. And you'll get all of the berries. And then you can ignore recipes, we're gonna do recipes in the next project not this one. Some additional troubleshooting tips for people that are having issues just getting anything to work. The first one that one of the students did was they connected, they did all their operations in the Postgres database and not the recipeguru database.

[00:05:36]
Which would be problematic because all of your data would be in the wrong database. And we were trying to query from something that didn't work. The way you can kind of diagnose that happen. If you go to your terminal, and here it should say recipeguru here, where you've been doing all of your querying.

[00:05:54]
And if I do \d where it says, recipeguru, you can see I have my ingredients table. If it says postgres right here, you're in the wrong database. So, the way you connect to different database, \c recipeguru, right? Now, if you hit dash d, if you do not see this ingredients then you have to go and get the, recreate everything here.

[00:06:18]
Or for the sake of just this exercise you can replace where it says recipeguru right here in database. And you can just put postgres there, and that'll connect you to that database, right? Either one of those will work. If you're this far and you've made all the tables and stuff like that, I probably would just go and replace this with postgres, it's fine.

[00:06:42]
That would work. Another thing that I saw another student do is the port was blocked either from running a previous version of Postgres or maybe you have some sort of corporate firewall that's not allowing you to host local ports. The way that you can tell if the ports are messed up, if you go to local host, 5432, which is the default port that postgres is running on, the one that we have it running on.

[00:07:07]
It should say, connection was reset or error empty response. Chrome will say empty response, do I have Chrome up here that I can show you? Yeah, localhost didn't send any data, error, empty response. This is oppose to if you see, if you see this, this is correct. This is what it should show.

[00:07:27]
If you see something like this where it says the site can't be reached and localhost refused to connect. That means that that port is not open, it's not exposed, right? Which means that you need to go figure out there's something amiss with your ports. The way that you might diagnose or fix that is maybe you didn't open the port and docker.

[00:07:45]
Or you might have a corporate policy on your computer that doesn't work. Or you might have to shutdown a different version of Postgres that might be running in the background. Those are all possible reasons. Then again, what that looks like in Firefox here, it will say unable to connect.

[00:08:03]
This says that then you have a port problem. If it says connection was reset, that means it's working, it's fine, everything's working like you think it should.

Learn Straight from the Experts Who Shape the Modern Web

  • In-depth Courses
  • Industry Leading Experts
  • Learning Paths
  • Live Interactive Workshops
Get Unlimited Access Now