DEV Community

Franck Pachot for YugabyteDB Distributed PostgreSQL Database

Posted on • Edited on

pREST on YugabyteDB


In a previous post, I published an example with PostgREST on YugabyteDB. Here is one on pREST which adds a REST API to PostgreSQL. As YugabyteDB is PostgreSQL-compatible Open-Source Distributed SQL database, it adds horizontal scalability to applications built for PostgreSQL.

Let's see how it integrates with pREST. The PREST_PG_URL can be set to any YugabyteDB node, and query the whole database from there.

Node locality

In a public or private cloud, there are multiple ways to scale out the pREST servers with YugabyteDB nodes:

  • Start prestd with PREST_PG_URL set to a cluster service (HA proxy, Kubernetes ClusterIP...) over the YugabyteDB nodes
  • Start one prestd for each YugabyteDB node, with its local IP
  • change the github.com/jackc/pgx driver to the cluster-aware one github.com/yugabyte/pgx/v4, as in https://docs.yugabyte.com/preview/drivers-orms/go/yb-pgx/, so that it discovers all nodes of the cluster from any node.

With geo-distribution, it is recommended to co-locate the prestd server in the same zone as the database node(s) it connects to. This will lower the latency and increase availability.

Testing

Start a YugabyteDB cluster with one of the Quick Start methods:
YugabyteDB Documentation / Quick Start

Start prestd with the PostgreSQL connection string to one YugabyteDB node ( default port is 5433 )

Example

Starting a single-node YugabyteDB cluster on Docker:

docker network create yb-net

docker run -d --hostname yb-tserver-n1 -p 7000:7000 \
  --network yb-net yugabytedb/yugabyte:2.14.6.0-b30 \
  yugabyted start --daemon false --listen yb-tserver-n1
Enter fullscreen mode Exit fullscreen mode

Starting prestd connecting to this node:

docker run -d -p 3001:3000 --network yb-net \
  -e PREST_PG_URL=postgres://yugabyte:yugabyte@yb-tserver-n1:5433/yugabyte \
  -e PREST_DEBUG=true \
  prest/prest:v1
Enter fullscreen mode Exit fullscreen mode

Creating a view (yb_servers() is a table function showing all YugabyteDB nodes in the cluster)

docker run --rm --network yb-net yugabytedb/yugabyte \
  ysqlsh -h yb-tserver-n1 -c "
   create view yb_servers as select host,zone,region,cloud from yb_servers()
"
Enter fullscreen mode Exit fullscreen mode

Querying this view through the REST API:

curl -i -X GET "http://127.0.0.1:3001/yugabyte/public/yb_servers" -H "Content-Type: application/json"

[
{"host": "yb-tserver-n1", "zone": "rack1", "cloud": "cloud1", "region": "datacenter1"}
]
Enter fullscreen mode Exit fullscreen mode

Starting two more YugabyteDB nodes (yb-tserver-n2 and yb-tserver-n3) to join the previous one (yb-tserver-n1):

docker run -d --hostname yb-tserver-n2 \
  --network yb-net yugabytedb/yugabyte:2.14.6.0-b30 \
  yugabyted start --join yb-tserver-n1 --daemon false --listen yb-tserver-n2

docker run -d --hostname yb-tserver-n3 \
  --network yb-net yugabytedb/yugabyte:2.14.6.0-b30 \
  yugabyted start --join yb-tserver-n1 --daemon false --listen yb-tserver-n3
Enter fullscreen mode Exit fullscreen mode

Starting one prestd for each YugabyteDB node:

docker run -d -p 3002:3000 --network yb-net \
  -e PREST_PG_URL=postgres://yugabyte:yugabyte@yb-tserver-n2:5433/yugabyte \
  -e PREST_DEBUG=true \
  prest/prest:v1

docker run -d -p 3003:3000 --network yb-net \
  -e PREST_PG_URL=postgres://yugabyte:yugabyte@yb-tserver-n3:5433/yugabyte \
  -e PREST_DEBUG=true \
  prest/prest:v1
Enter fullscreen mode Exit fullscreen mode

Querying any endpoint to read from the view

curl -i -X GET "http://127.0.0.1:3001/yugabyte/public/yb_servers" -H "Content-Type: application/json"

[
{"host": "yb-tserver-n3", "zone": "rack1", "cloud": "cloud1", "region": "datacenter1"},
 {"host": "yb-tserver-n2", "zone": "rack1", "cloud": "cloud1", "region": "datacenter1"},
 {"host": "yb-tserver-n1", "zone": "rack1", "cloud": "cloud1", "region": "datacenter1"}
]

curl -i -X GET "http://127.0.0.1:3003/yugabyte/public/yb_servers" -H "Content-Type: application/json"

[
{"host": "yb-tserver-n3", "zone": "rack1", "cloud": "cloud1", "region": "datacenter1"},
 {"host": "yb-tserver-n2", "zone": "rack1", "cloud": "cloud1", "region": "datacenter1"},
 {"host": "yb-tserver-n1", "zone": "rack1", "cloud": "cloud1", "region": "datacenter1"}
]
Enter fullscreen mode Exit fullscreen mode

All works as with PostgreSQL, with the additional High Availability and Elasticity provided by YugabyteDB πŸš€

Sakila

For more tests, I import the Sakila database:

for sql in yugabytedb-sakila-schema.sql yugabytedb-sakila-insert-data.sql
do
 curl -s https://raw.githubusercontent.com/jOOQ/sakila/main/yugabytedb-sakila-db/$sql
done | 
docker run -i --rm --network yb-net yugabytedb/yugabyte \
  ysqlsh -h yb-tserver-n1

Enter fullscreen mode Exit fullscreen mode

Here is a simple query:

curl -sG http://127.0.0.1:3001/yugabyte/public/rental \
  -d _select='customer.last_name,customer.first_name' \
  -d _join='inner:customer:rental.customer_id:$eq:customer.customer_id' \
  -d staff_id=2 \
  -d _order='-return_date' -d _page=1 -d _page_size=5 | jq '.'
Enter fullscreen mode Exit fullscreen mode

And the result:
Image description

REST API is a great interface with the database. The application developer has a simple API for CRUD operations and a SQL developer can even add views or custom queries for more complex use cases. With a SQL database, no need to worry about race conditions: choose the right isolation level and the database will prevent anomalies. With Distributed SQL database, the application is always available even during failures or upgrades. With YugabyteDB, all this is Open Source and PostgreSQL compatible.

My recommendation is to encapsulate complex queries into views so that the call to the REST API stays simple with a GET /{DATABASE}/{SCHEMA}/{VIEW} with parameters for filters, sort, pagination. The application developer doesn't have to think about the SQL complexity and the database developer can optimize the access paths.

Top comments (0)