Stratos Communities Database Upgrade - Utopian.iosteemCreated with Sketch.

in #utopian-io6 years ago

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.

GitHub Account

https://github.com/nicholas-2

Sort:  

Thank you for your contribution @shredz7 !

AFAIK, SQLite is relational DBMS which may not be so scalable. The new way of writing the query e.g. using the Sequelize is in fact very much alike the NoSQL such as MongoDB - which is scalable by its nature.

Maybe you can extract all the database logics into a separate module/class which provides the interface for querying database whether it is sqlite or sequelize - in other words, implementation - dependent.

You could also use async/await to replace your Promise syntax. The DB logging can be also handled separatedly.

How do you ensure the upgrade is safe? I would expect the unit tests that give you some confidences.

Your contribution has been evaluated according to Utopian policies and guidelines, as well as a predefined set of questions pertaining to the category.

To view those questions and the relevant answers related to your post, click here.


Need help? Chat with us on Discord.

[utopian-moderator]

Thanks @justyy! I definitely agree about SQLite, originally Stratos was only going to have to handle things like NFTs and stuff so I was using SQLite because it is easier and there isn't much scalability requirements for just smaller NFTs.

Sequelize does work a lot like mongoose and it is more readable IMHO. I've already extracted the database logic into a specific module, database.js, though there is no need to have seperate code for SQLite and Sequelize as Sequelize supports SQLite already.

Thank you for your review, @justyy! Keep up the good work!

Hi @shredz7!

Your post was upvoted by @steem-ua, new Steem dApp, using UserAuthority for algorithmic post curation!
Your post is eligible for our upvote, thanks to our collaboration with @utopian-io!
Feel free to join our @steem-ua Discord server

(this is @shredz7's test account, accidentally posted while logged in to it)

!tipuvote 5 :)

This post is supported by $2.14 @tipU upvote funded by @cardboard :)
@tipU voting service guide | For investors.

You got a 6.33% upvote from @ocdb courtesy of @shredz7! :)

@ocdb is a non-profit bidbot for whitelisted Steemians, current max bid is 24 SBD and the equivalent amount in STEEM.
Check our website https://thegoodwhales.io/ for the whitelist, queue and delegation info. Join our Discord channel for more information.

If you like what @ocd does, consider voting for ocd-witness through SteemConnect or on the Steemit Witnesses page. :)

Hey, @shredz7!

Thanks for contributing on Utopian.
We’re already looking forward to your next contribution!

Get higher incentives and support Utopian.io!
Simply set @utopian.pay as a 5% (or higher) payout beneficiary on your contribution post (via SteemPlus or Steeditor).

Want to chat? Join us on Discord https://discord.gg/h52nFrV.

Vote for Utopian Witness!

This post has been included in the latest edition of SoS Daily News - a digest of all you need to know about the State of Steem.

Congratulations @shredz7! You received a personal award!

Happy Birthday! - You are on the Steem blockchain for 2 years!

You can view your badges on your Steem Board and compare to others on the Steem Ranking

Vote for @Steemitboard as a witness to get one more award and increased upvotes!

Great work 👍 Keep up the good work. We need more users like you to help our ecosystem grow. I love what you are doing with Stratos, I even made a SteemHunt on it about a week ago.

Posted using Partiko iOS