Blockchair.com Direct SQL Access documentation
Table of contents
- v.0.0.1 - May 3rd, 2019
- We begin beta testing direct access to our databases. If you're interested in participating in the beta test, please reach us out at firstname.lastname@example.org. Note that while in beta, there may be some compatibility breaking changes.
Blockchair's Direct SQL Access allows you to query our databases directly bypassing our API. While in some cases our API works a lot faster (e.g. querying the balance of an address), sometimes it's handy to have an ability to execute an arbitrary query (e.g. you can create your own filters to aggregate data however you want, or you can use window functions).
We start with supporting three blockchains - Bitcoin (BTC), Bitcoin Cash (BCH), and Litecoin (LTC). More blockchains are coming in the future.
All our databases are working in real time - as soon as there's a new block it is instantly populated into the database.
While Blockchair has a public API available without obtaining a key, this feature requires dedicated access. Please contact us at email@example.com for pricing. As always, we provide free access for academic purposes in some cases.
The database server is PostgreSQL 11.
The connection parameters are:
- Server: <redacted>
- Login: <your personal username>
- Password: <your personal password>
- Database name:
The simplest way to connect is to use
$ psql -h <server> -p 5432 -U <login> blockchair
Please note that we allow neither connections to the
postgres database, nor access to
public schemas. External users do not have writing permissions, including creating views.
By default, there's a limit of 1 connection per user.
The following schemas are available:
Each schema contains:
usd(BIGINT, TIMESTAMP DEFAULT current_date)function to convert satoshi values to USD values using exchange rate on a specified date
To get columns description use
\d bitcoin.blocks. The columns are consistent with the described columns in the API documentation. There are no
*_usd columns, please use the
usd function instead (e.g.
blocks.output_total_usd can be accessed as
There are following indexes available:
blockchair=> SELECT id, encode(hash, 'hex'), time FROM bitcoin.blocks WHERE id = 0 blockchair-> UNION ALL blockchair-> SELECT id, encode(hash, 'hex'), time FROM bitcoincash.blocks WHERE id = 0 blockchair-> UNION ALL blockchair-> SELECT id, encode(hash, 'hex'), time FROM litecoin.blocks WHERE id = 0; id | encode | time ----+------------------------------------------------------------------+--------------------- 0 | 000000000019d6689c085ae165831e934ff763ae46a2a6c172b3f1b60a8ce26f | 2009-01-03 18:15:05 0 | 000000000019d6689c085ae165831e934ff763ae46a2a6c172b3f1b60a8ce26f | 2009-01-03 18:15:05 0 | 12a765e31ffd4059bada1e25190f6e98c99d9714d334efa41a195a7e7e04bfe2 | 2011-10-07 07:31:05 (3 rows)
Support is available in two languages: English and Russian.