pREST on YugabyteDB
Tags: postgres, rest, yugabytedb, ords
Published: 2023-01-22 19:18:27

{% embed https://twitter.com/GolangRepos/status/1454538556022697986?s=20&t=Uxf5d0BrxTg99KDrdkLdPQ %}
In a previous post, I published an example with [PostgREST on YugabyteDB](https://dev.to/yugabyte/rest-data-service-on-yugabytedb-postgresql-5f2h). Here is one on [pREST](https://github.com/prest/prest) which adds a **REST API** to **PostgreSQL**. As [YugabyteDB](https://www.yugabyte.com/) is PostgreSQL-compatible Open-Source **Distributed SQL** database, it adds horizontal scalability to applications built for PostgreSQL.


Let's see how it integrates with _p_**REST**. 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](https://docs.yugabyte.com/preview/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:

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

```sh
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)

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

```json
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`):

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

```sh
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

```json
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:
```sh
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:
```sh
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:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bv3lwdxlvncylp31d1fc.png)

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](https://docs.prestd.com/prestd/api-reference/parameters/#filters). The application developer doesn't have to think about the SQL complexity and the database developer can optimize the access paths.