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 Private Key - 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.
- (Optional) Kuneiform VSCode Extension - For syntax highlighting and intellisense.
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).
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.
// 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 Procedures
To define how users can interact with your database, you can define procedures. Procedures contain SQL statements and business logic that are executed when an end user calls the procedure.
Inputs are defined using a $ symbol, followed by the variable name. Contextual values, such as the wallet address calling your procedure, are specified using the @ symbol, followed by the contextual value name.
To learn more about the support SQL syntax, see SQL As Understood By Kwil.
// a public procedure for creating a user
// it will automatically include the wallet address
// of the caller in the "address" column
procedure create_user ($id int, $username text, $age int) public {
INSERT INTO users (id, username, age, address)
VALUES ($id, $username, $age, @caller);
}
// a public procedure for updating mutable user data
procedure update_user ($username text, $age int) public {
UPDATE users
SET username=$username, age=$age
WHERE address=@caller;
}
// a public procedure for creating a post
// it will automatically include the account id
// of the caller in the "author_id" column
procedure create_post ($id int, $title text, $content text, $date_string text) 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 Procedures
Data is read back from procedures using read-only procedures. To specify a read-only procedure, use the view access modifier.
view procedures are not processed in network consensus, and therefore are gasless and return results immediately.
// get_posts will get the posts for a given user.
// It will return many values in a table
procedure get_posts($user_id int) public view returns table(title text, content text, post_date text) {
return SELECT title, content, post_date
FROM posts
WHERE author_id=$user_id;
}
// procedure get_user will get a user by their username.
// It will return exactly one value, and error if no user is found.
procedure get_user($username text) public view returns (username text, age int, address text) {
// we iterate over the sql results and return the first row
for $row in SELECT username, age, address
FROM users WHERE username=$username {
return $row.username, $row.age, $row.address;
}
// if no user is found, we raise an error
ERROR('user not found');
}
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 procedure for creating a user
// it will automatically include the wallet address
// of the caller in the "address" column
procedure create_user ($id int, $username text, $age int) public {
INSERT INTO users (id, username, age, address)
VALUES ($id, $username, $age, @caller);
}
// a public procedure for updating mutable user data
procedure update_user ($username text, $age int) public {
UPDATE users
SET username=$username, age=$age
WHERE address=@caller;
}
// a public procedure for creating a post
// it will automatically include the account id
// of the caller in the "author_id" column
procedure create_post ($id int, $title text, $content text, $date_string text) public {
INSERT INTO posts (id, title, content, author_id, post_date)
VALUES ($id, $title, $content, (
SELECT id
FROM users
WHERE address=@caller
), $date_string);
}
// get_posts will get the posts for a given user.
// It will return many values in a table
procedure get_posts($user_id int) public view returns table(title text, content text, post_date text) {
return SELECT title, content, post_date
FROM posts
WHERE author_id=$user_id;
}
// procedure get_user will get a user by their username.
// It will return exactly one value, and error if no user is found.
procedure get_user($username text) public view returns (username text, age int, address text) {
// we iterate over the sql results and return the first row
for $row in SELECT username, age, address
FROM users WHERE username=$username {
return $row.username, $row.age, $row.address;
}
// if no user is found, we raise an error
ERROR('user not found');
}
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/to/social_media.kf --provider='https://longhorn.kwil.com' --chain-id 'longhorn-2' --private-key 'your-private-key' --sync
TxHash: 53018a450101d07f096e767fc6bc11374addf7f5a5a9788d8ad69ed6f1a24f49
Status: success
Height: 587740
Log: success