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
prestdwithPREST_PG_URLset to a cluster service (HA proxy, Kubernetes ClusterIP...) over the YugabyteDB nodes - Start one
prestdfor each YugabyteDB node, with its local IP - change the
github.com/jackc/pgxdriver to the cluster-aware onegithub.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
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
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()
"
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"}
]
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
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
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"}
]
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
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 '.'
And the result:
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)