DEV Community

Cover image for Build a simple project management app with Neon, PostgREST, and DigitalOcean
Hackmamba profile image Femi Akinyemi
Femi Akinyemi for Hackmamba

Posted on

Build a simple project management app with Neon, PostgREST, and DigitalOcean

Introduction

Neon serverless Postgres stands out in the world of databases. It provides a fully managed service for PostgreSQL with unique features like autoscaling, branching, 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 API on DigitalOcean. 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:

Create a Neon project

To set up a Neon project, let’s begin by registering for an account, 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

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

Connection string

Project directory

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

    mkdir neonprojectappfolder
Enter fullscreen mode Exit fullscreen mode

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: 
    psql 'postgresql://xxx:xxxx@ep-xxx-xxx-01196450.us-east-2.aws.neon.tech/xxx?sslmode=require'
Enter fullscreen mode Exit fullscreen mode

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: 
    psql 'postgresql://xxx:xxxx@ep-xxx-xxx-01196450.us-east-2.aws.neon.tech/xxx?sslmode=require < projects.sql
Enter fullscreen mode Exit fullscreen mode

The demo data (projects.sql) can be downloaded here.

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

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: 
    psql 'postgresql://xxx:xxxx@ep-xxx-xxx-01196450.us-east-2.aws.neon.tech/xxx?sslmode=require'
Enter fullscreen mode Exit fullscreen mode

(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: 
    select * FROM projects LIMIT 1
Enter fullscreen mode Exit fullscreen mode

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

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.

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

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:

    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.projects TO femakin
Enter fullscreen mode Exit fullscreen mode

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.

    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"
Enter fullscreen mode Exit fullscreen mode

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

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

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 GitHub repository:

    git clone https://github.com/femakin/Neo-Demo-PostgREST-DigitalOcean-App
Enter fullscreen mode Exit fullscreen mode

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

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 the DigitalOcean dashboard and create a Droplet by following this guide.

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

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:

    ssh root@copied_droplet_IP
Enter fullscreen mode Exit fullscreen mode

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

SSH connection

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

    sudo apt update
Enter fullscreen mode Exit fullscreen mode

Cache update

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:

    sudo apt install libpq5
Enter fullscreen mode Exit fullscreen mode

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

libpq5

Now, we’ll install PostgREST from the release page available here. Choose a version compatible with your operating system; for example, we will install postgrest-v11.2.0-linux-static-x64.tar.xz.

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

Downloading PostgREST

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
Enter fullscreen mode Exit fullscreen mode

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

    ./postgrest -e
Enter fullscreen mode Exit fullscreen mode

We should expect a response similar to the image below.

Starting PostgREST

Configuring PostgREST on Droplet

Let us configure PostgREST by following the steps below.

  • Move PostgREST to usr/bin by executing the following command: 
    sudo mv postgrest /usr/bin
Enter fullscreen mode Exit fullscreen mode
  • Navigate to /usr/bin using the command 
    cd /usr/bin
Enter fullscreen mode Exit fullscreen mode
  • Confirm that everything is in order by running 
    ./postgrest -e
Enter fullscreen mode Exit fullscreen mode
  • Now, create a directory using the command 
    mkdir projectsmanagement
Enter fullscreen mode Exit fullscreen mode

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

    cat projects.config
Enter fullscreen mode Exit fullscreen mode

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

    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"
Enter fullscreen mode Exit fullscreen mode

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

    vi projects.config
Enter fullscreen mode Exit fullscreen mode

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

projects.config

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

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

Creating systemd

Now, let's create a system service for a PostgREST. We need to go back to PostgREST documentation here.

systemd

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
Enter fullscreen mode Exit fullscreen mode

Here, we’ll 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
Enter fullscreen mode Exit fullscreen mode

Reload systemd

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

curl response

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

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

    scp -r dist root@copied_droplet_IP:/usr/bin/vueapp
Enter fullscreen mode Exit fullscreen mode

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

    sudo apt install nginx
Enter fullscreen mode Exit fullscreen mode

Installing a web server

Next is to create a virtual host using:

    sudo vi /etc/nginx/sites-available/copied_droplet_IP
Enter fullscreen mode Exit fullscreen mode

Paste the code below and save.

Virtual host

We can now create a link using.

    sudo ln -s /etc/nginx/sites-available/copied_droplet_IP  /etc/nginx/sites-enabled/
Enter fullscreen mode Exit fullscreen mode

Then reload nginx using

    sudo systemctl reload nginx
Enter fullscreen mode Exit fullscreen mode

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

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

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
Enter fullscreen mode Exit fullscreen mode

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/;
        }
Enter fullscreen mode Exit fullscreen mode

Update nginx configuration

Then reload nginx using:

    sudo systemctl reload nginx
Enter fullscreen mode Exit fullscreen mode

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

Complete Page

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

Top comments (0)

Read next

jefferyhus profile image

Implementing your own Proof Of Authentication. (Part 1)

El Housseine Jaafari -

ega_janiva_e3387d6f7940ce profile image

Birthday candle

Ega Janiva -

praveenrajamani profile image

Say goodbye to tedious data entry! The future of OCR is here, and it’s smarter than ever!

Praveen -

tobysolutions profile image

I built a Web3 URL shortener in Next.js!

Tobiloba Adedeji -