Build a simple project management app with Neon, PostgREST, and DigitalOcean
Published: 2024-02-27 16:29:41

# Introduction

[Neon](https://neon.tech/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) [s](https://neon.tech/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)[erverless Postgres](https://neon.tech/?ref=hm) stands out in the world of databases. It provides a fully managed service for PostgreSQL with unique features like [autoscaling](https://neon.tech/docs/introduction/autoscaling?ref=hm), [branching](https://neon.tech/docs/introduction/branching?ref=hm), and a great serverless experience. Its architecture makes Neon serverless Postgres different from how users set up Postgres databases by separating storage and compute.

In this guide, we'll leverage the strengths of Neon serverless Postgres to establish a [PostgREST](https://postgrest.org/en/stable/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) 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 with complete create, read, update, and delete (CRUD) functionality. This application will allow users to create new projects, view existing details, update information, and delete projects as needed. 

Ultimately, you’ll have a fully functional PostgREST API powered by Neon serverless Postgres, seamlessly incorporated into your DigitalOcean environment.

# Prerequisites

To fully grasp the concepts presented in this tutorial, you’ll need the following:

- [A Neon account](https://console.neon.tech/sign_in?ref=?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)
- [A DigitalOcean account](https://cloud.digitalocean.com/registrations/new?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)
- A basic understanding of PostgreSQL and REST APIs
- Node.js installed on your local machine

# Create a Neon project

To set up a Neon project, let’s begin by [registering for an account](https://console.neon.tech/sign_in?ref=hm), assuming you don't have one already. With that done, we’ll create a new project by selecting a **Project Name**. We will use (Project Management), **Database name** (`managementdata`), and **Postgres version**. For this demonstration, we’ll opt for 16 as the Postgres version and choose the region closest to where we would like to deploy our app.

![project creation](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1702543084612_Screenshot+2023-12-14+at+09.37.40.png)


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


![Connection string](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700673660173_Screen+Shot+2023-11-21+at+12.20.48+PM.png)

## Project directory

To organize our project files and resources, let's create a new project directory using the following terminal command:

```javaScript
    mkdir neonprojectappfolder
```


## Loading demo data into the database

With the project directory in place, follow these steps to load the demo data into the database.


- To connect to our database, we will use the connection string provided by Neon and run it in the terminal:

```javaScript
    psql 'postgresql://xxx:xxxx@ep-xxx-xxx-01196450.us-east-2.aws.neon.tech/xxx?sslmode=require'
```

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


-  Now that we’re connected to the database, let’s upload our demo data by running the command below in the terminal:

```javaScript
    psql 'postgresql://xxx:xxxx@ep-xxx-xxx-01196450.us-east-2.aws.neon.tech/xxx?sslmode=require < projects.sql
```

The demo data (projects.sql) can be downloaded [here](https://gist.github.com/femakin/f1ecfa87c607744a65b03a6a1c11271a?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity).


> The demo data must be downloaded in our project directory before running the command above.

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


![Loading Data RESPONSE](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700673807419_Screenshot+2023-11-21+at+16.26.57.png)


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

Next, we verify the demo data is present using the following steps:



- First, connect to the database again by running the command below:

```javaScript
    psql 'postgresql://xxx:xxxx@ep-xxx-xxx-01196450.us-east-2.aws.neon.tech/xxx?sslmode=require'
```

(Just a note that we’re connecting to the database again because we exited the database when we loaded the demo data into the database.)


- Now, let us run the command below in our terminal:

```javaScript
    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 the one in the image below.


![Data response](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700673976031_Screenshot+2023-11-21+at+16.41.32.png)


As a result, our demo data has been successfully uploaded to our database.

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


![PostgREST confirmation](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700674034456_Screenshot+2023-11-22+at+10.19.30.png)


## Granting CRUD access to a database user

Now, we can connect to the database using the connection string above.

(We’re connecting again because checking the PostgREST installation disconnected us from the database.)

After a successful connection, execute the command below:

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

The command grants `femakin` user permissions for `SELECT`, `INSERT`, `UPDATE`, and `DELETE` operations (CRUD) on the projects table in the public schema.

## PostgREST connection setup

The next step is to establish a PostgREST connection. To do this, exit the database terminal by typing `\q` and then enter the following command: `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.

```javaScript
    db-uri = psql 'postgresql://xxx:xxxx@ep-xxx-xxx-01196450.us-east-2.aws.neon.tech/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.


![PostgREST setup](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700674200528_Screenshot+2023-11-22+at+12.30.02.png)

## Testing the API routes

Now, let us employ a tool such as Postman 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.


![API response](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700674269619_Screenshot+2023-11-22+at+12.46.53.png)


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


# Bootstrapping VueJS application

The quickest approach to get started with our VueJS application is to clone this [Git](https://github.com/femakin/Neo-Demo-PostgREST-DigitalOcean-App?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)[H](https://github.com/femakin/Neo-Demo-PostgREST-DigitalOcean-App?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)[ub repository](https://github.com/femakin/Neo-Demo-PostgREST-DigitalOcean-App?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)[:](https://github.com/femakin/Neo-Demo-PostgREST-DigitalOcean-App?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)

```javaScript
    git clone https://github.com/femakin/Neo-Demo-PostgREST-DigitalOcean-App
```

Follow the instructions in the README file: Run `npm install` to install dependencies and `npm run dev` to start the application. 

The application will appear like this in the browser:


![Home screen](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1701553465337_Screenshot+2023-12-02+at+22.43.52.png)


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

Thus far, we’ve 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, [log into](https://cloud.digitalocean.com/login?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) the DigitalOcean dashboard and create a Droplet by following this [guide](https://docs.digitalocean.com/products/droplets/how-to/create/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity).

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

Now, let’s access our droplet. First, copy the created droplet's `IP` address, as shown in the image below, and then use it to `SSH` into the droplet.


> Note: Keep this IP address handy; we’ll need it for future steps.


![Droplet IP](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700726778282_Screenshot+2023-11-23+at+09.01.13.png)


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


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

```javaScript
    ssh root@copied_droplet_IP
```

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


![SSH connection](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700728139033_Screenshot+2023-11-23+at+09.23.23.png)


Next, we will update the `apt cache` to get the latest packages by running the below command:

```javaScript
    sudo apt update
```


![Cache update](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700730715143_Screenshot+2023-11-23+at+10.06.57.png)


A runtime library, an essential component of the PostgreSQL database system, must be installed. This library is a connection and communication interface for PostgreSQL database servers.

Let’s run the following command in the terminal to install the PostgreSQL runtime library, `libpq5`:


```javaScript
    sudo apt install libpq5
```

This command will ensure that the necessary runtime components for PostgreSQL are installed on the system.


![libpq5](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700731627637_Screenshot+2023-11-23+at+10.25.24.png)


Now, we’ll install PostgREST from the release page available [here](https://github.com/PostgREST/postgrest/releases/tag/v11.2.0?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity). Choose a version compatible with your operating system; for example, we will install `postgrest-v11.2.0-linux-static-x64.tar.xz`.

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


![Downloading PostgREST](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700735647372_Screenshot+2023-11-23+at+11.25.55.png)


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

```javaScript
    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:

```javaScript
    ./postgrest -e
```

We should expect a response similar to the image below.


![Starting PostgREST](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700741732848_Screenshot+2023-11-23+at+13.11.27.png)

## Configuring PostgREST on Droplet

Let us configure PostgREST by following the steps below.


-  Move PostgREST to `usr/bin` by executing the following command:

```javaScript
    sudo mv postgrest /usr/bin
```


-  Navigate to `/usr/bin` using the command

```javaScript
    cd /usr/bin
```

-  Confirm that everything is in order by running

```javaScript
    ./postgrest -e
```


-  Now, create a directory using the command

```javaScript
    mkdir projectsmanagement
```

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

```javaScript
    cat projects.config
```

We previously created the config file in the section above. We’ll see a configuration similar to the one below:

```javaScript
    db-uri = psql 'postgresql://xxx:xxxx@ep-xxx-xxx-01196450.us-east-2.aws.neon.tech/xxx?sslmode=require'
    db-schema = "public"
    db-anon-role = "femakin"
```

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

```javaScript
    vi projects.config
```

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


![projects.config](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1700746783469_Screenshot+2023-11-23+at+14.24.12.png)


Now, we can connect to our database using `postgrest postgrest.config`.

You should now see a response stating that the connection is successful, as shown in the image below:

![Connecting to database](https://hackmd.io/_uploads/HkliTCnN6.png)


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


![Response](https://hackmd.io/_uploads/HyqLk1aEp.png)

## 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?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity).


![systemd](https://hackmd.io/_uploads/SkToa1pVp.png)


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:

```javaScript
    [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 

```

Here, we’ll reload, start, and check the status of the `systemd` using the command below.

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


![Reload systemd](https://hackmd.io/_uploads/BkANQe64T.png)


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


![curl response](https://hackmd.io/_uploads/ByiZNgaET.png)


## Uploading the Vue app into Droplet


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


![App build](https://hackmd.io/_uploads/Bk5SlM0Ea.png)


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

```javaScript
    scp -r dist root@copied_droplet_IP:/usr/bin/vueapp 
```

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

```javaScript
    sudo apt install nginx
```


![Installing a web server](https://hackmd.io/_uploads/ByGdOpAVp.png)


Next is to create a virtual host using:

```javaScript
    sudo vi /etc/nginx/sites-available/copied_droplet_IP
```

Paste the code below and save.


![Virtual host](https://hackmd.io/_uploads/BJw79pCNp.png)


We can now create a link using.

```javaScript
    sudo ln -s /etc/nginx/sites-available/copied_droplet_IP  /etc/nginx/sites-enabled/
```

Then reload `nginx` using

```javaScript
    sudo systemctl reload nginx
```

## Testing deployment

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


![Rendered app](https://hackmd.io/_uploads/rkoA36C46.png)


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


![API server error](https://hackmd.io/_uploads/r1eAa6AVT.png)


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.](https://postgrest.org/en/v10.2/admin.html?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)


We can now edit our `nginx` configuration using

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

Then, update the configuration with the command below.


```javaScript
     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/;
        }
```


![Update nginx configuration](https://paper-attachments.dropboxusercontent.com/s_F4CC8B3EBBD91AA6B62F54D269315BB9517CA68DB811ADA96169A34E77BD63F5_1701433530087_Screen+Shot+2023-12-01+at+1.24.38+PM.png)


Then reload `nginx` using:

```javaScript
    sudo systemctl reload nginx
```

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


![Complete Page](https://hackmd.io/_uploads/HknMQACEp.png)



Congratulations! You successfully created a mini-project management application by seamlessly integrating PostgREST and DigitalOcean, backed by Neon serverless Postgres.

Here are a few recommendations to take it further. 

- 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

We covered a lot in this tutorial. Let’s recap! You accomplished the following:

- Leveraged Neon serverless Postgres to establish a PostgREST API on DigitalOcean.
- Transformed a PostgreSQL database into a functional RESTful API using PostgREST.
- Created a mini-project management application as a practical illustration using VueJS.
- Demonstrated seamless integration between Neon serverless Postgres, PostgREST, and DigitalOcean.

# References

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