<center>![HafSQL.png](https://files.peakd.com/file/peakd-hive/mahdiyari/23uEsNRBN8VFqNkR4aCSQNwDRgLN2G4wfa9XnM9DyWZQwdrNaay2JikkpGnTU2eaS7JzA.png)</center>

HafSQL runs on a HAF node and makes it possible to run certain queries on the HAF data. It does this by parsing the data already present in the HAF database and adding additional indexes and tables.

As I wrote in [the original proposal post](https://peakd.com/hive-139531/@mahdiyari/dhf-proposal-hive-related-development), the first application is the HafSQL itself and the second is a way to provide this data to the apps and users.

It is fast and easy to setup on any HAF node and doesn't take that much space. Fast here doesn't mean 10 minutes. It can take around 10 hours to be ready on a HAF node which depends on the hardware. It is fast in regards that we are dealing with nearly 5 billion operations.

The first app is self explanatory but for the second one the original plan was to parse the SQL queries in a middleware and forward them into the database. What ended up being is a jussi compatible API endpoint that provides a JSON-RPC2 API access.

I did implement an structure to allow custom queries be implemented in the API as the community or the apps ask for them. For example one custom query that is already added is getting comments/posts sorted by their pending payout value.

I thought about this for a while and I think API is the better approach and will make it easier for public nodes to install and run. Some might decide to provide open access to the database and I will probably provide a public instance shortly too.
***
### Beta version
It is not really beta because it is ready but it might need some more testing in production and feedback before being fully released.

I encourage developers and anyone using hivesql to go through list of the available APIs and see if anything is missing in their opinion.

I expect to add some additional custom queries but I don't have a clear idea of what is being used.

***
### Going forward
The proposal has ended few days ago and I was doing the final touches since then. I expect to work on this project probably a few more days. Maybe not in terms of coding but testing in production, documenting, and adding custom queries that might come up.

The first motivation for starting this project was having an open source alternative to hivesql (which we have been paying $4,000 monthly through DHF). But a few times during the development I though about going a little bit further and replacing the hivemind too. HafSQL only misses a couple of things that is available on hivemind. I can try developing further but will need more feedback. I'm not sure yet how will public node providers go about this project.

***
### How HafSQL works
Examples:
- Get all the transfer operations that have memo="test"
```
curl -s --data '{"jsonrpc":"2.0", "method":"hafsql.op_transfer", "params":{"memo": "test"}, "id":1}' https://rpc.mahdiyari.info
```

- Get all the delegations to account "ocdb"
```
curl -s --data '{"jsonrpc":"2.0", "method":"hafsql.delegations", "params":{"delegatee": "ocdb"}, "id":1}' https://rpc.mahdiyari.info
```

Checkout the gitlab page of the [hafsql-api](https://gitlab.com/mahdiyari/hafsql-api) for the full list of APIs.

***
### Testing
You can try playing with the APIs or you can replay a [HAF](https://gitlab.syncad.com/hive/haf) node with the 5 million block_log and install HafSQL there. It should take maybe around 10-30 minutes. This can be helpful before running on a production HAF node as well.

Installation instructions are provided in the gitlab repository of each app. All the APIs are listed there as well as their parameters etc.

HafSQL: https://gitlab.com/mahdiyari/hafsql
HafSQL-API: https://gitlab.com/mahdiyari/hafsql-api
RPC node: https://rpc.mahdiyari.info

***

Thanks for all the support. I appreciate it.