How to Create a Database and Blockchain in bSQL
Tags: sql, database, blockchain
Published: 2021-08-23 20:52:01

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](https://www.youtube.com/watch?v=xjVNbdk7sp4&list=PLdRS5wHN77gWuG71IvaH4pX7IdfZYGd06&index=2) online.

You can create a bSQL account through the Blockpoint [website](https://blockpointdb.com/) or join the [slack](https://join.slack.com/t/bsqlcommunity/shared_invite/zt-169oaigpu-R3W1D5_vc6OyNnKy4thNoA) 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9264cgvibmkgsr2d7bsm.png)
 
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](master_database.md), 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](https://bsql.org/docs/database/create-database) command to build a database.

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

````sql
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](https://bsql.org/docs/transaction/use) statement followed by the name of our database. Now *marketplace* is used as the default database
for all subsequent statements.

````sql
USE marketplace;
```

## Create a blockchain

Creating an immutable [Blockchain](https://bsql.org/docs/blockchain-overview) 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](https://bsql.org/docs/management/create-blockchain) documentation for full syntax and keywords.

````sql
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](https://bsql.org/docs/system_data_types). You can read the columns from the database by writing a
[DESCRIBE](https://bsql.org/docs/analytics/describe) statement.

````sql
DESCRIBE marketplace.contracts SCHEMA;
```

We can use an [INSERT](https://bsql.org/docs/mutations/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.

````sql
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](https://bsql.org/docs/mutations/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.

````sql
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](database/check-validity.md) command to 
validate our data digests.

````sql
CHECK VALIDITY;
```

To query from the current state of the database, use a [SELECT](https://bsql.org/docs/database/check-validity) statement.

````sql
SELECT * FROM contracts;
```

Output:
![select_output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ye1p56m7jeg8j3hq4j26.png)
 

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

````sql
SELECT * FROM LIFETIME contracts;
```

Output:
![select_lifetime_output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8eh40f2fauparkw1afaz.png)