pg-gateway

README

PG Gateway

 

This project aims to make it simple and fast to interact with a postgresql database over http inspired by Postgrest.

Features

Currently, the application supports the following database interactions

• inserts,
• get whole table,
• get row by id,
• get rows where field=value.

Other features

• For multi-row returns, the application writes rows back to the client as each row is read.
• Low memory requirement (around 8 MB of RAM used for entire docker container under high load. Allocate 16 MB to be generous).
• Database connection cache. Each request doesn't have to wait for a new connection to be made.
• Transfer binary-to-binary. Reading from Postgresql is done in binary and written back to the client without conversion etc.
• Set the database connection details using environment variables. Great for Cloud Native environments.
• Dockefile with light, low attack-surface final image.
• Listen address an environment variable.

Setup

Providing database connection details

Env	Description
listenAddr	Address to listen on for http request (for ex: :8080)
pghost	Postgresql hostname
pguser	Postgresql username
pgpassword	Postgresql password
pgdb	Postgresql database
pgport	Postgresql port
poolSize	Number of postgresql connections to keep open

• Build from source or
• Run the Docker container docker pull just1689/scraping-in-go:svc-db-gateway

You may need to set the search_path for the user.

ALTER USER postgres SET search_path to myschema

Usage

For the examples below, we'll assume the application is hosted on localhost:8080

Get all rows for that table

curl http://localhost:8080/users

is the equivalent of

SELECT * FROM users

Get rows a table where x=y

curl http://localhost:8080/users/x/y

is the equivalent of

SELECT * FROM users WHERE x=y

Get row where id=z

curl http://localhost:8080/users/z

is the equivalent of

SELECT * FROM users WHERE id=z

Insert row into table

curl -X POST \
  http://localhost:8080/entities \
  -H 'Content-Type: application/json' \
  -d '{
	"entity": "user",
	"id": "12",
	"name": "Justin"
}'

is the equivalent of

INSERT INTO entities (entity, id, name) VALUES ("user", "12", "Justin")

Memory Usage

Memory performance after 1M requests with a concurrency of 100.

Roadmap

To do	Notes
Deleting by id	Important feature.
Handle options request	Allow calls from browsers & frameworks using OPTIONS.
Better standard for returning errors	Important feature.
FastHTTP	Fewer allocs for each request.
Bulk insert	Better insert performance.
DockerHub	Project, repository and notes.
Docker Service	Simple script to spin up db and service.
Pagination	Restricting result sets.
Complex queries	HUGE amount of work.
Deleting (complex)	Requires that complex queries are implemented.
Updating rows	Requires that complex queries are implemented.