Build a simple contact manager with PostgREST, Railway, and Postgres
Published: 2024-02-28 10:25:55

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](https://postgrest.org/en/stable/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) – 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](https://postgrest.org/en/stable/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) 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](https://neon.tech/docs/introduction?ref=hm) 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](https://railway.app/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) 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](https://www.postgresql.org/docs/current/sql.html?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) commands
- JavaScript and React fundamentals
- Basic terminal commands

## Set up Neon

To get started, we’ll need to [create an account](https://console.neon.tech/sign_in) with Neon. After creating an account, choose a project and a database name, then click the **Create Project** button.

![Create Neon project](https://paper-attachments.dropboxusercontent.com/s_9A181B5474D5142700D8E2D7CBC2F4C29FF949AD505684EAD5E6A90735F1FA2D_1701396497257_Screen+Shot+2023-12-01+at+3.05.31+AM.png)


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:

![Neon dashboard console](https://paper-attachments.dropboxusercontent.com/s_9A181B5474D5142700D8E2D7CBC2F4C29FF949AD505684EAD5E6A90735F1FA2D_1701396922186_Screen+Shot+2023-12-01+at+3.15.05+AM.png)


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

1. Click on the SQL Editor tab
2. Clear all text and insert the following query:
    
    ```SQL
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.


3. Click on the **Run** button.

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

![Neon Postgres database](https://paper-attachments.dropboxusercontent.com/s_9A181B5474D5142700D8E2D7CBC2F4C29FF949AD505684EAD5E6A90735F1FA2D_1701397758252_Screen+Shot+2023-12-01+at+3.28.03+AM.png)



## Set up PostgREST

To use PostgREST, you have to install it. [Homebrew](https://brew.sh/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) 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:

```BASH
    # 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:

![Neon connection string](https://paper-attachments.dropboxusercontent.com/s_9A181B5474D5142700D8E2D7CBC2F4C29FF949AD505684EAD5E6A90735F1FA2D_1701398871186_Screen+Shot+2023-12-01+at+3.47.00+AM.png)


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:


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

![PostgREST server host](https://paper-attachments.dropboxusercontent.com/s_9A181B5474D5142700D8E2D7CBC2F4C29FF949AD505684EAD5E6A90735F1FA2D_1701409875249_Screen+Shot+2023-12-01+at+6.50.40+AM.png)


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](https://github.com/Dalu46/neon-db?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity). 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:


![Local demo application](https://paper-attachments.dropboxusercontent.com/s_9A181B5474D5142700D8E2D7CBC2F4C29FF949AD505684EAD5E6A90735F1FA2D_1701411217202_Screen+Shot+2023-12-01+at+7.13.30+AM.png)


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](https://railway.app/login?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) (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. 

![New Railway project](https://paper-attachments.dropboxusercontent.com/s_9A181B5474D5142700D8E2D7CBC2F4C29FF949AD505684EAD5E6A90735F1FA2D_1701414408588_Screen+Shot+2023-12-01+at+8.05.41+AM.png)


Follow the steps below to deploy your application: 

1. Click on **New Project**
2. Click on **Deploy from GitHub repo**
3. Click on **Configure GitHub app**
4. 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.

![Connect to GitHub repo](https://paper-attachments.dropboxusercontent.com/s_9A181B5474D5142700D8E2D7CBC2F4C29FF949AD505684EAD5E6A90735F1FA2D_1701415386496_Screen+Shot+2023-12-01+at+8.06.02+AM.png)


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

1. Click on the project.
2. Click on the **Settings** tab.
3. 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. 
![](https://paper-attachments.dropboxusercontent.com/s_9A181B5474D5142700D8E2D7CBC2F4C29FF949AD505684EAD5E6A90735F1FA2D_1702062141069_labelled+railway+guide.png)

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


![](https://paper-attachments.dropboxusercontent.com/s_9A181B5474D5142700D8E2D7CBC2F4C29FF949AD505684EAD5E6A90735F1FA2D_1702062307308_Screen+Shot+2023-12-08+at+8.04.34+PM.png)


Now, your production URL should be able to render, add, and remove contacts from your Neon Postgres database.
    
![Production demo application](https://paper-attachments.dropboxusercontent.com/s_9A181B5474D5142700D8E2D7CBC2F4C29FF949AD505684EAD5E6A90735F1FA2D_1701416969343_Screen+Shot+2023-12-01+at+12.34.33+AM.png)


Here’s a link to the live video of how the application works:
{% embed https://app.opentape.io/share/b94c8351-b0ac-4c14-bc83-b8d9125e4ac2 %}

## 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:
- [PostgREST Documentation](https://postgrest.org/en/stable/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)
- [Railway Documentation](https://docs.railway.app/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)
- [Neon Documentation](https://neon.tech/docs/introduction?ref=hm)