gem-hunter
Language: C
Now part of Hasura GraphQL Engine. Listen to postgres events and forward them as JSON payloads to a webhook
A few months ago, we built the open source GraphQL Engine that gives you instant GraphQL APIs over any Postgres database. We have added all of Skor's existing features and even more to make it production ready:
Reliable: We capture every relevant action on the database as an event, even when Hasura is down! The events are delivered to your webhook as soon as possible with an atleast-once guarantee.
Scalable: What more, it even scales horizontally. If you are processing millions of events, just add more instances of GraphQL engine.
Use with Serverless: If you are using Skor, then avoid the pain of managing your webhook by moving to Serverless infrastructure. Check out these blog posts to get started
Use Hasura GraphQL Engine for production use cases
skor is a utility for Postgres which calls a webhook with row changes as JSON whenever an INSERT, UPDATE or DELETE event occurs on a particular table.
You can drop the docker image next to your Postgres database instance and configure a webhook that will be called.
It works using a pg_notify trigger function and a tiny C program skor that listens to the notifications and calls the configured webhook with a JSON payload.
A PostgreSQL stored procedure is set up as a trigger on the required table(s). This trigger uses PostgreSQL's LISTEN and NOTIFY to publish change events as JSON to a notification channel. Skor watches this channel for messages and when a message is received, it makes an HTTP POST call to the webhook with the JSON payload. The webhook can then decide to take an action on this.
We need to setup triggers on the tables that we we are interested in. Create a triggers.json file (see sample.triggers.json) with the required tables and events.
Note: This command requires python3.
$ ./gen-triggers.py triggers.json | psql -h localhost -p 5432 -U postgres -d postgres --single-transaction --Run the skor Docker image (that has the skor binary baked in):
$ docker run \
-e DBNAME="postgres" \
-e PGUSER="postgres" \
-e PGPASS="''" \
-e PGHOST="localhost" \
-e PGPORT=5432 \
-e WEBHOOKURL="http://localhost:5000/" \
--net host \
-it hasura/skor:v0.1.1Make sure you use the appropriate database parameters and webhook URL above.
Query:
INSERT INTO test_table(name) VALUES ('abc1');
JSON webhook payload:
{"data": {"id": 1, "name": "abc1"}, "table": "test_table", "op": "INSERT"}Query:
UPDATE test_table SET name = 'pqr1' WHERE id = 1;
JSON webhook payload:
{"data": {"id": 1, "name": "pqr1"}, "table": "test_table", "op": "UPDATE"}Query:
DELETE FROM test_table WHERE id = 1;
JSON webhook payload:
{"data": {"id": 1, "name": "pqr1"}, "table": "test_table", "op": "DELETE"}To remove the skor related functions and triggers that were added to Postgres, run this in psql:
DO $$DECLARE r record; BEGIN FOR r IN SELECT routine_schema, routine_name FROM information_schema.routines WHERE routine_name LIKE 'notify_skor%' LOOP EXECUTE 'DROP FUNCTION ' || quote_ident(r.routine_schema) || '.' || quote_ident(r.routine_name) || ' CASCADE'; END LOOP; END$$;
The pre-built Docker image with the skor binary is available at hasura/skor and can be deployed as a microservice with the sample k8s.yaml in this repo.
The webhook can be another microservice that exposes an endpoint.
To learn more on deploying microservices on Hasura you may check out the documentation.
gcclibcurl4-openssl-dev)libpq-dev)$ make
$ ./build/skor 'host=localhost port=5432 dbname=postgres user=postgres password=' http://localhost:5000tests/requirements.txtlocalhost:5432 and a database called skor_test which can be accessed by an admin user.http://localhost:5000run_tests.sh script in the tests directory.Contributions are welcome!
Please check out the contributing guide to learn about setting up the development environment and building the project. Also look at the issues page and help us in improving Skor!