MARTIN OMANDER: Hey, Roger. ROGER MARTINEZ: Hey, Martin. What's up? MARTIN OMANDER:
Oh, nothing much. I see you still have dog
photos on the wall behind you. You're still doing
dog photography? ROGER MARTINEZ: I still do. And that reminds me,
last time we spoke, we did a REST API with
a Firestore back end. But a lot of comments
have come in asking us to do the same REST API
but with a Cloud SQL back end. What do you think about that? MARTIN OMANDER: Oh,
that sounds fun. Let's do it. ROGER MARTINEZ: Let's do it. [MUSIC PLAYING] MARTIN OMANDER: All right. So I created a
bark-bark-api project here, and I guess now we need to
create a Cloud SQL instance. ROGER MARTINEZ: Yep. And that shouldn't be too hard. It's pretty simple. They make it pretty simple. It's a few clicks. MARTIN OMANDER: Should
we do MySQL, Roger? ROGER MARTINEZ: Yep,
sounds good to me. MARTIN OMANDER: Let's do it. All right. And here, how about
instance ID barkbark? And for a password, I will enter
my super secret password here, visible for everybody. ROGER MARTINEZ: Yep, let's
keep this between us. MARTIN OMANDER:
Yeah, that's right. Iowa and SQL 5.7. ROGER MARTINEZ: Looks good. MARTIN OMANDER: All right. Let's see. Maybe for this bark-bark
API we don't need four CPUs. ROGER MARTINEZ: Yeah, not yet. For development purposes, let's
shrink that down to something a little bit smaller. MARTIN OMANDER:
Yeah, that's right. A little bit smaller and
a little bit cheaper. ROGER MARTINEZ: Yeah. MARTIN OMANDER: So one
CPU I'm sure will be fine. ROGER MARTINEZ: Mm-hmm. MARTIN OMANDER: All right. And let's hit create. This would be a good time to
go and refill that coffee cup. ROGER MARTINEZ: Yeah. I'm going to go walk my
dog, so I'll be back. MARTIN OMANDER: All right. So we're back. The database has being created. The database server
instance has been created. What's next? ROGER MARTINEZ: Now we
have to actually create a database for our data to live. MARTIN OMANDER: Oh, right. Because one database server
can have many databases on it, right? ROGER MARTINEZ: Yes. MARTIN OMANDER:
So maybe dog data. Should we call it that? ROGER MARTINEZ: Let's
name it dog data. And this should only
take a quick second. There it is. It's done. MARTIN OMANDER: Nice. ROGER MARTINEZ: So now we have
an empty database, no tables or anything. So let's connect to it
via the Cloud console. MARTIN OMANDER: Yeah. ROGER MARTINEZ: Now we're in. MARTIN OMANDER: All right, cool. So here we're in the database. Let's see if we have any tables. Oh. We need to pick a database. So we'll use this database. So we're in dog data database. Let's see what tables we have. Oh, no tables. ROGER MARTINEZ: None. So now let's create a
table called breeds. MARTIN OMANDER: Breeds. OK, that sounds good. ROGER MARTINEZ: And this is
going to have the same fields that we did in our last video. So it's going to
have a name field. MARTIN OMANDER: OK. ROGER MARTINEZ: Let's
leave varchar 30. Make that unique, so we don't
have more than one breed in there of the same name. MARTIN OMANDER: Very nice. ROGER MARTINEZ: Life expectancy. MARTIN OMANDER: Life
expectancy, yep. Is that an int? ROGER MARTINEZ: That is an int. MARTIN OMANDER: OK. And what is it-- type, right? ROGER MARTINEZ: Ah, we
should have had type before life expectancy. MARTIN OMANDER: Oh,
was type before? ROGER MARTINEZ: Yeah. MARTIN OMANDER: Let's do that. Very good. That means I have to spell
"life expectancy" again. That's a hard word. ROGER MARTINEZ: Yes, you do. MARTIN OMANDER: And then
was it origin, right? ROGER MARTINEZ: Origin,
that's another varchar. MARTIN OMANDER: All right. So we now have a table. All right, let's see
what's in the table. ROGER MARTINEZ: There
should be absolutely nothing in that table. So last video you mentioned
you like poodles, so let's add poodle to our SQL database. MARTIN OMANDER: OK. ROGER MARTINEZ: Insert
into breeds, values. Don't forget the
"values" key word or else it's going to yell at you. MARTIN OMANDER:
I always do this. ROGER MARTINEZ:
Error message, yeah. MARTIN OMANDER: All right, good. ROGER MARTINEZ: Poodle
is a sporting dog. MARTIN OMANDER: Oh, sporting. So sporting, like that? ROGER MARTINEZ: Yep. Life expectancy is about 14. MARTIN OMANDER: 14, very good. ROGER MARTINEZ: And
they are from Germany. MARTIN OMANDER: Oh. As much as I like poodles,
I didn't know that. ROGER MARTINEZ: Now you know. Boom. OK. MARTIN OMANDER: There it is. ROGER MARTINEZ: How's that look? Yep, looks good to me. So here we go. Now we have a REST Poodle API. So now we can jump to
the code and make it so that we can check this
out with HTTP requests. MARTIN OMANDER: Oh, my
favorite part, code. So let's see. We have package.json
here from previously that you had written. Thank you for putting
this together, Roger. ROGER MARTINEZ: Mm-hmm. MARTIN OMANDER: You put
two dependencies in here? ROGER MARTINEZ: Yeah,
we're just going to have Express and MySQL. So Express is going to
serve as a HTTP server, and MySQL will be used to
communicate with the SQL database on the back end. So it's as simple as that. And then we also
have that start line. That's what we're
going to use to start our app on the container
once it's up in Cloud Run. MARTIN OMANDER: All right. So I see here the start
script mentions index.js, so I guess that's where
the action is, huh? ROGER MARTINEZ: Yep, that's
where the magic happens. So to start out with, not
a lot going on, once again. We're initializing
the app with Express, and then we're having
it listen to port 8080 where it'll consult
log that the REST API is listening to port 8080. And then it's going to
reply to any HTTP request to the root with a JSON object
saying the status, which is, bark bark, ready to roll. MARTIN OMANDER: All right, cool. Let's run it, see what happens. ROGER MARTINEZ: And there it is. MARTIN OMANDER:
Listening on port 8080. So we got here in the code. Now let's see if
we can get here. ROGER MARTINEZ: Yeah, in a new
window, just do a quick curl. Localhost 8080 and
see what we get. MARTIN OMANDER: 8080. Whoa, look at that, Roger. Looks great. ROGER MARTINEZ: Ready to roll. But as you can tell,
there are no poodles yet. So now let's add
another app get function that's going to return the breed
information that we specify in the URL. MARTIN OMANDER: All right. So you said we'll have
another app get here. How about I start typing and
you can explain what's going on? ROGER MARTINEZ:
Yeah, that's good. Sounds like a good plan. So we're going to
have another app get. This time it's going to
take the variable for breed so that we can attach our
breed to the end of the URL. And then we're going to
construct a query, which is going to be a
standard SQL query, select everything from
breeds where the name is equal to that breed
variable, and then we're going to take that
query variable and throw it into the
pool.query method. And this is pretty
much what's going to relay our request to the
SQL database on the back end. So if there's no
results, we want to let the user know that
there are no results. So yep, status not found. Sounds like a good
way to let them know. If there are results, we want
to respond with a JSON object with that result in it. And I referenced the
pool variable up top. Let's define that. MARTIN OMANDER: Yeah. ROGER MARTINEZ: And
this is pretty much the pool of connections that
our application has to the SQL database on the back end. So we're going to put a few
environment variables that aren't defined yet. Well, they are
defined, but we'll show that in a little bit. But this is pretty
much the credentials that it needs to access
the database that we just created a little earlier. MARTIN OMANDER: Yeah,
and I like to usually-- let's see, process and,
oh yeah, the instance. Right. I like to make this-- there we go. I like to make this usually
a global variable like this. So because it's a
global, then it's reused between invocations. So if two HTTP calls hit the
same Cloud Run instance that has served the
request before, it can just reuse the
pool variable here and doesn't have
to create it anew. ROGER MARTINEZ: Right. Let's scroll up a little bit. Let's make sure-- let's
take that v out of the app get function. Typo-- caught that. And just checking it out real
quick, it looks good to me if it looks good to you. MARTIN OMANDER: All right. ROGER MARTINEZ: Yeah. I think now we can deploy this. MARTIN OMANDER: And you sent me
to this handy-dandy deployment script. So I'm just going to run it. And let's see here-- ROGER MARTINEZ:
This script might look-- it's going to
look very familiar. We've used it before. It's going to have a little
bit more information in it because, since we're connecting
to a Cloud SQL instance, it's going to require
a few specific flags and the environment
variables that we reference in the index.js codes. So those are the
environment variables up top, cloud project ID,
the instant connection name, the database credentials. And those are referenced in
the second command there, cloud run deploy. The first one,
cloud build submit, is going to build our
container and submit it to the cloud, the
Google Container Registry under our projects. MARTIN OMANDER: Yep. And here in the second one,
you said-- oh, right here is where we're using
all of those variables, like the database
password, database user. ROGER MARTINEZ: Yeah,
and it's important that we include also the
add cloudsql instances flag as well,
because that's going to allow our container
to actually use Cloud SQL back end. MARTIN OMANDER: Yeah. And it looks like it
deployed it down here. ROGER MARTINEZ: It
looks like it finished. So now let's jump back
to the cloud console. Let's check out the
cloud build page. MARTIN OMANDER: All right. Let's go to-- ROGER MARTINEZ: And
let's see how that looks. MARTIN OMANDER:
--build, Cloud Build. ROGER MARTINEZ: Cloud Build. MARTIN OMANDER: Succeeded. ROGER MARTINEZ: 23 seconds
ago, so it looks all good. If we head over to the
registry, Container Registry-- MARTIN OMANDER:
Container Registry. ROGER MARTINEZ: --we
should see our container. There it is. MARTIN OMANDER: There it is. ROGER MARTINEZ: The
latest, so 1 minute ago. So that's definitely it. Let's tag that
with a version 1.0. MARTIN OMANDER: The
most dangerous version of any software. ROGER MARTINEZ: Version 1.0. MARTIN OMANDER: All right. So here we are in Cloud Run. We see our service here. And let's look at the revisions. 100% is going to
the latest revision. That's looking good. ROGER MARTINEZ: Let's click
that URL, see what we got. We should off back get
that ready to roll step. There it is. Ready to roll. OK, looking good. Now let's take our Poodle. MARTIN OMANDER: OK,
the moment of truth. Poodle. Look at that. ROGER MARTINEZ: There it is,
poodle, sporting, 14, Germany. Boom. So that works. MARTIN OMANDER: Excellent. ROGER MARTINEZ: So what's next? I think now we want to be able
to add more entries once again, because, again, having one
entry is not very useful. So let's jump back to the code. MARTIN OMANDER: It is
kind of sad, the database. As much as I like
poodles, it's a little sad to only have poodles. ROGER MARTINEZ: And we're
back in index.js, right? Yep. So we've had our
two get functions. We've defined pool. Now what we're going to
do is add a post function. So that's going to
look like app.post. MARTIN OMANDER: Start typing. Here we go. app.post, you said. ROGER MARTINEZ: Yep. And we're just going to send
a post request to the root of the callback method. It's going to be this
asynchronous function. We're going to throw our data
into this variable object, because we'll use it
again in a second. So req, body, name-- that's just taking
the requests data, because the request is
going to be a JSON object. So it's going to take that
data out of that JSON object and throw it into
this data variable. Name, type, life
expectancy, origin. Close that out. And then we want to
do what we did before, which is make that
query variable and now construct our query,
which is another simple SQL query. It's actually an insert
query to insert breeds, into breeds these values. So these question
marks are going to be used to insert our values
in the pool.query function right there in that
second argument. And if there's an error, we want
to respond with a JSON object saying that there was an error. So yep, status failure. And the reason is going
to be the actual error. If there is no error, well, we
want to reply with the object that you just added
to our database and a status of success. So here's where we
use that data variable that we define in the beginning
of this post function. So that looks good to me. MARTIN OMANDER: Yeah. Let me just eyeball
it here, make sure I made no typos this time. Yeah, looks about right. ROGER MARTINEZ:
Looks about right, and that's good enough for me. So let's deploy this once again
with the deploy.sh script. MARTIN OMANDER: All right. ROGER MARTINEZ:
And there it goes. MARTIN OMANDER: So,
Roger, I wonder here, in deploy.sh, we're
putting the password here into a plaintext in a file. It feels a little iffy. What do you think about this? ROGER MARTINEZ: It
doesn't feel good. You should never do that. This is just for
demonstration purposes. So typically you'd want to use a
more secure way to handle that, like a Secrets Manager. MARTIN OMANDER: Oh, right. That's part of the GCP, right? ROGER MARTINEZ: Yes. MARTIN OMANDER: Yep. OK, cool. But then other variables that
aren't as secret as passwords, they're fine to have as
environment variables right? ROGER MARTINEZ:
Right, that's correct. MARTIN OMANDER: OK. So it looks like the
deployment finished. We'll go back here. I guess we'll see
here and refresh. ROGER MARTINEZ:
We can check back on the Container Registry page. MARTIN OMANDER: Oh, right. Let's do that. ROGER MARTINEZ: And let's
tag that second upload as our version 2. MARTIN OMANDER:
Yeah, let's do it. ROGER MARTINEZ: I feel like
jumping from one to two, it seems like a big update. MARTIN OMANDER: Yeah,
being able to write, yeah. So here we have that one. We'll do version 2.0. We are rolling. All right. ROGER MARTINEZ: Cloud Run,
bark-bark-api-service. Let's hit that URL
once again, make sure we didn't break the
original functionality that we had, which was just poodle. MARTIN OMANDER: And
100% goes to the latest. Yeah, let's make sure
we didn't break it. Just hitting the root there. Still getting through poodle. ROGER MARTINEZ: Poodle. MARTIN OMANDER: Let's
see if we broke it. ROGER MARTINEZ: We
didn't break it. Let's try one more thing. Remember, we have
an error message if we don't have that dog. So let's try a pug. MARTIN OMANDER: Pug, OK. ROGER MARTINEZ: Beloved pug. MARTIN OMANDER: That's not
my favorite kind of dog. We'll try it anyway. ROGER MARTINEZ: OK, not found. Good, so our error works, too. So now let's jump to
Postman and see if we can add pug to our database. MARTIN OMANDER: All
right, let's do it. ROGER MARTINEZ: So Postman. The audience may have seen
it in our past episodes, but this is pretty much a tool
that we use to test REST APIs. Super handy for sending
posts and get requests to HTTP servers. MARTIN OMANDER: Yeah. It makes it a lot easier to
send post requests, as you said. I'm copying the URL here. And top of the
morning-- all right. Very nice. Here we go. So there's the URL. Let's see. Can we make this bigger? ROGER MARTINEZ:
Make that bigger. MARTIN OMANDER:
There, that's better. We are not going
to post to /poodle. We're just going to
post to the root. ROGER MARTINEZ: And that
was referenced in the code, posting to the root. We're going to send
this format of JSON. MARTIN OMANDER: Yeah. And let's see here. It's a little tricky. You have to select raw and
then JSON for all the types to work out correctly. So here, I just
copied the poodle. Now, we're not going to
paste the poodle again in the database. That would actually
lead to an error, right? Because we are
using a constraint. ROGER MARTINEZ: Yeah, it
should give us an error, and it won't add the poodle. But let's add pug since
you love pugs so much, too. P-U-G. Pug is a companion. MARTIN OMANDER: Oh,
not a sporting dog? OK. ROGER MARTINEZ: Surprise,
it's a companion dog. Life expectancy is about 13. MARTIN OMANDER: 13, OK. And where is that-- I don't know where that's from? ROGER MARTINEZ: The
pug is from China. MARTIN OMANDER: Oh, that's cool. I didn't know that. All right. ROGER MARTINEZ:
That's a JSON object. Send that off. MARTIN OMANDER:
Let's send that off. ROGER MARTINEZ:
Sending that request, we should get a success status-- yes-- and then a copy of
the data that we just added. Just to make triple sure, let's
hop into our browser and go to the /pug URL and see
if we get our pug data. MARTIN OMANDER:
So we do pug here. And there it is. ROGER MARTINEZ: All
right, so now we've got two dogs in our database. So now, it's a
limitless opportunity to add dog breeds
to our database and serve them via HTTP
requests-- all server-less. MARTIN OMANDER: Very cool. This was a fun
programming project. ROGER MARTINEZ: Yeah,
it was pretty easy, too. Surprisingly, it's not a lot of
work to get us up and running. MARTIN OMANDER: Yeah, right? All right, cool. Thank you everybody
for watching. This episode was created thanks
to feedback and questions from you in the comments below. So if there's anything
else you would like us to program here,
please add it in the comments, or if you have any questions
about how we built this. See you later. [MUSIC PLAYING]
