Create a Database

In this tutorial, we will create a database using Kuneiform. We will be deploying the database using the Kwil CLI, however you can also deploy your database using the Kuneiform IDE.

Prerequisites

• Kwil CLI - Download from the Kwil Binary Releases.
• Ethereum Wallet - You will need an Ethereum private key to sign the transaction.
• Testnet Tokens - Get testnet tokens from the Kwil Faucet for your associated Ethereum address.

Naming the Database

Schemas are named using a database declaration at the top of the Kuneiform file. Database names are unique to the account that deployed them (i.e. you cannot have two databases with the same name deployed by the same account).

social_media.kf

database social_media;

Defining Tables

Tables are defined using a table declaration. Tables are named using table declarations under the database declaration. Table names are unique to the database that contains them.

social_media.kf

// table for storing application users
table users {
    id int primary,
    username text notnull unique minlen(5) maxlen(32),
    age int notnull max(120),
    address text notnull unique // user wallet addresses
}

// table for storing posts
table posts {
    id int primary,
    title text notnull maxlen(300),
    content text notnull maxlen(10000),
    author_id int notnull,
    post_date text notnull
}

Using Actions

To define how users can interact with your database, you can define actions. Actions contain SQL statements that are executed when an end user calls an action.

Inputs are defined using a $ symbol, followed by the variable name. Contextual values, such as the wallet address calling your action, are specified using the @ symbol, followed by the contextual value name.

To learn more about the support SQL syntax, see Kuneiform's supported DML.

social_media.kf

// a public action for creating a user
// it will automatically include the wallet address
// of the caller in the "address" column
action create_user ($id, $username, $age) public {
    INSERT INTO users (id, username, age, address)
    VALUES ($id, $username, $age, @caller);
}

// a public action for updating mutable user data
action update_user ($username, $age) public {
    UPDATE users
    SET username=$username, age=$age
    WHERE address=@caller;
}

// a public action for creating a post
// it will automatically include the account id
// of the caller in the "author_id" column
action create_post ($id, $title, $content, $date_string) public {
    INSERT INTO posts (id, title, content, author_id, post_date)
    VALUES ($id, $title, $content, (
        SELECT id
        FROM users
        WHERE address=@caller
    ), $date_string);
}

Creating Read-Only Actions

Data is read back from actions using read-only actions. To specify a read-only action, use the view access modifier. view actions are not processed in network consensus, and therefore are gasless and return results immediately.

social_media.kf

// action get_user will get a user by their username
action get_user ($username) public view {
    SELECT *
    FROM users
    WHERE username=$username;
}

// get_posts will get the posts for a given user
action get_posts ($user_id) public view {
    SELECT *
    FROM posts
    WHERE author_id=$user_id;
}

Full Example

The full Kuneiform schema we created can be found in the dropdown below:

social_media.kf

database social_media;

// table for storing application users
table users {
    id int primary,
    username text notnull unique minlen(5) maxlen(32),
    age int notnull max(120),
    address text notnull unique // user wallet addresses
}

// table for storing posts
table posts {
    id int primary,
    title text notnull maxlen(300),
    content text notnull maxlen(10000),
    author_id int notnull,
    post_date text notnull
}

// a public action for creating a user
// it will automatically include the wallet address
// of the caller in the "address" column
action create_user ($id, $username, $age) public {
    INSERT INTO users (id, username, age, address)
    VALUES ($id, $username, $age, @caller);
}

// a public action for updating mutable user data
action update_user ($username, $age) public {
    UPDATE users
    SET username=$username, age=$age
    WHERE address=@caller;
}

// a public action for creating a post
// it will automatically include the account id
// of the caller in the "author_id" column
action create_post ($id, $title, $content, $date_string) public {
    INSERT INTO posts (id, title, content, author_id, post_date)
    VALUES ($id, $title, $content, (
        SELECT id
        FROM users
        WHERE address=@caller
    ), $date_string);
}

action get_user ($username) public view {
    SELECT *
    FROM users
    WHERE username=$username;
}

// get_posts will get the posts for a given user
action get_posts ($user_id) public view {
    SELECT *
    FROM posts
    WHERE author_id=$user_id;
}

Deploying the Database

To deploy the database, we will use the Kwil CLI. To deploy the database, run the following command. Make sure to replace ./path/to/social_media.kf with the path to your Kuneiform file and your-private-key with your private key.

$ kwil-cli database deploy --path=./path/to/social_media.kf --kwil-provider='https://longhorn.kwil.com' --chain-id 'longhorn' --private-key 'your-private-key' --sync
TxHash: 53018a450101d07f096e767fc6bc11374addf7f5a5a9788d8ad69ed6f1a24f49
Status: success
Height: 587740
Log: success