Build a simple serverless CRUD App with Neon, PostgREST and REST API
Tags: database, postgres, neon, postgrest
Published: 2024-03-17 19:08:54

PostgreSQL is an open-source object–relational database system that is robust, reliable, and highly performant.

In this tutorial, we’ll discuss [Neon](https://neon.tech/?ref=hm), an open-source serverless Postgres with a generous free tier. Neon takes care of all developer Postgres needs so they can focus on creating their applications without worrying about the underlying infrastructure. It also saves developers time in scaling and sizing the database. With Neon, all you need is the connection string to the database. 

Neon separates storage and computing, providing the following benefits.

- **You only pay for what you use**: If there is a lot of data to be stored that does not require much processing, there won’t be any need to pay for expensive computing resources, as opposed to cases in which storage and computing were tightly coupled.
- **Flexible scaling**: Storage and compute resources can be scaled independently. When more computing or storage resources are needed, they can be added; if fewer resources are required, they can be scaled down.
- **Better utilization of resources:** Scaling storage and computing independently helps better utilize resources. For example, if a lot of data that needs little to no processing is to be stored, we can choose a less expensive storage option and save money.

In this tutorial, you will build a simple CRUD application of students' information using PostgREST and Neon.

## Getting started with Neon

To sign up on Neon, navigate to Neon’s [website](https://neon.tech/?ref=hm) and click the **Signup** button. You’ll have the option to sign up using Email, GitHub, Google, or Hasura accounts.


![Neon signup](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1701034574782_image.png)


After signing up, you’ll be redirected to the Neon **Getting Started** page, where you‘ll fill out the **Project name** and **Database name**. For this tutorial, let’s name the Project and Database **PostgRest-Neon** and **postgrestNeon**, respectively. There is also the option of choosing the Region closest to your application. Then, click on the **Create project** to create a free tier project.


![Getting started with Neon](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1699990838383_image.png)


After creating the project, you’ll be directed to the Neon console home page.


![Home page](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1699992314542_image.png)



## What is PostgREST?

[PostgREST](https://postgrest.org/en/stable/index.html?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) is a web server that turns a PostgreSQL database into a RESTful API. Most web applications are interactions between the browser and the database. However, that rarely happens *directly*: often, there are intermediaries between the browser and the database. These could be the API gateway, load balancer, etc. The question is whether the browser can interact directly with the database. This is what PostgREST helps to achieve. 

## Installing PostgREST

PostgREST compiled versions are available for Linux and Windows. To install one for a particular distribution, visit the [latest release](https://github.com/PostgREST/postgrest/releases/tag/v11.2.2?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) of PostgREST to download the installation file. 

If your platform isn’t part of the pre-built versions, visit the [b](https://postgrest.org/en/stable/explanations/install.html#build-source)[uild from source](https://postgrest.org/en/stable/explanations/install.html#build-source) page to build it yourself.

After installation, you should have a file named `postgrest` or `postgrest.exe` on Windows. If you built it yourself, you should have a folder named `postgrest`.

PostgREST requires [libpq](https://www.postgresql.org/docs/9.5/libpq.html?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity), a PostgreSQL C library, to be installed on your system to function correctly. Run the following commands in your terminal, depending on your operating system.


- Ubuntu or Debian

```powershell
sudo apt-get install libpq-dev
```

- Fedora, CentOS, or Red Hat

```powershell
sudo yum install postgresql-libs
```

- MacOS

For MacOS, you will need to install [homebrew](https://brew.sh/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) if you do not already have it installed. To install Homebrew, paste the command below into your terminal.

```powershell
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)”
```
Next, run the following command in your terminal to install postgresql.

```powershell
brew install postgresql
```


- Windows

All the DLL files needed to run PostgREST are available in the Windows installation of PostgreSQL, so there is no need to install the library.

Verify the PostgREST installation by running this command in your terminal.

```powershell
postgrest -h
```
If the installation was successful, you should see the available command options:


![PostgREST successful installation](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700171270180_image.png)


 

## Create data in Neon for API

To connect to your Neon database, copy the psql `connectionString` in your dashboard.

![Neon project dashboard](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700253735497_image.png)


Then, run the psql command in your terminal. The command connects to your Neon database using psql.

```powershell
psql 'postgresql://oisiwekpeni:************@ep-fancy-bird-83727579.eu-central-1.aws.neon.tech/neonCloudfront?sslmode=require'
```

After running it, it will open up the database console, where you can execute SQL statements against your Neon database.

The console looks like this:

![postgrest console](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700507486721_image.png)


Next, create a schema for the database objects that will be exposed in your API. To do this, enter the command below in your database console.

```powershell
create schema api
```

The schema is created in your postgrestNeon database. To verify this, click the **Tables** menu in your Neon project.

![Creating a schema](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700254265057_image.png)


The API will have one endpoint `/student`, which will come from the table. In this case, it will be a list of students. To create a `Students` table, run the command below in the postgrestNeon database console you started.

```powershell
create table api.students (
id serial primary key,
firstName text not null,
lastName text not null
);
```    

Then, add some data to the table. To do so, run the command below in the postgrestNeon database console.

```powershell
insert into api.students (firstName, lastName) values
('Obinna', 'Hilary'), ('Justin', 'Bieber');
```

Click on the **Tables** menu and verify that the table and the data were successfully created and inserted, respectively.


![Database table](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700255962233_image.png)

## Run PostgREST

You can run PostgREST using a configuration file to specify how it connects to the database. Create a configuration file, `students.conf` with the content below.

```powershell
db-uri = "<Your Neon database connection string>"
db-schemas = "api"
db-anon-role="<your database role>"
```

For the `db-anon-role`, you can set the default role created when the Neon project was created or create a new role. The reason for specifying the role is to allow anonymous requests to the database. PostgREST will switch to the role when a request comes in to make the database queries.

There is no predefined location of the `student.conf` config file, but we recommend creating it on your desktop for easy access. To run PostgREST with the config file, run the command below in your terminal.

```powershell
postgrest /path/to/students.conf
```

You should see a series of messages in the terminal showing a successful connection to the database.


![Running PostgREST](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700308059405_image.png)


You are now ready to make API requests against your Neon database. There are many tools to make API requests, but we will use [Postman](https://www.postman.com/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) for this tutorial.

First, make a GET request to the database. It returns the list of students in the database.


![GET request](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700309278268_image.png)


You can also make a POST request to the database with a student's information, and the new record is saved.


![Creating a student data 1](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700309468609_image.png)



![Creating a student data 2](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700309537810_image.png)


You can also GET a particular student information in the database by specifying the `id` as a query string parameter. 


![Getting a particular student's data](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700310702530_image.png)


You can use the address bar in Postman to specify the query parameters by adding `?id=eq.3` to the URL or entering it in the Params tab.


![Specifying the query string parameters](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1701716732180_image.png)


Additionally, you can update a student's information with a PATCH request to the database by passing the `id` as a query string parameter.


![Update student data 1](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700311011728_image.png)


The database will update accordingly.


![Update a student data 2](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700311119260_image.png)


To delete a student's data, make a DELETE request to the database by passing the `id` as a query string parameter.


![Delete a student data 1](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700311286580_image.png)


The data is successfully deleted from the database.


![Delete a student data 2](https://paper-attachments.dropboxusercontent.com/s_7D022530D27B0FC560A1ABF2D45611F1659A038270D3E298E29189ADC6D7D57D_1700311377534_image.png)



## Conclusion

This tutorial explores Neon, a cloud-based serverless PostgreSQL that separates storage and compute. It offers powerful benefits for developers: paying for what you use, scalability, and much more. We also dove into PostgREST, a web server that turns a PostgreSQL database into an API. Finally, we integrated Neon and PostgREST, performing CRUD operations against the Neon database using PostgREST.

Neon serverless makes building applications a breeze, helping developers ship their products faster and focus on meeting customer needs.

## Resources
- [PostgREST](https://postgrest.org/en/stable/index.html?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)
- [Neon](https://neon.tech/?ref=hm)
- [Postman](https://www.postman.com/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)