Develop a Hive Curator Tool (a.k.a Advanced Search) with Svelte and Hivesql

I always need a tool to help me curate content on Hive. There was a tool called Steemlookup developed by team @curie, but it's no longer maintained. If I remember correctly, that tool was built using Node.js with PostgreSQL. I also noticed that @geekgirl developed a similar tool using Python and @hivesql by @arcange. Geekgirl’s Hive search tool was hosted on Heroku, but as of now, it's no longer available there.

The purpose of these tools isn't to turn you into a best curator like @alcibiades, but rather to find content on Hive and filter it using several parameters.

I don't know Python, and hosting a large PostgreSQL database can be expensive. So, I decided to make one myself, using SvelteKit and Hivesql. Thanks to the proposal, Hivesql is now free to use; you just need to sign up by transferring 1 HBD.

I don't plan to host this tool on Vercel or similar services, I just use it locally. But I've pushed the repository to GitHub. So, if you want to use it or fork it, you can simply clone the repo, run npm install, and then npm run dev as needed.

Now, for the development story.

I chose SvelteKit because it's easy to use and fast. I just needed to learn about SQL since I hadn't worked with it before.

The idea of Hivesql is to get data from the Hive Blockchain and store it as an MSSQL database. It offers more flexible queries than manually fetching data with a specific API.

Here is a basic query example:

    SELECT 
        c.title,
        FLOOR(a.reputation_ui) AS reputation,
        c.author,
        c.url,
        c.created,
        c.pending_payout_value,
        c.json_metadata,
        c.body_length,
        CASE 
            WHEN cm.title IS NOT NULL THEN cm.title
            ELSE '#' + c.category
        END AS displaycategory
    FROM 
        Comments c
    LEFT JOIN 
        Communities cm ON c.category = cm.name
    JOIN 
        Accounts a ON c.author = a.name
    WHERE 
        c.depth = 0
        AND c.allow_curation_rewards = 1 
        AND ISJSON(c.json_metadata) = 1
        ${searchTermCondition}
        ${bodyLengthCondition}
        ${authorExcludeCondition}
        ${authorCondition}
        ${tagsCondition}
        ${excludeUpvotedByCondition}
        AND EXISTS (
            SELECT 1 
            FROM OPENJSON(c.body_language)
            WITH (
                language NVARCHAR(50) '$.language',
                isReliable BIT '$.isReliable'
            ) 
            WHERE language = @language AND isReliable = 1
        )
        ${excludeTagsCondition}
        ${payoutWindowCondition}
        ${payoutCondition}
    ORDER BY 
        c.created DESC
    OFFSET @offset ROWS
    FETCH NEXT @pageSize ROWS ONLY;

Since we are using SvelteKit (or any other JavaScript frameworks), we can make the query parameters dynamic. This way, users can select options from the UI, and the backend will process the input to provide desirable results.

We can achieve this by storing the states from user selections and reflecting those states to the SQL query. My idea was also saving those states in local storage so the parameters persist in the browser as long as the user doesn't clear the session. Storing state in Svelte is fairly easy and intuitive, similar to useState in React.

My goals with this tool are to enable searching for content with parameters like languages (as I'll be looking for Indonesian and English content), excluding certain tags, setting a minimum word length, excluding accounts flagged by Spaminator, etc. I also add a UI frontend selections since I know my fellow curators love to use their own personal favorite UI (peakD, inleo, Ecency, Hive condenser, etc)

If you find this tool useful, as mentioned earlier, you can clone the repository to your local machine. Then, create an .env file containing your Hivesql database configuration.

DB_USER=Your-Username
DB_PASSWORD=Yourpassword
DB_SERVER=vip.hivesql.io
DB_DATABASE=DBHive
PORT=3000

You'll need Node.js installed on your machine and a Hivesql subscription. You can also modify the language options and UI frontend selections from the config file.

export const languageOptions = [
    { value: 'en', label: 'English' },
    { value: 'id', label: 'Indonesian' }
    // add more languages
];

export const UIOptions = [
    { value: 'https://peakd.com', label: 'PeakD' },
    { value: 'https://hive.blog', label: 'Hive Blog' }
    // add more UI frontend
];

export let selectedValue = 'en';
export let selectedUIValue = 'https://peakd.com';