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:
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:
Top comments (15)
Nice article
Very detailed!
Amazing article! 🔥
Nice article
I love this article
THANK YOU
Amazing and detailed article 💯
Nice read
Nice read
Love it
Nicely done
Wow a nice article