Imagine you have a databases created with MySQL (or other SQL server). You need to share this database with some community and allow write access. And because of trust problem, there can not be any “master” nodes or any special administrating roles with special permissions. Additionally, there is no 100% trust to all users who will want to use a database, however, you expect to have the DB working.
In other words, you need to create a distributed ledger. Best and most known technology to convert your local database to a distributed ledger is a blockchain.
How to do this?
How many coding will it require to join your MySQL DB with a blockchain tools?
What if your local DB already has a GUI and you want to reuse it in your distributed ledger too?
Some time ago I had this question. I didn’t find a solution that time.
And I got an idea to create the tool.
The idea
I would like to have a tool that can do all work related to replication of data between copies of same databases managed by different people. I want to work on my DApp business logic as easy as on any centralised application, like a web site or a desktop app storing a data in a local database. In my DApp I don’t want to think about how data changes are delivered to other instances of an app.
When I do update in my local MySQL DB , I want this update automatically be replicated on other copies of this DB supported by other people.
And , of course, I want this all to be secure. There must be rules of possible updates, specified for DB users and different DB entities. Even if some users are not fair and try to modify what is not allowed for them, it must be restricted during replication.
Blockchain can solve all that requirements.
So, I decided to make an app that will synchronize MySQL databases using a blockchain. And now, I would like to introduce OurSQL — a tool to replicate MySQL/MariaDB databases using a blockchain.
What problems does it solve
OurSQL makes development of a DApp (decentralized application ) to be much simpler.
OurSQL allows to run distributed ledger as a SQL database and work with it using standard SQL client tools and libraries.
Therefore, using the OurSQL as a component of a DApp decreases amount of coding and allows to focus on a DApp business logic, instead of thinking about how changes will be delivered to nodes. This tool does all work related to synchronizing of changes in local copies of distributed ledger.
A DApp code becomes as simple as a code of a web site or a desktop app working with a local MySQL. DApp interface doesn’t need any special blockchain libraries, just use standard mysql client module/package of your programming language to connect to local mysql server (which is mysql proxy included in OurSQL).
How does it work
OurSQL is a server software that runs in between a MySQL server and a DB client software. All together MySQL + OurSQL + Application (GUI & business logic of a DApp) creates a single instance of a decentralised application — a Node.
There are 2 core parts of this tool — DB proxy server and a blockchain management server.
Every node works with a single MySQL database. Blockchain data are stored in same database together with data tables. All communication with a DB is only through OurSQL, nothing goes directly to MySQL server.
OurSQL node server can be started after new decentralised DB is created and new blockchain started. Or a node joins existent decentralized DB on init. A node server listens on 2 ports: first — DB proxy server for local mysql clients , second — a port to communicate with other OurSQL nodes of current DB cluster.
MySQL client connects to a proxy on a known port. This is same process as connecting to MySQL server directly on “localhost”, just different port number. MySQL client can be SQL tool or it can be GUI for a DApp using this decentralized DB. A client can execute any type of SQL read query (SELECT or so), this will be executed only against local instance of a DB. More complex work-flow is for SQL update queries.
When mysql client executes SQL update query:
- DB proxy checks with OurSQL blockchain server (BS) if this query can be executed
- BS parses SQL, checks if a table from a query is configured for replication. If not, the query is just executed
- BS checks a query against consensus rules. If this operation is allowed on this able, if this user (identified by public key) is allowed to execute. And some other checks
- If all checks passed, BS prepares a transaction for blockchain, adds it to a pool of transactions and allow a proxy to complete a query
- When there are no yet enough transactions to make new block, a node sends new transaction to other nodes
- When there are enough transactions, BS makes a block (using Proof of Work approach) and when a block is created, it is sent to all other known OurSQL nodes (of this database).
- When a node receives a block or a transaction, it verifies it against a blockchain state, against all consensus rules , and if all passed, a transaction is added to a pool or a block is added to a chain. And all income transactions are executed to apply DB changes in a node local DB.
This work-flow is similar to well known blockchain networks, like a bitcoin or so. The difference is a contents of transactions.
Cryptocurrency
Every decentralized database created and supported with OurSQL receives own blockchain and can have own internal cryptocurrency.
A cryptocurrency is like a side effect. A DApp using a DB may use a cryptocurrency or may not. It is optional.
However, internal cryptocurrency can be useful for a consensus mechanism, it can help to restrict access to a DB by using “paid” SQL queries. Considering a cryptocurrency as an action energy does good work in self regulation of a DB modification.
Blockchain consensus
Consensus agreement is a core feature of a blockchain technology. Consensus is a set of rules agreed by all nodes that helps to keep a decentralized database in a consistent state. Important role of a consensus agreement is to protect a database from unwanted modifications when some part of nodes are under control of “bad people”.
Currently , OurSQL supports only configurable Proof of Work (PoW) consensus approach. Each database has own “consensus config” document which describes options of PoW used in this database: complexity of a block hash, number of transactions per block, minter (“miner”) wage — count of coins in internal currency for new block made.
Additionally, consensus config file contains rules related to a DB tables: which tables should be replicated, which operations are allowed for a particular table (for example, disable update and delete on some table). Besides, it is possible to set up a cost of different SQL update operations for particular tables (as good option to control data flood etc).
Relation to other blockchains and cryptocurrencies
As it was said above, there is cryptocurrency created for every blockchain when it is created. Such blockchains are private blockchains and are not somehow related to any other blockchains, including famous cryptocurrencies and cryptonetworks. Private means it is new blockchain, but, of course, it can be made public.
There are no “smart-contracts”. However, every DApp created on base of OurSQL can be considered as a smart-contract itself. The only smart-contract on its own blockchain.
Brending
The name OurSQL came by itself. If MySQL is my database, than OurSQL is work with our database (shared database).
And this logo follows after MySQL logo (I hope this is legal). With OurSQL your MySQL databases can go together like a pod of dolphins.
How to try it
Currently, there are no installation package yet. You can try OurSQL if you know how to compile Golang code. The code is on https://github.com/gelembjuk/oursql
Or, you can use a docker image. If you are familiar with docker, just execute:
docker run --name oursql1 -p 9001:8765 -p 9002:8766 -d -it oursql/oursql-server interactiveautocreate -port 9001
This creates in decentralized ledges — SQL database and a blockchain . Now run other node and connected it to the first.
docker run --name oursql2 -p 9003:8765 -p 9004:8766 -d -it oursql/oursql-server importfromandstart -port 9003 -nodeaddress host.local.address:9001
Use command line mysql client o connect to your nodes, execute some SQL updates and see how data are replicated.
mysql -h 127.0.0.1 -P 9002 -u blockchain -pblockchain BC
> CREATE TABLE test (a int unsigned primary key auto_increment, b varchar(100));
> INSERT INTO test SET b='row1';
And the second node
mysql -h 127.0.0.1 -P 9004 -u blockchain -pblockchain BC
> SELECT * FROM test;
> INSERT INTO test SET b='row2';
Next steps
There are two important things to add to OurSQL soon.
Firstly, it is needed to make consensus functionality to be more flaxible. Now there is only PoW support and basic update rules. But in real works this is not enough. I am going to add support of a “consensus module” to be possible to code for every DB. It will be integral part of a DB. The module will allow to add flexible rules for SQL updates, like “if it is request to add new like in a post_likes table, check if this user is friends with a post owner“.
Secondly, I am going to develop some example application using OurSQL to demonstrate how it can be used on practice.
Follow our updates on http://github.com/gelembjuk/oursql