DEV Community

Cassidy Mountjoy
Cassidy Mountjoy

Posted on

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 fill out a beta form 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

click-on-open-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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
);
Enter fullscreen mode Exit fullscreen mode

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

DESCRIBE marketplace.contracts SCHEMA;
Enter fullscreen mode Exit fullscreen mode

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
);
Enter fullscreen mode Exit fullscreen mode

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
);
Enter fullscreen mode Exit fullscreen mode

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

CHECK VALIDITY;
Enter fullscreen mode Exit fullscreen mode

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

SELECT * FROM contracts;
Enter fullscreen mode Exit fullscreen mode

Output:
select_output

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

SELECT * FROM LIFETIME contracts;
Enter fullscreen mode Exit fullscreen mode

Output:
select_lifetime_output

Discussion (3)

Collapse
natclark profile image
Nathan

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

Collapse
cassidymountjoy profile image
Cassidy Mountjoy Author

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.

Collapse
natclark profile image
Nathan

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