Using SteemSQL to query Steem database in your SteemJS Application

Gokul N K

Posted on April 6, 2018 - 12:03 pm

SteemJS is still rudimentary and if you start developing some applications you will know that they will fall short. For a bot that I was working on I wanted to know if the user has already posted earlier with the same tag. The case in questions was I wanted to check if the user is misusing the #introduceyourself tag. So before posting a welcome message to the users I wanted to check if he has created more than two posts with the#introduceyourself tag.

What will you learn?

In this tutorial you will learn

How to use SteemSQL in SteemJS code
How to use steem.api.streamTransaction function to do something when a user has created a post.
Using SteemSQL to identify if the user has already create posts with a similar tag.

Requirements

These are good to have requirements. Even if you don't know these you can still read the tutorial and make sense out of it.

Basics of Javascript
Basics of SteemJS

Difficulty

Advanced. You can take a look at the code even if you are not at an advanced level. It might seem a little complicated but it will start making sense to once you cover your basics. I will be happy to answer any queries you have in the comments section.

Add relevant node packages and settings.

In the below script we are adding relevant node packages and their related settings.

We are adding steem package so that we can use steemJS APIs. We are setting that url to wss://steemd-int.steemit.com which is functioning without any issues for me for last two months. We are settings up variables that will be used across the file.

We are adding mssql package as SteemSQL is a MS-SQL database. You can get your SteemSQL credentials by purchasing the subscription as mentioned in the post

var steem = require('steem');
steem.api.setOptions({ url: 'wss://steemd-int.steemit.com' });


const BASEURL = 'https://steemit.com/'
const ACCOUNT_NAME = 'steemladder'

console.log('SteemLadder app has started');

const sql = require('mssql');
const config = {
    user: 'Steemit-gokulnk',
    password: 'you-will-get-this-once-you-subscribe',
    server: 'vip.steemsql.com',
    database: 'DBSteem',
}

Listem to streamTransaction to identify when a comment is posted.

In the below script we are listening streamOperations. Once the streamOperation is emmitted we are getting the txType and txData from the operation. We are then checking if the txType is comment and it has no parent(comments without parents or posts) and has metadata. If it passes all these conditions they we get the tags of the posts from metadata and see if the tags text contains the tags we are looking for.

steem.api.streamOperations(function (err, operations) {
    var txType = operations[0];
    var txData = operations[1];
    var steemDbquery = '';
    if (txType == 'comment' && txData.parent_author == '' &&  txData.json_metadata){
        var metadata = JSON.parse(txData.json_metadata);
        if (metadata) {
            var tags = metadata.tags || [];
            var parentAuthor = txData.author;
            var parentPermlink = txData.permlink;
            var commentPermlink = steem.formatter.commentPermlink(parentAuthor, parentPermlink);

            if (tags.indexOf("introduceyourself") > -1) {

Once it matches all these we now need to check if the user has already posted earlier with the same tag.

Use SteemSQL before running doSomething function

This is the core of the tutorial and we will see how to use the SteemSQL query in steemJS.

In the code below we are checking that the json_metadata contains the work introduce and depth is 0 to make sure that we are querying only the posts and not the comments. Since node.js is asynchronous we will get the response from the DB and we need to use then to process that output. Within the the then function we are checking if we have got more than 2 rows which satisfies our conditions. Including the current post if the user has posted less than or equal to two posts with #introduce tags then we can perform some action like say up-voting their posts.

                    steemDbquery = `SELECT * FROM [dbo].[Comments] where author = '${parentAuthor}' AND json_metadata like '%introduce%' AND DEPTH = 0`;
                    sql.connect(config).then(pool => {
                        console.log('Connected to SteemSQL');
                        return pool.request()
                            .query(steemDbquery)
                    }).then(result => {
                        console.log(result); // Print SQL results to console for debugging                                                                                           
                        if (result.rowsAffected[0] <=2) {
                            doSomething();
                        }
                        sql.close();
                    })

                    sql.on('error', err => {
                        console.log('Unable to connect to DB');
                    })

Helper Functions

Following Helper functions are used to generate links to the authors and blogs.

  function steemBlogFullurl(author, permlink) {
      return BASEURL + '@' + author + '/' + permlink;
  }
  function steemBlogFullurl(author, permlink) {
      return BASEURL + '@' + author + '/' + permlink;
  }
  function steemAuthorFullurl(author) {
      return BASEURL + '@' + author;
  }
  function steemAuthorLink(author) {
    return getLink('@' + author, steemAuthorFullurl(author));
  }
  function getLink(linkText, targetLink) {
    if (typeof linkText == "undefined" || linkText == ''){
      linkText = targetLink;
    }else if (typeof targetLink === "undefined") {
      targetLink = linkText;
    }
    return `<a href = ${targetLink} target="_blank">${linkText}</a>`;
  }

Final notes.

You can checkout the final code from Github.

Curriculum

If you are a developer and would like to kickcstart your learning you can check the following link to set up your dev environments and tools. As I write further tutorials I will add them to the list.