Automatic APIs With PostgREST for PostgreSQL

Storing information in a relational database and sharing it via a RESTful API is a very common scenario these days. Depending on your RDBMS (relational database management system), this may take a lot of development effort. One solution that attempts to simplify the process is the PostgREST web server add-on for PostgreSQL.

If your organization uses the PostgreSQL database, PostgREST quickly adds basic CRUD (create, read, update, delete) functions in an easy-to-use RESTful HTTP interface. But while PostgREST does the API generation well, it may lack other features for more complex projects. 

While PostgREST is a handy utility, it is somewhat limited in scope. A more full-featured API management solution may be needed for many enterprise users. Here’s a look at how PostgREST works, its pros and cons, and what you can do with it.

What Is PostgreSQL?

PostgreSQL is an open-source, enterprise-class RDBMS that supports both SQL for relational querying and non-relational querying via JSON. Also known informally as Postgres, PostgreSQL has been a popular choice for enterprises for decades, thanks to performance that’s in the same class as commercial database systems like Oracle and Microsoft SQL Server.

Postgres features the PL/pgSQL procedural language for complex and programmatic query operations. With PL/pgSQL, database administrators have access to a C-style language that supports functions, procedures, and triggers. 

PL/pgSQL and the support for JSON have made PostgreSQL a popular choice for developers who need more complex data management than standard SQL offers. This has made Postgres a popular choice as the data store for web services, mobile apps, data warehousing, and analytics operations.

What Is PostgREST?

PostgREST is a free web server add-on for PostgreSQL that is available for download from the project’s GitHub page. It’s written in the Haskell language with the Warp HTTP framework. Available for Linux and Windows, PostgREST serves up a fully RESTful API from any Postgres database.

Once you have downloaded PostgREST, most of the setup is complete. PostgREST is simply used to make an API call to the Postgres HTTP interface. With that in mind, here’s a quick tutorial on how you might set up a PostgreSQL schema with data to be served up by the API.

Why is PostgREST important?

PostgREST is significant because it provides an efficient, scalable, and secure way to convert any PostgreSQL database into a RESTful API. This approach streamlines the development process by allowing direct access to a database through a web API, which can be particularly useful for web and mobile applications. It eliminates the need for manual coding of backend logic, as the database schema itself defines the API endpoints, leading to rapid development and easier maintenance.

PostgREST enforces robust security measures and embraces PostgreSQL's powerful features, including its role-based access control and advanced query capabilities. This makes it a valuable tool for developers looking to create high-performance, secure, and scalable web services with minimal overhead.

How To Use PostgREST

To show you how to get started with PostgREST, you’ll want to create an API schema using the psql tool:

CREATE SCHEMA api; 

Then create a table within that schema with data for the API to serve up. In this example, you’ll create a simple table for a list of books with these columns and data types:

CREATE TABLE api.books (

    index SERIAL,

    title TEXT,

    author VARCHAR(255),

    genre TEXT,

);

Note that the index column has the SERIAL type, which means it will create an auto-incremented ID for every row created in the table. To get started, enter a few rows, each with a book title, author, and genre.

PostgREST Security

PostgREST delegates security to the database, using PostgreSQL roles as the RESTful API users. You’ll want to create a role with minimal privileges. In this example, the role is called apiOperator. It will not be able to log in to the server and will only have permissions to the books table via API:

CREATE ROLE apiOperator nologin;

GRANT usage ON SCHEMA api TO apiOperator;

GRANT all ON api.books TO apiOperator;

GRANT usage, SELECT ON SEQUENCE api.books_index_seq TO operator;

This completes a basic configuration on the Postgres side, and you’re now ready to move on to configuring PostgREST to talk to your database server.

PostgREST Configuration

PostgREST uses postgrest.conf, a simple configuration file that contains just a few variables. You’ll need to provide the URI for your Postgres server, along with the schema containing your data to be retrieved by API calls and the role with which you’ll make a connection.

For this example, we’ll assume you’re running PostgREST on the same server as your PostgreSQL database (localhost). As outlined above, our example schema is api, and the name of the role is apiOperator. Place these parameters in the config file like this:

db-uri = "localhost"

db-schema = "api"

db-anon-role = "apiOperator"

Once you have the configuration file saved, you’re ready to make your first API call with the help of PostgREST.

Making an API Call With PostgREST

Assuming that you’re running PostgREST on localhost along with your database server as outlined above, the following HTTP request will return all rows from your books table:

curl “http://localhost:3000/books”

PostgREST supports a wide range of filters to limit the results returned. For example, to get the first five rows from the books table, use this curl request:

curl “http://localhost:3000/books?limit=5”

You can also select which columns from the table you would like returned. This request will return only the book title and author from the first five rows:

curl “http://localhost:3000/books?limit=5&select=title,author”

As previously mentioned, PostgREST can perform full CRUD functions. The above examples show the R (“read”) part in action. Here’s a way to create a new record:

curl "http://localhost:3000/books" \

    -X POST -H "Content-Type: application/json" \

    -d \

    '

    {

        "author": "Ernest Hemingway",

        "title": "The Sun Also Rises"

    }

    '

And here is how you can delete that record that was just added:

curl -X DELETE "http://localhost:3000/books?title=like.”The Sun*"

PostgREST: Features Overview

Out of the box, PostgREST offers the following features:

• A single source of truth with a standardized API.

• API creation from scratch with no custom programming.

• Access to all CRUD operations in a secure fashion.

While PostgresREST does API generation well, it lacks features for more complex projects. Many users still need to have the ability to customize the API, send emails, and add any additional custom business logic. 

Achieving these goals will require combining PostgreSQL’s features, custom programming, and using some of the open-source apps and extensions for PostgREST.

Pros and Cons of PostgREST

In the end, PostgREST is simply a web server that can turn your PostgreSQL database into a REST API. This removes the need to create your CRUD endpoints manually, but it doesn’t offer much more without considerable extra work. With that in mind, here’s a rundown of the pros and cons of PostgREST.

PostgREST Pros

• Provides a lightweight middleware.

• Simple SQL queries to build your API.

• Integrates with OpenAPI to write interactive documentation.

PostgREST Cons

• No native GUI (graphical user interface).

• API documentation is not auto-generated.

• SQL knowledge is required to create each endpoint.

• Allows non-secure APIs to be published and limits row-level security.

With all this in mind, PostgREST will have limited utility in the enterprise. Unless your entire digital strategy is built around a Postgres data warehouse—and you have the developer resources to extend the limited functionality of PostgREST—you will need a more comprehensive API management solution.

As most organizations have multiple applications and services they support, an API management platform is likely to be better suited to your overall needs.

API Generation and Management for PostgreSQL

API generation for PostgreSQL simplifies the process of creating and exposing RESTful interfaces, making database data easily accessible to external systems without the need for manual coding. Tools like PostgREST automatically generate APIs based on the PostgreSQL schema, which speeds up development, ensures consistency, and leverages built-in security features like role-based access control (RBAC). However, while PostgREST is highly effective for basic API creation, an API management platform offers a more comprehensive solution for handling the entire API lifecycle across multiple systems.

An API management platform covers everything from API creation to deployment, versioning, and monitoring, making it ideal for organizations with diverse needs. These platforms automate many aspects of API development, reducing redundancies and speeding up the process. They also provide robust governance and security tools, ensuring that access and authentication are managed in compliance with organizational policies. API management platforms also offer advanced analytics and reporting features, allowing developers to easily parse log data and gain insights into API usage. Centralized documentation through an API management portal further enhances accessibility and usability for both developers and users.

For most organizations, the broader capabilities of an API management platform make it a more attractive option compared to one-off solutions like PostgREST, especially when managing APIs across multiple systems. Choosing the right platform can streamline workflows, improve security, and ensure that all API-related tasks are handled efficiently.

DreamFactory: The Complete API Management Platform

If you do not believe PostgREST meets your needs, consider learning more about DreamFactory’s PostgreSQL connector. DreamFactory has an intuitive dashboard that gives everyone the access control they require to manage their APIs. Many users love DreamFactory because it can automatically generate REST APIs from PostgreSQL. Other benefits include:

• Live API documents.

• Instant API creation.

• API limits.

• Server-side scripting.

DreamFactory provides a complete API management solution for all of your systems, in one central location. Discover the advantage of managing and creating APIs with DreamFactory by signing up for a free 14-day trial.