How to run PostGREST on DigitalOcean backed by Neon Postgres

# Introduction

Experience the power of Serverless Postgres, a fully managed solution with innovative architecture separating storage and computing, which provides unmatched autoscaling, branching, and extensive storage options.

In this guide, we'll leverage the strengths of [Neon Serverless Postgres](https://neon.tech/) to establish a [PostGREST](https://postgrest.org/en/stable/) API on [DigitalOcean](https://www.digitalocean.com/). With PostGREST, a PostgreSQL database is turned into a RESTful API.

As a practical illustration, we'll walk through creating a mini-project management application. This tangible example will demonstrate the seamless integration between Neon Serverless Postgres, PostGREST, and DigitalOcean. Upon completing this tutorial, we'll have a fully functional PostGREST API powered by Neon Serverless Postgres, seamlessly incorporated into our DigitalOcean environment. It stands ready to cater to our data management and API interaction needs, whether for a project management application or any other web or mobile app, we may develop.

Project demo

The project demo can be found [here](http://159.89.232.18//?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity).

# Prerequisite

To fully grasp the concepts presented in this tutorial, the following are required:

* [A Neon account](https://console.neon.tech/)
* [ A DigitalOcean account](https://cloud.digitalocean.com/registrations/new)
* A basic understanding of PostgreSQL and REST APIs
* Node.js installed on oour local machine

# Create a Neon project

To set up a Neon project, let's begin by [registering for an account](https://console.neon.tech/), assuming we don't have one already. Now, we'll create a new project by selecting a Project Name. We will use (Project Management), Database name (managementdata), and Postgres version. We'll opt for 16 as the Postgres version and choose the region closest to where we'd like to deploy our app.


Upon successfully creating the project, we will get a connection string for connecting to our database.


## Loading Demo Data Into the Database

To load the demo data into the database, we need to first connect to our database by running the connection string in our terminal:

psql 'postgresql://xxx:[e-mail]/xxx?sslmode=require'

The command connects to the database using the provided connection string.

Now that we are connected to the database, let us upload our demo data by running the command below In the terminal:

psql 'postgresql://xxx:[e-mail]/xxx?sslmode=require < projects.sql

The demo data (projects.sql) can be downloaded [here](https://drive.google.com/file/d/1UkCJMlYsSv_cVkK25pRn-TpCw0BBCYr4/view?usp=sharing).

> Note that the demo data must be in our project directory before running the command above.

Upon successfully loading demo data into the database, we would see a response like the image below:


The output `CREATE SEQUENCE`, `CREATE TABLE`, and `INSERT 0 5` Indicates that the sequence was created, the table was created, and 5 rows were inserted into the table, respectively.

Next is to check if our demo data gets into the database by running:

First, we connect to the database by using the connection string.

psql 'postgresql://xxx:[e-mail]/xxx?sslmode=require'

Then we run the command below in our terminal:

select * FROM projects LIMIT 1

The SQL command `SELECT * FROM projects LIMIT 1` retrieves data from the "projects" table.

We should get a response like in the image below.


As a result, our demo data has been successfully uploaded to our database. We will use the data in some sections below later.

# PostgREST Setup

Now that we have set up our database, the next step involves setting up PostgREST. First, we install PostgREST by following this installation [guide](https://postgrest.org/en/stable/explanations/install.html#install).

After a successful installation, we can confirm everything works perfectly by typing `postgrest -e` in the terminal. We should see a response similar to the image below, indicating a successful operation.


## Granting CRUD Access to Database User

Now, we can connect to the database using the connection string above and then grant `SELECT`, `INSERT`, `UPDATE`, and `DELETE` (CRUD) operations on the `projects` table in the `public` schema to the user. In this case, the user is `femakin` in PostgrsSQL by running the command below:

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.projects TO femakin

## PostgREST Connection SetUp

Next is to set up a PostgREST connection. First, we exit our database by typing `\q` and then typing:

`vi projects.config`

The command `vi projects.config` opens the `projects. config` file using the vi text editor.

We then edit the `projects.config` file by pasting the command below.

db-uri = psql 'postgresql://xxx:[e-mail]/xxx?sslmode=require'
db-schema = "public"
db-anon-role = "femakin"

This configuration specifies a PostgreSQL database connection string URI with credentials and connection details. It also defines the default database schema as `public` and the anonymous role as `femakin` for authentication purposes.

To ensure the proper setup of the PostgREST connection, we can run `postgrest projects.config` in the terminal. This command should yield a response similar to the image below.


## Testing the API Routes

Now, we can employ a tool such as Postman or a browser to conduct tests. In the browser, navigate to the URL `localhost:3000/projects`.

The connection is working if we find something like this in the browser.


Otherwise, we must review all the steps and ensure everything is present.

# Bootstrapping VueJS Application

We need to Initiate the setup of our VueJS application by cloning the VueJS repository available here on [GitHub](https://github.com/femakin/Neo-Demo-PostgREST-DigitalOcean-App).

Follow the instructions outlined in the README file to configure the necessary environment variables, execute `npm install` to install dependencies, and launch the application using `npm run dev`.

Now we can start our application using `npm run dev`

We should have a screen similar to the image below:


The code in the `src/App.vue` is a project management app using VueJS. It handles project data, loading status, and user interactions. It fetches project data and lets users view, add, update, and delete projects. The focus is on simplicity for managing projects in a web app.

# Deploying to Digital Ocean

We created our database, set up PostgREST, and got our mini-application running in VueJS. Now, we need to deploy everything to DigitalOcean. To do this, we log into the DigitalOcean dashboard and create a Droplet by following this [guide](https://docs.digitalocean.com/products/droplets/how-to/create/).

DigitalOcean Droplets are Linux-based virtual machines (VMs) that operate on virtualized hardware.

Now, we can log into our droplet by copying the `IP` address of the droplet we created and `SSH.` into the droplet.


Now, let's log into the droplet by using the copied IP and running the command below in the terminal:

ssh root@copied_IP

This will prompt us to enter a passphrase for the key.


Next, we'll update the `apt cache` to get the latest packages by running:

sudo apt update


We must install a runtime library, a crucial part of the PostgreSQL database system. This library is an interface to connect to and communicate with PostgreSQL database servers.

Let us paste the command below in the terminal:

sudo apt install libpq5


Now, let's install PostgREST from the release page available [here](https://github.com/PostgREST/postgrest/releases/tag/v11.2.0). Choose a version that is compatible with our operating system; for example, we will install `postgrest-v11.2.0-linux-static-x64.tar.xz`.

We can paste the command below in the terminal:

wget 'https://github.com/PostgREST/postgrest/releases/download/v11.2.0/postgrest-v11.2.0-linux-static-x64.tar.xz'


Next is to extract the compressed file to obtain the executable. run the command below in the terminal:

root@project-management:~# tar vxf postgrest-v11.2.1-linux-static-x64.tar.xz
postgrest

To ensure everything works, let's run the following command:

./postgrest -e

We should expect a response similar to the image below.


Now, let us move PostgREST to `usr/bin` by executing the following command:

sudo mv postgrest /usr/bin

let us move PostgREST to usr/bin by executing the following command:

sudo mv postgrest /usr/bin

Then we navigate to `/usr/bin` using the command:

cd /usr/bin

Then, confirm that everything is in order by running:

./postgrest -e

Now, we create a directory using the command:

mkdir projectsmanagement

let's return to our local machine within the project directory and copy the config file using:

cat projects.config

We previously created the config file in the section above.

We will see a configuration similar to the one below:

db-uri = 'postgresql://xxx:[e-mail]/xxx?sslmode=require'
db-schema = "public"
db-anon-role = "femakin"
server-host = "localhost"

Then, return to our droplet and create a new config using:

vi projects.config

Paste the copied `projects.config`, and save the file.


Now, we can connect to our database using

`postgrest postgrest.config`

We will see a response stating that

connection successful

as shown in the image below


Now, we can test the API using the command `curl localhost:3000/projects`, and observe a response from our database, like the illustration below.


## Creating systemd

Now, let's create a system service for a PostgREST.

We need to go back to PostgREST documentation [here](https://postgrest.org/en/stable/integrations/systemd.html?highlight=daemonizing).


We can now go back to our droplet and create the system file using the command below:

`vi /etc/systemd/system/postgrest.service` and paste the code below:

[Unit]
Description=REST API for any PostgreSQL database
After=postgresql.service

[Service]
ExecStart=/usr/bin/postgrest /usr/bin/postgrest.config
ExecReload=/bin/kill -SIGUSR1 $MAINPID
User=nobody
Group=nogroup

[Install]
WantedBy=multi-user.target 

Let us reload, start and check the status of the `systemd`

Using the command below.

 sudo systemctl daemon-reload
 sudo systemctl start postgrest
 sudo systemctl status postgrest


Testing the API using `curl localhost:3000/projects` gives us our expected response, as shown below:


## Uploading the Vue App into Droplet

It's time to upload our project management application into the droplet. We will return to our local machine to upload our application into the droplet and run `npm run build` inside the project directory.


Now that we have our production Vue App, we can copy it into the droplet using the command below:

scp -r dist root@copied_IP:/usr/bin/vueapp 


Next is to install a web server to serve the files.

sudo apt install nginx


Next is to create a virtual host using:

sudo vi /etc/nginx/sites-available/Copied_IP

Paste the code below and save.


We can now create a link using.

sudo ln -s /etc/nginx/sites-available/Copied_IP  /etc/nginx/sites-enabled/

Then reload `nginx` using

sudo systemctl reload nginx

## Testing Deployment

We can now go to the browser and paste the copied URL. We should see our project management app on the page.


But, if we look at the console, we will see an error. This error is there because we have not set up the API server.


To take care of this, we must use `nginx` to proxy `/API` to the Postgrest instance running on our droplet, as described in this Postgrest documentation.

We can now edit our `nginx` configuration using:

sudo vi /etc/nginx/sites-available/Copied_IP

Then, update the configuration with the command below.

 location /api/ {
      default_type  application/json;
      proxy_hide_header Content-Location;
      add_header Content-Location  /api/$upstream_http_content_location;
      proxy_set_header  Connection "";
      proxy_http_version 1.1;
      proxy_pass http://localhost:3000/;
    }


Then reload `nginx` using

sudo systemctl reload nginx

We can now return to the browser and paste the copied IP Address. Now, we can see our application without any errors.


Now that we have been able to look at how to successfully run PostGREST on DigitalOcean backed by Neon Postgres. Here are a few recommendations to take it further. Including but not limited to;

- To add a domain to the droplet
- Configure HTTPS
- Add more features to the applications.

The possibilities are endless when it comes to Neon Serverless Postgres.

# Conclusion

In this article, we look at how to:

- Leverage Neon Serverless Postgres to establish a PostGREST API on DigitalOcean.
- Transform a PostgreSQL database into a RESTful API using PostGREST.
- Create a mini-project management application as a practical illustration using VueJS.
- Showcase seamless integration between Neon Serverless Postgres, PostGREST, and DigitalOcean.
- Achieve a fully functional PostGREST API.

# References

[Neon Documentation](https://neon.tech/)
[PostgREST Documentation](https://postgrest.org/en/stable/)
[How to Create a Droplet](https://docs.digitalocean.com/products/droplets/how-to/create/)
[Building a Contacts List with PostgREST and Vue.js](https://www.youtube.com/watch?v=iHtsALtD5-U)