Build a simple serverless CRUD App with Neon, PostgREST and REST API

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

In this tutorial, we’ll discuss Neon, 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 and click the Signup button. You’ll have the option to sign up using Email, GitHub, Google, or Hasura accounts.

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.

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

What is PostgREST?

PostgREST 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 of PostgREST to download the installation file.

If your platform isn’t part of the pre-built versions, visit the build from 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, 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.

sudo apt-get install libpq-dev

• Fedora, CentOS, or Red Hat

sudo yum install postgresql-libs

For MacOS, you will need to install homebrew if you do not already have it installed. To install Homebrew, paste the command below into your terminal.

/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.

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.

postgrest -h

If the installation was successful, you should see the available command options:

Create data in Neon for API

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

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

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:

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.

create schema api

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

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.

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.

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.

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.

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.

postgrest /path/to/students.conf

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

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 for this tutorial.

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

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

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

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.

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

The database will update accordingly.

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

The data is successfully deleted from the database.

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