In todays post I will walk through the process of installing MariaDB, run the database server and access it through Javascript. In this project I will get real time trades from the Binance futures exchange (BTCUSDT and ETHUSDT). The goal with this post is to create a template database structure to save stock prices, orderbooks or other stuff gathered online via Javascript.
Why Javascript? To get the data I will use websockets. Javascript is one of the best performing language in various websocket performance comparisons. Javascriptcode can also easily be written asynchronously – when we have multiple websockets, e.g. from multiple exchanges simultaneously, it will be very handy.
MariaDB is a fork of MySQL. Some developers created it after some concerns of the acquisition of MySQL by Oracle.
Install MariaDB
First we will need to download and install the MariaDB Server. We can download it on the official website: https://mariadb.org/download/
This is the current installation process:
Run MariaDB server
To create and edit a database with MariaDB we need to run it as a server (database server), which is nothing else than a program in the background with which we can access our created databases. Some reference can be found here: https://mariadb.com/kb/en/mariadb-basics/
First we will need to start cmd (I use windows, I do not know what the MacOS or Linux equivalents are nor do I care) and open the directory in which we installed MariaDB with the command “cd”. In my case “cd C:\Program Files\MariaDB 10.8”
With the “dir” command we can see every sub-directory in the folder. We will open the “bin” directory:
To run the server we will now run the “mysql” program in the “bin” folder using the login of the root user we created in the installation process. The command will be the program name, followed by the username (-u username) followed by the password (-p → enter password after entered the command) followed by the hostname (-h hostname):
After initialising the database server correclty it should look something like this:
Here are two essential commands:
– To show the current databases available: “show databases;”
– To shutdown the server: “exit”
This was the part of how to install and run MariaDB server. Now we will start with how to access MariaDB server through Javascript, create a database, tables and records and edit them. The server does not need to run in the background through cmd to work since we will start the connection from Javascript anyway.
Access MariaDB with Javascript
We will gather realtime trades data of Binance through a websocket and save the last n trades. Then we save these trades in the database and empty the trades-list to free up the list of recent trades for new trades. We do that to not overload the list with to many trades at once and not to connect, insert and disconnect from the database after every trade.
First things first: We will use a websocket library called “ws” (https://www.npmjs.com/package/ws) to create a websocket connection to the Binance exchange and get realtime futures data. On the “ws” module site you can see some examples if you scroll down a bit (install the library with “npm i ws”). I will build my code with the help of this example code:
import WebSocket from 'ws';
const ws = new WebSocket('ws://www.host.com/path');
ws.on('open', function open() {
ws.send('something');
});
ws.on('message', function message(data) {
console.log('received: %s', data);
});
After some coding I ended up with the code below which can retrieve real time data of the last trades on BTCUSDT and ETHUSDT (Binance exchange – futures). It saves the last n incoming trades into an array and hand it over to the function which I will create afterwards. The function will then delete all these entries from the array.
//####################
// import ws library
const WebSocket = require("ws");
//####################
//####################
// log date and time when program started
var date = new Date();
console.log(`${date.toGMTString()} | Program started`);
//####################
//####################
// save last n (maxNEntries) trades to this array
let tradesData = []
let maxNEntries = 1000
//####################
//####################
async function RunWebsocketBinance() {
// Create an array with our streams which we want to get from Binance and connect to the Binance Websocket
// With the '.join("/")' we can join the elements of the array to a single string seperated by "/"
const streamsBinance = ["btcusdt@trade", "ethusdt@trade"];
var wsBinance = new WebSocket("wss://fstream.binance.com/stream?streams="+streamsBinance.join("/"));
//####################
//####################
// There are different actions this websocket client can have:
// When establishing the connection -> "open"
// When retrieving a message from binance -> "message"
// To keep the connection alive -> "ping" and "pong"
// -> https://www.npmjs.com/package/ws: Pong messages are automatically sent in response to ping messages as required by the spec
// all the work after each action is done in their functions
wsBinance.on("open", function openWsBinance() {
console.log(`${date.toGMTString()} | Connected to Binance Websocket`);
});
wsBinance.on("message", function incomingWsBinance(data) {
//console.log(tradesData.length);//data.toString());
tradesData.push(JSON.parse(data.toString())["data"]);
// when we collected 1000 messages -> write them into our sql db and empty the messages array in the "insertData" function
if (tradesData.length == maxNEntries) {
insertData(tradesData, maxNEntries);
}
});
wsBinance.on("ping", function heartbeatWsBinance() {
date = new Date();
console.log(`${date.toGMTString()} | Got a Ping from Binance | Pong has been sent automatically`);
});
wsBinance.on("pong", function heartbeatWsBinance() {
date = new Date();
console.log(`${date.toGMTString()} | Got a Pong from Binance`);
});
}
//####################
//####################
async function main() {
await createDB();
RunWebsocketBinance();
}
//####################
//####################
main()
//####################
For the connection between and querying from Javascript and MariaDB I will use the library “mariadb” (https://www.npmjs.com/package/mariadb). I will not go into the code super detailed since I think that everyone who codes and knows a little bit about Javascript, SQL and queyring should understand it easily. In my code I will create a new database called “cryptoDB” and create two tables. The table “symbols” will hold the names of the cryptocurrencies (like “BTCUSDT”) and their unique ID in the table. The table “trades” will hold the information about the recent trades we retrieve:
//####################
// import library to connect with mariadb
const mariadb = require("mariadb");
//####################
//####################
async function createDB() {
var conn;
try {
// connect to mariadb using the hostname and our login data
var pool = mariadb.createPool({
host: "localhost",
user:"root",
password: "1234"
});
conn = await pool.getConnection();
// create the database
await conn.query("DROP DATABASE IF EXISTS cryptodb;");
await conn.query("CREATE DATABASE cryptodb;");
// in the created database: create tables "symbols" and "trades"
await conn.query("use cryptodb;");
await conn.query("CREATE TABLE symbols(\
ID INT(11) NOT NULL AUTO_INCREMENT,\
Name VARCHAR(50),\
PRIMARY KEY (ID)\
)\
engine=innodb\
DEFAULT CHARACTER SET = utf8;");
await conn.query("CREATE TABLE trades(\
ID INT(11) NOT NULL AUTO_INCREMENT,\
SymbolID INT(11) NOT NULL,\
EventTime BIGINT(11) NOT NULL,\
Price FLOAT(11, 6) NOT NULL,\
Quantity FLOAT(11, 6) NOT NULL,\
TradeTime BIGINT(11) NOT NULL,\
PRIMARY KEY (ID),\
CONSTRAINT fk_SymbolID\
FOREIGN KEY (SymbolID) REFERENCES Symbols (ID)\
ON DELETE NO ACTION\
ON UPDATE NO ACTION\
)\
engine=innodb\
DEFAULT CHARACTER SET = utf8;");
pool.end();
conn.end();
} catch {
console.log("Something went wrong (creating database)");
console.log(err);
pool.end();
conn.end();
}
}
//####################
//####################
async function insertData(trades, n) {
var conn;
try {
// connect to mariadb using the hostname and our login data
var pool = mariadb.createPool({
host: "localhost",
user:"root",
password: "1234"
});
conn = await pool.getConnection();
await conn.query("use cryptodb;");
// insert every trade
for (let i = 0; i < trades.length; i++) {
// get symbol name id - if not existing -> create it
var SymbolIDQuery = await conn.query("SELECT ID FROM symbols WHERE Name='"+String(trades[i]["s"])+"';");
if (SymbolIDQuery.length == 1) {
SymbolIDQuery = SymbolIDQuery[0]["ID"];
} else {
await conn.query("INSERT INTO symbols (Name) VALUES ('"+String(trades[i]["s"])+"');");
SymbolIDQuery = await conn.query("SELECT ID FROM symbols WHERE Name='"+String(trades[i]["s"])+"';");
SymbolIDQuery = SymbolIDQuery[0]["ID"];
console.log("New entry in symbols: " + String(trades[i]["s"]));
}
// create insert sql query
var sqlQueryValues = "(" + String(SymbolIDQuery);
sqlQueryValues = sqlQueryValues + ", " + String(trades[i]["E"]);
sqlQueryValues = sqlQueryValues + ", " + String(trades[i]["p"]);
sqlQueryValues = sqlQueryValues + ", " + String(trades[i]["q"]);
sqlQueryValues = sqlQueryValues + ", " + String(trades[i]["T"]);
sqlQueryValues = sqlQueryValues + ")";
sqlQuery = "INSERT INTO trades (SymbolID, EventTime, Price, Quantity, TradeTime) VALUES " + sqlQueryValues;
await conn.query(sqlQuery);
}
trades.splice(0, n);
pool.end();
conn.end();
} catch (err) {
console.log("Something went wrong (insert data)");
console.log(err);
pool.end();
conn.end();
}
}
//###################################################################
Put it all together we get the following code:
//####################
// import library to connect with mariadb
const mariadb = require("mariadb");
//####################
//####################
async function createDB() {
var conn;
try {
// connect to mariadb using the hostname and our login data
var pool = mariadb.createPool({
host: "localhost",
user:"root",
password: "1234"
});
conn = await pool.getConnection();
// create the database
await conn.query("DROP DATABASE IF EXISTS cryptodb;");
await conn.query("CREATE DATABASE cryptodb;");
// in the created database: create tables "symbols" and "trades"
await conn.query("use cryptodb;");
await conn.query("CREATE TABLE symbols(\
ID INT(11) NOT NULL AUTO_INCREMENT,\
Name VARCHAR(50),\
PRIMARY KEY (ID)\
)\
engine=innodb\
DEFAULT CHARACTER SET = utf8;");
await conn.query("CREATE TABLE trades(\
ID INT(11) NOT NULL AUTO_INCREMENT,\
SymbolID INT(11) NOT NULL,\
EventTime BIGINT(11) NOT NULL,\
Price FLOAT(11, 6) NOT NULL,\
Quantity FLOAT(11, 6) NOT NULL,\
TradeTime BIGINT(11) NOT NULL,\
PRIMARY KEY (ID),\
CONSTRAINT fk_SymbolID\
FOREIGN KEY (SymbolID) REFERENCES Symbols (ID)\
ON DELETE NO ACTION\
ON UPDATE NO ACTION\
)\
engine=innodb\
DEFAULT CHARACTER SET = utf8;");
pool.end();
conn.end();
} catch {
console.log("Something went wrong (creating database)");
console.log(err);
pool.end();
conn.end();
}
}
//####################
//####################
async function insertData(trades, n) {
var conn;
try {
// connect to mariadb using the hostname and our login data
var pool = mariadb.createPool({
host: "localhost",
user:"root",
password: "1234"
});
conn = await pool.getConnection();
await conn.query("use cryptodb;");
// insert every trade
for (let i = 0; i < trades.length; i++) {
// get symbol name id - if not existing -> create it
var SymbolIDQuery = await conn.query("SELECT ID FROM symbols WHERE Name='"+String(trades[i]["s"])+"';");
if (SymbolIDQuery.length == 1) {
SymbolIDQuery = SymbolIDQuery[0]["ID"];
} else {
await conn.query("INSERT INTO symbols (Name) VALUES ('"+String(trades[i]["s"])+"');");
SymbolIDQuery = await conn.query("SELECT ID FROM symbols WHERE Name='"+String(trades[i]["s"])+"';");
SymbolIDQuery = SymbolIDQuery[0]["ID"];
console.log("New entry in symbols: " + String(trades[i]["s"]));
}
// create insert sql query
var sqlQueryValues = "(" + String(SymbolIDQuery);
sqlQueryValues = sqlQueryValues + ", " + String(trades[i]["E"]);
sqlQueryValues = sqlQueryValues + ", " + String(trades[i]["p"]);
sqlQueryValues = sqlQueryValues + ", " + String(trades[i]["q"]);
sqlQueryValues = sqlQueryValues + ", " + String(trades[i]["T"]);
sqlQueryValues = sqlQueryValues + ")";
sqlQuery = "INSERT INTO trades (SymbolID, EventTime, Price, Quantity, TradeTime) VALUES " + sqlQueryValues;
await conn.query(sqlQuery);
}
trades.splice(0, n);
pool.end();
conn.end();
} catch (err) {
console.log("Something went wrong (insert data)");
console.log(err);
pool.end();
conn.end();
}
}
//###################################################################
//###################################################################
// import ws library
const WebSocket = require("ws");
//####################
//####################
// log date and time when program started
var date = new Date();
console.log(`${date.toGMTString()} | Program started`);
//####################
//####################
// save last n (maxNEntries) trades to this array
let tradesData = []
let maxNEntries = 1000
//####################
//####################
async function RunWebsocketBinance() {
// Create an array with our streams which we want to get from Binance and connect to the Binance Websocket
// With the '.join("/")' we can join the elements of the array to a single string seperated by "/"
const streamsBinance = ["btcusdt@trade", "ethusdt@trade"];
var wsBinance = new WebSocket("wss://fstream.binance.com/stream?streams="+streamsBinance.join("/"));
//####################
//####################
// There are different actions this websocket client can have:
// When establishing the connection -> "open"
// When retrieving a message from binance -> "message"
// To keep the connection alive -> "ping" and "pong"
// -> https://www.npmjs.com/package/ws: Pong messages are automatically sent in response to ping messages as required by the spec
// all the work after each action is done in their functions
wsBinance.on("open", function openWsBinance() {
console.log(`${date.toGMTString()} | Connected to Binance Websocket`);
});
wsBinance.on("message", function incomingWsBinance(data) {
//console.log(tradesData.length);//data.toString());
tradesData.push(JSON.parse(data.toString())["data"]);
// when we collected 1000 messages -> write them into our sql db and empty the messages array
if (tradesData.length == maxNEntries) {
insertData(tradesData, maxNEntries);
}
});
wsBinance.on("ping", function heartbeatWsBinance() {
date = new Date();
console.log(`${date.toGMTString()} | Got a Ping from Binance | Pong has been sent automatically`);
});
wsBinance.on("pong", function heartbeatWsBinance() {
date = new Date();
console.log(`${date.toGMTString()} | Got a Pong from Binance`);
});
}
//####################
//####################
async function main() {
await createDB();
RunWebsocketBinance();
}
//####################
//####################
main()
//####################
The written code does not stop – so just CTRL + C.
In addition to the code above I also wrote some little script to export the data from the tables (after we gathered our data) into seperate csv’s. It basically connects to the database, queries the data in the tables using “SELECT * FROM tablename” and writes it to a file using the NodeJS library fs:
const fs = require('fs');
const mariadb = require("mariadb");
//####################
function JsonArrayToCSV(arr){
var keys = Object.keys(arr[0]);
var csv = [];
var tmp = "";
for (let j = 0; j < keys.length; j++) {
if (j == 0) {
tmp = String(keys[j]);
} else {
tmp = tmp + ", " + String(keys[j]);
}
}
csv.push(tmp);
for (let j = 0; j < arr.length; j++) {
for (let k = 0; k < keys.length; k++) {
if (k == 0) {
tmp = String(arr[j][keys[k]]);
} else {
tmp = tmp + ", " + String(arr[j][keys[k]]);
}
}
csv.push(tmp);
}
csv = csv.join('\n');
return csv;
}
//####################
async function main() {
var conn;
try {
// connect to mariadb using the hostname and our login data
var pool = mariadb.createPool({
host: "localhost",
user:"root",
password: "1234"
});
conn = await pool.getConnection();
await conn.query("use cryptodb;");
tables = await conn.query("SHOW TABLES");
for (let i = 0; i < tables.length; i++) {
var tmpData = await conn.query("SELECT * FROM " + tables[i]["Tables_in_cryptodb"]);
var csv = JsonArrayToCSV(tmpData);
fs.writeFileSync("table"+tables[i]["Tables_in_cryptodb"]+".csv", csv);
}
pool.end();
conn.end();
} catch (err) {
console.log("Something went wrong (export data)");
console.log(err);
pool.end();
conn.end();
}
}
main();
This project can of course also be found on my github (github.com/Heuristic-Analyst/…) – adios!