Upgrading the Stratos communities database to Sequelize
Repository
https://github.com/stratos-steem/stratos
New Features
Stratos began development by using a local database, SQLite, which is easy to use and can be set up on anyone's computer with no installation required. This database would allow anyone to simply run Stratos and participate in consensus easily, but since it is a local database it is not very performant. From the SQLite homepage:
any site that gets fewer than 100K hits/day should work fine with SQLite.
But Steem needs to be prepared for the future and a time where more than a few thousands of users will be using a site on Steem, so to plan for that I knew that at one point Stratos would need to upgrade to a client/server database like PostgreSQL. But PostgreSQL is very hard to set up and if that were the only way to set up a node the network would be limited only to those who can pay for and have the expertise to maintain a full PostgreSQL database.
So I was planning on allowing the user to execute two pieces of code, one which runs using PostgreSQL and one that uses an SQLite local database. Originally I had thought that this would mean I would have to write all the queries and code twice, one for each server, until I found Sequelize. Sequelize is a SQL ORM for nodejs that can use database dialects PostgreSQL, MySQL, SQLite and MSSQL and translate between them in the same code. So then if Stratos uses Sequelize, the code will only have to be written once but it can use both PostgreSQL and SQLite.
So I switched to Sequelize, completely updating the database.js file to work with both databases. For example, I changed
getFeatured: function(community, limit, callback) {
const query = 'SELECT DISTINCT * FROM featured_posts WHERE community=? ORDER BY block DESC LIMIT ?';
db.all(query, [community, limit], function(err, rows) {
if(err) {
throw err
}
},
which used the sqlite3 package, to
getFeatured: function(community, limit, callback) {
Post.findAll({
where: {
community: community,
featured: true
},
order: sequelize.literal('block DESC'),
limit: limit
}).then(callback);
},
which uses Sequelize to integrate with both PostgreSQL and SQLite at the same time. This was completed in PR #9.
Once the database had been upgraded to no longer need persistent disk, the project was nearly at the point where it could run on services like Heroku or Google App Engine (which have huge benefits) where there is no persistent disk. The only thing left was to move the state storage from a local state.json file to a table in the either SQLite or PostgreSQL database. This meant changing the state storage functions in the main code and adding the new table with its functions in the database.js file. For example, the following code saves the current state (stored in RAM for fast usage) to the DB so that it can be reloaded at a later time by updating the single-row table State:
saveState: function(currentBlock, lastCheckpointHash, currentState, consensusDisagreements) {
State.findOne({
where: {}
}).then((result) => {
if (result) {
result.update({
block: currentBlock,
lastCheckpointHash: lastCheckpointHash,
state: JSON.stringify(currentState),
consensusDisagreements: JSON.stringify(consensusDisagreements)
}).then(() => {
console.log('Saved state.')
});
} else {
console.log('Error saving state: state not correctly created in DB?')
}
});
},
This upgrade was done in PR #10.
So in two pull requests, the entire database has been upgraded to be practically infinitely vertically scalable through PostgreSQL (not to mention that the database and nodes themselves are horizontally scalable because it runs off of Steem), as well as allowing for node usage on platforms like Heroku or Google App Engine for fast, horizontally scalable, and easy-to-use hosting.