Create a Serverless Postgres SQL Database with Ampt

[00:00] Amped has just launched AmpedSQL, which is a fully managed serverless Postgres database. Let's give it a shot. To get started, I'm going to create a new directory, which I'm going to call AmpedSQL. Then I'm going to cd into it. And next up, I'm going to run Amped.

[00:16] Because there's no packet.json in this directory, Amped is going to ask us whether we would like to create a new app. And this is exactly what I want, so I'm going to select yes. Next up, we need to choose a template. So I'm going to go with TypeScript Starter and hit enter. Next, I need to provide a name for the app, so I'm going to go with AmpedSQL.

[00:35] And this is going to take a second and generate the entire project for us. OK, now we can see that our personal sandbox is up and running, so I'm going to open Visual Studio Code And we can see a brand new AMPed project generated for us. To see what we need to do next, let's take a look at the announcement blog post. If we scroll down, we are going to see that we are going to need two modules. So we are going to install AMPed API and AMPed SQL.

[00:59] AMPed API is a module that allows us to create serverless APIs, and AMPedSQL is a module that allows us to use Postgres SQL in our AMPed projects. With that being said, let me go back to our AMPed terminal, and I'm going to install AMPed API and AMPed sql. This is going to take a second and install those packages from npm. Once this is done, let me go back to our code and I'm going to paste in some boilerplate. So those few lines are going to create a new serverless API with two API routes, so we are going to be able to get all posts and we also want to be able to create a post.

[01:38] Let's start with getting all posts. So I'm going to paste in some SQL. What we can see is that we are going to use the SQL module to run an SQL query. So we are going to select everything from the posts table. But wait, we don't have a post table.

[01:54] First of all, we need to create one. If we take another look at the blog post, we can see down here that before you can insert and query data, you need to create your database schema. So in essence, we need to create a migration which has up and down direction. So our first migration in the up direction is going to create a new table, which we are going to call post with three columns, ID, Outdoor, and Content. So let me copy that and I'm going to go back to our project and create a new directory which I'm going to call Migrations and I'm going to create a first migration to create posts table.

[02:30] And here I'm going to paste in the migration. So again, we are going to create a new table of posts with three columns. Before we can run this migration, we need to modify our package.json. So in our package.json, we need to add a new script and this script needs to be called amp-migrate, which is going to run amp-sql-migrate. With all of that in place, we can go back to our terminal and execute run migrate.

[02:56] And now let's go back to our code. I made a small typo. I meant to select all posts on a GET request, so let me fix that real quick. Now we can test if our database actually works even though we don't have any posts yet. To do that I'm going to go to AMP dashboard and I'm going to copy the address of my development sandbox.

[03:15] And let's test it. I happen to use Insomnia, but you can use any REST client or honestly whatever in order to send those requests. So I'm going to paste that here, slash API slash posts. And we can see that everything works fine. We were able to select all items from the posts table, which of course is empty right now.

[03:36] So what we need to do is to add some more posts. To do that, let's implement this method right here. First of all, we need to change the get to post. And secondly, I'm going to paste in some code. So first of all we are going to grab the content and the author from the request body and secondly we are going to use this piece of SQL in order to insert a new post into our posts table.

[04:00] Each post needs to contain a content and the author and we can also see that in our migration over here. Both author and content cannot be null. And once the data is inserted into the table, we are going to return our freshly created post. After we hit save, we can see in the terminal that AMP has successfully updated our code in a few seconds. So let me go back to Insomnia.

[04:25] I'm going to change the request to post and I'm going to add a JSON body which is going to contain an author. That will be me. And content, let me just do hello, ahead. And if I hit Send, we can see that our post has been added to our SQL database. Let me delete the request body and send a GET request once more in order to verify whether I can still get all of the posts.

[04:49] And here it is. So to sum up, in this quick lesson we have learned how to use the AMPed SQL module in order to create a fully managed serverless Postgres database without setting up and managing servers. In addition to that, we have also learned how to create a serverless API in order to add data to our POST table and also to get all items from our SQL table. And notice how we didn't have to create or manage any structure in order to do that.