How to Create a Database and Blockchain in bSQL

In this tutorial we will be introducing how to set up a new database, blockchain, and perform your first mutation. If you
prefer to learn in a video format, you can watch the tutorial online.

You can create a bSQL account through the Blockpoint website or join the slack for direct assistance from an engineer.

We will be:

1. Creating a new database
2. Starting a session on that database
3. Creating a blockchain
4. Populating your blockchain
5. Reading from your blockchain

Opening the IDE

After selecting the "open IDE button" you will automatically navigate to the IDE. A few things are important to note:

• You will need to provide your username and password to connect to bSQL
• Once you connect, a session will be started on the master database, a hub for system metadata where work and application logic should not be performed

Creating a database

Once you have the master database open in the IDE we can run a CREATE DATABASE command to build a database.

I will be calling mine marketplace, and will create a new database by running the following statement:

CREATE DATABASE marketplace;

Start a session on the database

After we have created a database, it is bSQL convention to begin a session on our working database. To do this we run a
USE statement followed by the name of our database. Now marketplace is used as the default database
for all subsequent statements.

USE marketplace;

Create a blockchain

Creating an immutable Blockchain is as simple as creating a table in other SQL databases. There are four types
of blockchain in bSQL. Before defining the schema, we specified that the blockchian would be of type TRADITIONAL.
Reference the CREATE BLOCKCHAIN documentation for full syntax and keywords.

CREATE BLOCKCHAIN contracts TRADITIONAL (
    id UINT64 PRIMARY,
    unique_identifier STRING PACKED,
    issuer STRING PACKED,
    owner STRING PACKED,
    currency STRING PACKED,
    amount FLOAT32
);

Data types are explained in data types. You can read the columns from the database by writing a
DESCRIBE statement.

DESCRIBE marketplace.contracts SCHEMA;

We can use an INSERT statement to add immutable data to our blockchain. Insert a new contract into the blockchain we
created by running the following statement. Once data is added, it can't be modified or illicitly changed.

INSERT contracts VALUES (
    0,
    "25c3d24f-0fc3-4a99-a292-98302dc0b5d0",
    "bank",
    "joe",
    "USD",
    120.0
);

If we want to change values in bSQL we can write an AMEND statement. It is important to note that
this statement doesn't modify existing values, only updates the current version relative to the primary key.

AMEND contracts VALUES (
    0,
    "36e55e77-3ef2-4d8c-9f74-58895f4193b5",
    "joe",
    "kate",
    "USD",
    120.0
);

To prove that data hasn't been illicitly modified we can run a CHECK VALIDITY command to
validate our data digests.

CHECK VALIDITY;

To query from the current state of the database, use a SELECT statement.

SELECT * FROM contracts;

Output:

Additionally, we can access the record history of the blockchain by applying the LIFETIME keyword to the contracts blockchain.

SELECT * FROM LIFETIME contracts;

Output:

Comments

[Nathan]

This looks like it could be very useful for immutable server logs.

[Cassidy Mountjoy]

That's an interesting use case, I haven't explored that yet. These queries are actually from a DAML demo application I wrote. Immutable, in-memory smart contracts persisted to an immutable db. The logic aligned very nicely.

[Nathan]

I don't think I've ever seen DAML before, but it looks really interesting! Thanks for sharing it.