Create Astro API Endpoints to Get and Update Database Records using Drizzle ORM

Lazar Nikolov: [0:03] Our Like button in the Article Page remains unimplemented. In order to keep track of the number of likes for each article, we need to implement an API endpoint that's going to receive the article slug and increment its likes number in the database. Lucky for us, Astro makes it very easy to implement API endpoints.

[0:20] Back in lesson 13, we defined a GET endpoint that returned our RSS feed. In this lesson, we're going to define an API endpoint for the PostLike functionality.

[0:31] Let's start by creating a directory into the source folder pages, and we're going to name it api/likes. Inside, we're going to create a new file, it's going to be a dynamic route with the segment slug.ts, so that's pages/api/likes/ [slug] .ts. That's going to be our endpoint URL.

[0:56] First thing we want to do is to export a new constant called pre-render is going to be assigned to the value of . Since our Astro config file defines the output as hybrid, all of the pages and also endpoints are being treated as static pages and endpoints. Because we want to treat this file as an SSR file instead, we need to tell Astro, and we can do that by exporting the pre-render flag.

[1:30] Astro will execute this file every time we send the request to it, instead of only executing it during build time. Now, we can define our methods. We're going to export an async function with the name of GET. We're also going to export an async function with the name POST.

[2:00] The GET function will get executed when we send the GET request to the endpoint. We're going to read the number of flags and return them. With the POST function, which is going to get executed when we send a POST request to the same endpoint, we're going to find the article into the database and increment the number of likes.

[2:08] Both of these functions will utilize the API context in order to obtain the slug. To have proper types, we're going to import the type API context from Astro. Now let's go down to the GET function and define the context as an argument and set it to the API context type.

[2:28] To obtain the slug, we just need to destructure it from the context.params property. The first thing we want to do is to check if the slug is not provided. In that case, we want to return a new response back to the client with the string of bad requests. We're going to provide a second argument, it's going to set the status to 400, which is the bad request HTTP status code.

[2:52] If we do have the slug, we can now query the database to get the number of likes. To do that, we first need to import our DB instance and also, our Likes table from our DB file. We're going to jump out three times and get into the DB directory. There we go.

[3:12] To query the database, we need to create a new constant. Let's name this to entry. We're going to assign it to db.select, which is going to generate a SELECT query for us, .FROM, and we'll pass the Likes table. That selects, in this case, everything from likes.WHERE.

[3:32] Now we want to filter out the entry whose slug matches the one we have from the context. There is a separate method for equations, so we need to import it. It's called EQ, and it comes from the drizzle-orm package.

[3:46] Going back to our WHERE method now, we're going to use the EQUALS to indicate the likes.slug property will equal to the slug that we have from the context. This basically reads, give me everything from the Likes table where the slug column equals to this value.

[4:07] That's not all -- in order to execute it, we'll just invoke the GET method at the end. That's how we can query our database. Now we need to check if the entry does not exist.

[4:18] For example, if you pass a slug that doesn't belong to any article, our entry constant is going to be . In that case, we need to return a new response again, where the message is going to be "Not found." Then we'll pass an object and set the status to 404 because no such entry exists.

[4:43] Lastly, if the entry exists, all we need to do is just return a new response for the payload. We're going to do JSON.stringify(), and we'll pass the entry and then we're going to provide the options. We'll set the status to 200, which is the HTTP status code. Of course, we're going to define a header(Content-Type), and we'll set this to application/json.

[5:08] The first argument is the data that we want to send back to the client. So far, we sent a string because they were simple cases, but now we actually want to send back the entry from the database. Because we need to turn it into a string, we need to use JSON.stringify() on our object.

[5:22] Aside from setting the status, we also needed to define the Content-Type header and set it to application/json, so that the client will know to expect JSON data instead of plain text. Our get function is done. Let's use it now.

[5:36] Let's go to the components and open the postlike.astro file. We'll scroll down all the way to the bottom and we're going to create a new function called refresh likes. We're going to immediately call it below so that it gets invoked when the page loads. The fetch method for the refresh likes is going to be very similar to the one we have in the hit like.

[6:01] Let's copy and paste it over. We're going to remove the second argument, so the request is get by default. Then when we receive the data, we'll just set the number of likes to the data.likes property. In our catch clause, instead of alerting, couldn't like the post, we're going to copy over the if statement and replace the alerts.

[6:30] Instead of setting this to data.likes, we'll just set it to as a string. If you remember, if no entry is found with that specific slug, our endpoint returns a not-found request, which will trigger the catch callback. We'll set the number of likes to just . Let's open the inspect element and go to the network tab and hit refresh.

[6:42] As you can see, we're making the first API call, which hits /api/likes and then /astro's content collection API, which matches the slug of the article we're currently looking at, but the response is a 404 not found. That's exactly what we expect. Now we can define the POST method. Let's go back to the slug.ts and do pretty much the same for the post.

[7:06] We're going to obtain the context. We're going to destructure this slug from the context.params. If the slug doesn't exist, we're going to return a new response with the message bad request and the status code of 400.

[7:23] If this log exists, we're going to try to obtain the entry, so db.select. from likes where the likes.slug column = the slug that we have from the context. Then we'll do .get to obtain it. This is where it gets different. If the entry does not exist, we need to create it. This is going to happen when we hit the first like for that particular article.

[7:58] Let's create a new constant, call it inserted, and do db.insert into the Likes table, then .values and provide the values as an object. We're going to pass the slug as it is, and we're going to set the number of likes to 1 because this is that first like.

[8:09] Then we're going to invoke the returning method in order for the query to return the newly inserted entry. To execute it, we'll just invoke the get method. At this point, we have our first entry in our database, so we can just return a new response.

[8:25] We're going to JSON.stringify the inserted constant and set the status to 200 and also the headers to define the content type header to application/json. This is the same thing that we did for the get request handler.

[8:52] Then in case the entry exists into the database, which means it's not the first like for this article, we need to update it. Let's create a new constant, update it, and assign it to db.update the Likes table. We're going to invoke the set method and we're going to set the likes property to entry.likes + 1.

[9:10] Now you'll see an error here. entry.likes can possibly be no. Let's wrap it with parentheses and add a new coalesce operator to return so that we can still treat it as a 1 additional like. Now we need to invoke the where method to tell Drizzle that we want to update that specific slug. We're going to use the equals method again, just like we did before.

[9:35] We're going to pass likes.slug for the column name and then slug for the value. That's how we can update a specific row with Drizzle. Lastly, we just want to invoke the returning method so we can get the updated result back and then .get method to invoke the actual query.

[9:54] Cool. Now, since we have the updated constant, we can return a new response just like before. JSON.stringify, pass the updated constant, open an object, set the status to 200, define the headers and define the Content-Type header to application/json. We already have the fetch implemented for the POST method.

[10:19] Let's open Inspect Element again and go to the Network Tab. We're going to scroll down and click the like button. The like button made a request. It's a POST request to the api/like/slug, which resulted in a 200 OK. The response contained the entry from the database where the slug is the article slug. The likes this time is just one.

[10:44] If we do it again, we're going to see the second call. The likes is set to two and also the label updates. If we do it again, we'll see the likes being set to three and of course, the label updates.

[10:58] Now, if we refresh the page, the request that happens after page load, the refresh likes function, we can see that it doesn't resolve in a 404 not found. Now, since we have an entry in the database for this article, it finds it, and it returns 200 OK with the same data as the POST method. That's how we can define endpoints in Astro.

[11:14] Let's do a quick recap now. Just like we did back in lesson 13, to define an endpoint, we needed to create a new file in the pages' directory. That file exported two methods named GET and POST for both of our API handlers. We also needed to let Astro know that we want this file to be treated as SSR. We exported the pre-render flag set to .

[11:36] First, we obtained the dynamic slug value from the context and then using our database instance, we read, inserted, and updated the entries from our database. To return the results back to the client, we returned an instance of the response class and parsed the results and the options.