PostgreSQL is powerful in the world of databases and is known for its robustness, extensibility, and versatility. As a relational database management system, it is the go-to choice for developers seeking data integrity, SQL support, and scalability. However, harnessing the full potential of PostgreSQL sometimes involves creating custom APIs, a task that can quickly become difficult.

But what if there were a way to seamlessly traverse this hassle, eliminating the need for manual API creation while harnessing the power of your PostgreSQL database? Meet PostgREST – the game-changer that liberates developers from the intricacies of building custom APIs. PostgREST steps in as your ally, simplifying the integration of your PostgreSQL database with the world of RESTful APIs.

This comprehensive guide will explore the seamless integration of PostgREST, Neon Postgres, and Railway to create a robust application. You will learn how PostgREST transforms your Postgres database into a RESTful API, how Neon Postgres provides serverless database management, and how Railway simplifies deployment. To illustrate this, we will build a simple contact web application that renders, adds, and deletes contact data from your Neon database.

This guide will take you through step-by-step instructions to set up Neon, configure PostgREST, and deploy a demo Next.js application on Railway. By the end, you'll have a fully functional application with live interaction between Neon Postgres, PostgREST, and Railway, showcasing the power of these tools in harmony.

What is PostgREST?
PostgREST is a tool that generates a RESTful API from your Postgres database. PostgREST automatically translates database schema, tables, and operations into a RESTful API, reducing the need for custom API development. To modify your Postgres database, you can send HTTP requests to the provided API endpoints.

What is Neon Postgres?
Neon is a serverless Postgres database that offers features such as branching, bottomless storage, and more. Neon also provides APIs and an intuitive dashboard console for managing your Postgres database.

What is Railway?
Railway is a deployment platform for running and managing your application infrastructure. Railway provides features such as an observability graph, out-of-the-box templates, integrations to popular tools, instant deployment, interactive CLI, cloud hosting, and more.

Prerequisites

To follow along fully, you‘ll need some knowledge on the following:

PostgreSQL commands
JavaScript and React fundamentals
Basic terminal commands

Set up Neon

To get started, we’ll need to create an account with Neon. After creating an account, choose a project and a database name, then click the Create Project button.

Next, you’ll see a modal containing your database connection string (in our case ”postgresql://lawrencefranklin100:7hzVnZSiG9yC@ep-old-firefly-95393312.us-west-2.aws.neon.tech/neondb?sslmode=require” ), which you will connect to PostgREST later. This string is always available on the dashboard console, so click the I’ll do this later button. Now, you should see your Neon dashboard, which should look like this:

Follow the steps below to create a table and add sample data to the database:

Click on the SQL Editor tab

Clear all text and insert the following query:

CREATE TABLE contacts (id SERIAL PRIMARY KEY, name TEXT NOT NULL, phone TEXT);
INSERT INTO contacts (name, phone) VALUES ('John Doe', '+23344874'), ('Alice Bob', '+33349847');```

These SQL commands create a table named contacts in the Neon database with three columns— id, name, and phone—and then insert two records into that table.

After running these SQL commands, you'll have a table named contacts populated with two records for John Doe and Alice Bob. The id column will be automatically populated with unique serial values due to its SERIAL type.

Click on the Run button.

Now, navigate to the Tables tab to view the sample added data:

Set up PostgREST

To use PostgREST, you have to install it. Homebrew is a simple way to install PostgREST. Type the following command into your terminal to install PostgREST:

brew install postgrest

Once the installation is completed successfully, you must connect your Neon Postgres database. To do this, create a postgrest.conf file in any folder of your choice (e.g., documents folder) and add the following content:

    # postgrest.conf

    # The standard connection URI format, documented at
    # https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
    db-uri = ""

    # The database role to use when no client authentication is provided.
    # Should differ from authenticator
    db-anon-role = ""

    ## The name of which database schema to expose to REST clients
    db-schemas = "public"


    # The secret to verify the JWT for authenticated requests with.
    # Needs to be 32 characters minimum.
    jwt-secret = "reallyreallyreallyreallyverysafe"
    jwt-secret-is-base64 = false

    # Port the postgrest process is listening on for http requests
    server-port = 5432

NOTE: the postgrest.conf file can be created anywhere. However, it is important to remember the path to its location as this file will be used to spin up a local server to listen to HTTP requests from the demo application. Also, you can use any port (server-port) you want.

Go to your Neon console and copy your connection string. Note the structure of the string:

Add the string (wrapped in double quotes) as the db-uri value in your Postgrest.conf file. Also, add the role (as seen in the string structure above) as the db-anon-role value.

For example:

db-uri = "postgres://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname"

    db-anon-role = "alex"

The default port, i.e., server-port is 3000, but since the demo application will be run on port 3000, use port 5432.

Save the postgrest.conf file and run the following command in your terminal to start listening for HTTP requests (make sure to use the correct path where the postgrest.conf file was created e.g., postgrest user/documents/code/postgrest.conf):

postgrest path/to/postgrest.conf

After running the PostgREST command, you should see something like this:

This should spin up a local server on the port that allows you to send HTTP requests to http://localhost:5432/contacts. This endpoint represents your Neon Postgres database table (contacts). A GET request to this endpoint returns the sample data from your Neon Postgres database. To test this, send a GET request using Postman, Curl, or whatever HTTP request client you prefer.

Set up a demo project

The demo project is a basic Next.js application that renders a list of contacts that can be added and deleted from your Neon Postgres database. The application uses the endpoint provided by PostgREST to modify data in the Neon Postgres database.

To set up the demo project, clone this repo. Once you’ve cloned this repo, create a .env.local file in your project root and add the following environment variable:

NEXT_PUBLIC_URL=https://localhost:5432/contacts

This variable stores the endpoint created by PostgREST. Save the .env.local file and run the following command to preview the application:

npm run dev

This spins up a localhost where you can preview the demo application, usually on port 3000, like this: http://localhost:3000. Open this URL in your browser, and you should see the demo project with two contacts from your Neon Postgres database:

You can add or delete contacts at this stage, which should sync with the data in your Neon Postgres database. Go ahead, test it out!

Create a remote repo on your GitHub and push this project to the remote repo on GitHub. The remote repo will be used to deploy the application on Railway.

Deploy demo project to Railway

To deploy to Railway, you need a Railway account. Create a Railway account (most preferably using your GitHub). After creating an account, you should see your railway dashboard. Type cmd + K or click the New Project button to create a new project.

Follow the steps below to deploy your application:

Click on New Project
Click on Deploy from GitHub repo
Click on Configure GitHub app
Follow the prompts and grant access to your GitHub repo

Once access is granted, your repo will be listed in Railway’s New Project options. Select your repo and deploy.

After deploying, follow the steps below to run the demo application:

Click on the project.
Click on the Settings tab.
Scroll to the Public Networking section and click on the Generate Domain button. This will generate a production URL where you can preview the application.
To access the PostgREST endpoint (running on your localhost server), you must create an environment variable on Railway. Click on the Variables tab, click on the New Variable button, and then add the environment variable just like you have it in your .env.local file.

Now, your production URL should be able to render, add, and remove contacts from your Neon Postgres database.

Here’s a link to the live video of how the application works:

NEON Contact list | Opentape

Lawrence Franklin Chukwudalu - Feb 28th, 8:43am

app.opentape.io

Conclusion

At this stage, you have learned what PostgREST is and how it can connect to a Postgres database such as Neon Postgres to create RESTful API endpoints. You also learned how to build a Next.js application using the provided endpoints and deploy your application to Railway.

Neon is fast and can facilitate easy scaling of your application. In addition to its UI features, such as SQL Editor, with which you can modify your data, it also works nicely with PostgREST and Railway to alter and deploy your application.

Here are a few resources to learn more:

Build a simple contact manager with PostgREST, Railway, and Postgres