The Problem

Web3 has been all the rave recently. The key component of Web3 is the use of Smart Contracts that are built on top of Ethereum's network. The problem is there is no good way to learn about writing smart contracts or to look at other people's smart contracts so you can learn from them. For that reason, I created Smart Contract School.

The Solution

My solution to the problem was to create a database that would handle a series of challenges in creating smart contracts. Users can select any challenge and create a solution.

Users will be able to review all solutions for any given challenge. They can use the solutions as a tool to learn how to create smart contracts or they can use it to find a solution for their own smart contract if they get stuck.

Frontend and Backend Servers

This project has both frontend and backend servers. The frontend server is what users will interact with to see challenges and solutions.

For example, if a person wants to see all the available challenges this is what they will see:

The code for the frontend server is here.

The backend server handles all API calls. These API calls will be used for example to get a list of all challenges or the details for a specific challenge. The backend server is what will interact with my database tables in PlanetScale.

For the above example, here are the calls the backend API server makes to retrieve all the solutions in my PlanetScale database as well as the call to retrieve the details for a specific challenge.

The code for the backend server is here.

Database Design

Smart Contract School utilizes multiple database tables with relationships between them. This design is the epitome of a relational database. A relational database is a type of database that stores and provides access to data points that are related to one another.

By spending some time initially on the design of the tables that will be used to store the data, I was able to identify the relationships that exist between different entities or groups of data.

For example, I can create a challenge. When users submit their solution to the challenge there needs to be a way to tie that solution to the correct challenge. Also, there needs to be a way to tie a solution to the user that created it.

Here is the layout of the database schema.

Seeding The Database

Now that I have the design of the database done, I need to actual seed the database with data. Luckily, PlantScale has a great tutorial on building an API that provides detailed steps on how to seed a database.

I followed this tutorial and was able to build scripts that would do the following:

• drop all the tables
• create the tables
• import data into each table

I have the code to seed the databases in my backend server in a folder called seed. This folder contains two files: seed.js and sql.js.

The sql.js file contains all the scripts to drop tables, create new tables and insert data into the database. Here is the content of that file

export const dropChallengeTableSQL = 'DROP TABLE IF EXISTS challenge';
export const dropSolutionTableSQL = 'DROP TABLE IF EXISTS solution';
export const dropUserTableSQL = 'DROP TABLE IF EXISTS user';
export const dropResourceTableSQL = 'DROP TABLE IF EXISTS resource';

export const insertChallengeSQL = 'INSERT INTO challenge (title, image_url, image_alt, descrShort, descrLong, difficulty, requirements, tags, learning) VALUES ?';
export const insertSolutionSQL = 'INSERT INTO solution (gist_url, author) VALUES ?';
export const insertUserSQL = 'INSERT INTO user (username) VALUES ?';
export const insertResourceSQL = 'INSERT INTO resource (title, descr, image_url, image_alt, category, url) VALUES ?';

export const createChallengeTableSQL = `CREATE TABLE challenge (
    challenge_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(240) NOT NULL,
    image_url VARCHAR(240),
    image_alt VARCHAR(240),
    descrShort VARCHAR(480),
    descrLong VARCHAR(480),
    difficulty VARCHAR(40) NOT NULL,
    requirements VARCHAR(480),
    tags VARCHAR(240),
    learning VARCHAR(240),
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (challenge_id),
    CONSTRAINT chk_difficulty CHECK (difficulty IN ('Beginner', 'Intermediate', 'Advanced'))
)`;

export const createSolutionTableSQL = `CREATE TABLE solution (
    solution_id INT NOT NULL AUTO_INCREMENT,
    gist_url VARCHAR(240) NOT NULL,
    votes INT DEFAULT 0,
    author VARCHAR(40) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    cid INT,
    uid INT,
    PRIMARY KEY (solution_id),
    KEY challenge_idx (cid),
    KEY user_idx (uid)
)`;

export const createUserTableSQL = `CREATE TABLE user (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(40) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id)
)`;

export const createResourcesTableSQL = `CREATE TABLE resource (
    resource_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(240) NOT NULL,
    descr VARCHAR(1240) NOT NULL,
    image_url VARCHAR(240) NOT NULL,
    image_alt VARCHAR(240) NOT NULL,
    category VARCHAR(40) NOT NULL,
    url VARCHAR(240) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (resource_id),
    CONSTRAINT chk_category CHECK (category IN ('Video', 'Article', 'Community', 'DAO'))
)`;

The seed.js file is the script that will actually seed the database. It will use the SQL code contained in the sql.js file. Here are the contents of that file:

import { createChallengeTableSQL, createSolutionTableSQL, createUserTableSQL, createResourcesTableSQL, dropChallengeTableSQL, dropSolutionTableSQL, dropUserTableSQL, dropResourceTableSQL, insertChallengeSQL, insertResourceSQL } from './sql.js';
import mysql from 'mysql2/promise';
import dotenv from 'dotenv';
dotenv.config();

const connection = await mysql.createConnection(process.env.DATABASE_URL);

const loadAndSaveData = async () => {
    try {
        /*
         * DROP ALL TABLES
         */
        await connection.query(dropChallengeTableSQL);
        console.log('***dropped challenge table***');

        await connection.query(dropSolutionTableSQL);
        console.log('***dropped solution table***');

        await connection.query(dropUserTableSQL);
        console.log('***dropped user table***');

        await connection.query(dropResourceTableSQL);
        console.log('***dropped resource table***');

        /*
         * CREATE NEW TABLES
         */
        await connection.query(createChallengeTableSQL);
        console.log('***created challenge table***');

        await connection.query(createUserTableSQL);
        console.log('***created user table***');

        await connection.query(createSolutionTableSQL);
        console.log('***created solution table***');

        await connection.query(createResourcesTableSQL);
        console.log('***created resource table***');

        /*
         * SEED NEW TABLES
         */
        const challenges = [
            ['Hello World',
                'https://res.cloudinary.com/ratracegrad/image/upload/v1658878203/HelloWorld_ojsk4f.png',
                'Hello World Smart Contract',
                'In this challenge you will create your first smart contract. This contract will display the words Hello World.',
                'In this challenge you will create your first smart contract. This contract will display the words Hello World. You can get started using the Remix IDE that you can load into your browser. The goal of this challenge is to write your very first smart contract.',
                'Beginner',
                'Should display the text Hello World when user clicks a button;To increase difficulty allow users to input a name and then display Hello <Name>;Another option is to display Hello World unless the user inputs a name',
                'Beginner',
                ''],
            ['AirBnb Clone',
                'https://res.cloudinary.com/ratracegrad/image/upload/v1658878203/AirbnbClone_kyt7tp.png',
                'AirBnb Smart Contract',
                'In this challenge you will create the basic functionality provided by AirBnb by registering properties and renting them out.',
                'In this challenge you will create the basic functionality provided by AirBnb by registering properties and renting them out. You will need to provide the ability for people to register as landlords. Once registered they can list their properties. Anyone visiting website will be able to rent any property that is available for the dates specified.',
                'Advanced',
                'Allow landlords to register with your contract;Landlords can list their properties;Landlords can provide descriptions for their property;Users can view available properties;Users can rent a property',
                'Advanced',
                ''],
            ['Who is the Owner?',
                'https://res.cloudinary.com/ratracegrad/image/upload/v1659007211/Owner_potwsm.png',
                'Who is the Owner?',
                'In this challenge you will create a smart contract that will return the owner and let you set the owner.',
                'In this challenge you will create a smart contract that will return the owner and let you set the owner. Every smart contract is owned by an address called as owner. In this contract you will set the owner. If anybody wants to know who is the owner then you can return that information to them.',
                'Intermediate',
                'Use private variable to store owner;Use the address modifier;Check if the caller is the owner;Set the owner when contract is deployed;Be able to change the owner;Return the owner',
                'Intermediate',
                ''],
            ['Ballot Box',
                'https://res.cloudinary.com/ratracegrad/image/upload/v1659006987/ballot_r9cpkb.png',
                'Ballot Box',
                'In this challenge you will create a smart contract that will allow the creation of proposals and then users vote on them.',
                'In this challenge you will create a smart contract that will allow the creation of proposals and then users vote on them. Anybody should be allowed to create a proposal. Users will be allowed to vote on those proposals. The personal who creates the proposal will get 2 votes and everyone else gets a single vote. Voters can delegate their vote to somebody else to vote for them. After vote is over the winning proposal is announced.',
                'Advanced',
                'Use structs to create Voter and Proposal;Create a public variable for the Chairperson;Give users the right to vote;Allow users to delegate their vote to other approved voters;Chairperson gets 2 votes;All users get one vote;Return the winning proposal',
                'Advanced',
                ''],
            ['Vending Machine Smart Contract',
                'https://res.cloudinary.com/ratracegrad/image/upload/v1659009520/VendingMachine2_fzn1ty.png',
                'Vending Machine Smart Contract',
                'In this challenge you will create a vending machine smart contract that will allow users to purchase items from a vending machine.',
                'In this challenge you will create a vending machine smart contract that will allow users to purchase items from a vending machine. You will have to provide the ability for users to purchase items from the vending machine. You will also have to provide the ability for the vending machine to track the inventory of items.',
                'Intermediate',
                'Create an inventory of products;Allow users to purchase a product;Keep track of available inventory;Notify user if item is out of stock',
                'Intermediate',
                ''],
            ['Random Number',
                'https://res.cloudinary.com/ratracegrad/image/upload/v1659007585/RandomNumber_pdxnoz.png',
                'Random Number',
                'In this challenge you will create a smart contract that will generate a random number using Keccak256.',
                'In this challenge you will create a smart contract that will generate a random number using Keccak256. There is not an easy way to generate random numbers in smart contracts so you will be creating your own.',
                'Beginner',
                'Use two smart contracts;Use block difficult, nonce and sender address t0 create random number;Return the random number',
                'Beginner',
                ''],
        ];
        await connection.query(insertChallengeSQL, [challenges]);
        console.log('***challenges saved***');

        const resources = [
            ['Buildspace', 'Home to the world\'s best web3 builders. Buildspace accelerates your builder journey into web3.', 'https://res.cloudinary.com/ratracegrad/image/upload/v1658083173/Screen_Shot_2022-07-17_at_2.39.22_PM_lw1fkr.png','Buildspace','Community','https://buildspace.so/'],
            ['Learn Blockchain & Solidity', 'This course will give you a full introduction into all of the core concepts', 'https://res.cloudinary.com/ratracegrad/image/upload/v1658082951/BlockchainSoliditySmartContracts_n4bmfi.webp', 'Learn Blockchain & Solidity','Video','https://www.youtube.com/watch?v=gyMwXuJrbJQ'],
            ['Solidity in 2 Hours', 'Learn to program in Solidity in the full tutorial. Solidity is an object-oriented programming language for writing smart contracts. It is used for implementing smart contracts on various blockchain platforms, most notably, Ethereum.', 'https://res.cloudinary.com/ratracegrad/image/upload/v1658082951/SolidityIn2Hours_mkawzt.webp', 'Solidity in 2 Hours','Video','https://www.youtube.com/watch?v=ipwxYa-F1uY'],
            ['Developer DAO', 'Developer DAO exists to accelerate the education and impact of a new wave of web3 builders.', 'https://res.cloudinary.com/ratracegrad/image/upload/v1658083577/Screen_Shot_2022-07-17_at_2.46.06_PM_suf7k6.png', 'Developer DAO','DAO','https://www.developerdao.com/'],
            ['Create Your First Smart Contract', 'If you ever wanted to get into smart contract development with Solidity, then this article is for you. Well, even if you never heard of Solidity and smart contracts before, following this article might perhaps inspire you to dig a little deeper and perhaps even create your own crypto coin or DEX.', 'https://res.cloudinary.com/ratracegrad/image/upload/v1658083715/createFirstSmartContract_bqvwhq.webp', 'Create Your First Smart Contract','Article','https://dev.to/oliverjumpertz/how-to-set-up-a-solidity-project-and-create-your-first-smart-contract-4e9d'],
            ['Create Tests For Your Smart Contract', 'You have created your first smart contract, now learn how to write tests for it.', 'https://res.cloudinary.com/ratracegrad/image/upload/v1658083811/CreateTests_sa7adf.webp', 'Create Tests For Your Smart Contract','Article','https://dev.to/stermi/how-to-create-tests-for-your-solidity-smart-contract-2238'],
        ];

        await connection.query(insertResourceSQL, [resources]);
        console.log('***resources saved***');

    } catch (error) {
        console.log(error);
    }
}

await loadAndSaveData();
process.exit(0);

To actually seed the database, all I had to do was to run this command:

node seed/seed.js

When I run this command, the console.log entries will be displayed like this:

***dropped challenge table***
***dropped solution table***
***dropped user table***
***dropped resource table***
***created challenge table***
***created user table***
***created solution table***
***created resource table***
***challenges saved***
***resources saved***

Tech Stack

The tech stack that I used to create Smart Contracts School is:

• React.js - frontend design
• JavaScript - frontend and backend servers
• Node.js - backend API
• PlanetScale - cloud-based SQL tables
• Tailwind CSS - CSS styling
• Firebase - authentication using GitHub
• Vercel - deployment of completed application

Live Site

I have deployed both my frontend server and backend server on Vercel. I have a custom URL that I used to point to my frontend server deploy on Vercel.

Please check out my Smart Contracts School website here.

Future Features

In the future, I will add the capability for users to submit solutions to the challenges. These solutions will be displayed for anyone to view. Solutions will have to be a link to a GitHub gist containing the code for the solution.

Conclusion

I really enjoyed participating in this Hackathon hosted by Hashnode and PlanetScale. It is amazing to see the capabilities that PlanetScale provides. It was incredibly easy to create an account and start building the databases that I needed for this project. I especially relished the fact that I was able to create these databases and access them without having to be an except at SQL or setting up server.