[Back to LOC CLI Guidebook](https://hackmd.io/4LIsNIuWQnuR70JMeuBAEA?view)
[Setup LOC CLI Environment](https://hackmd.io/igLh4azUT2aI8Fv-q-0e-g)
[Getting Started - JavaScript](https://hackmd.io/IiHvmAtjTTGFfakaH0dWuw?view)
[Getting Started - TypeScript](https://hackmd.io/kz93Th7vTCCbO3GFxp3r-A)
[LOC CLI Commands](https://hackmd.io/R4mrz2t1QSyTCHH73_2itg)
[Local Simple Runtime Execution](https://hackmd.io/JhAMB49rS4CrpNdhHed7YA?view)

# Local Database Testing with Simple Runtime

In Local Simple Runtime Execution, since a LOC data process is running locally, it can access other local services on your machine for testing purposes. However, we can add a database service in the Docker Compose definition as well (which makes it accessible from ```127.0.0.1```). This is also a good way to do test with [database agents](https://hackmd.io/Uj-tC7l9Q82VyGr8R5PL2Q?view#Database).

In this tutorial, we will demonstrate the following operations:

1. Add a DB server to LOC local environment
2. Add a script to automatically initialize the database on start
3. Access the database in a data process

There are currently 3 supported databases:

- [MySQL](#Add-a-Local-MySQL-Service)
- [PostgreSQL](PostgresSQL)
- [MS SQL Server](MS-SQL-Server)

:::info
### Do I Have to Use Docker Compose for Databases?

Not really, you can still install a database service in the conventional way (```localhost:<port>``` can be accessed via ```host.docker.internal:<port>```). For people who don't want to have a bunch of DB services running on all times, using containers is a lot faster and convenient.
:::

## Add a Local MySQL Service

### Modify ```saffron-evenstore.yaml```

Add the following lines at the end of your ```saffron-evenstore.yaml``` (see [Local Simple Runtime Execution](https://hackmd.io/JhAMB49rS4CrpNdhHed7YA?view) for more information):

services:
...

  mysql:
    image: mysql
    restart: "always"
    container_name: mysql
    environment:
      - MYSQL_ROOT_USER=root
      - MYSQL_ROOT_PASSWORD=1234
    volumes:
      - ./sql/mysql-setup.sql:/docker-entrypoint-initdb.d/setup.sql
    ports:
      - "3306:3306"
    networks:
      - saffron

This will add a MySQL server container:

- Container name: ```mysql```
- Port: ```3306```
- Default user: ```root```
- Password: ```1234```

### Add ```/sql/mysql-setup.sql```

Since the database would be empty after startup, you may want to add something first.

Now create a dir ```sql``` and add a file```mysql-setup.sql``` in it. This will be run when the database container starts up at the first time.

The following script will do the following things:

- create a db ```mydb```
- create a table ```policy``` under ```mydb```
- insert three instances of data

CREATE DATABASE mydb;

USE mydb;

CREATE TABLE policy (AcuNo  VARCHAR(20) NOT NULL, Amount INT);

INSERT INTO policy (AcuNo, Amount) VALUES ('Acu-048', 4239);
INSERT INTO policy (AcuNo, Amount) VALUES ('Acu-049', 2022);
INSERT INTO policy (AcuNo, Amount) VALUES ('Acu-050', 9527);

The table is a simple data structure that records accounting data of insurance policies:

| Column | Type | Description |
| ------ | ------ | ---------------------------------- |
| AcuNo | string | Account number of insurance policy |
| Amount | number | Amound (premium) of the policy |

You can replace the content with your own SQL script.

## Startup Database

Open a new terminal under the LOC CLI workspace dir and run the following command:

docker-compose -f saffron-evenstore.yaml up -d

This is *exactly* the same as what we did in [Local Simple Runtime Execution](https://hackmd.io/JhAMB49rS4CrpNdhHed7YA?view) - except that now you have a new container ```mysql``` running along with others and share the same virtual network environment.


### (Optional) Access MySQL Client

You can access the client interface of the MySQL container.

Open a new terminal and enter

docker exec -it mysql mysql -u root -p

MySQL will prompt you to enter the root password (```1234``` in our case). Then you will see

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

You can test SQL commands here. For example, you can check if the table and data in ```mysql-setup.sql``` have been added successfully:

mysql> USE mydb;
mysql> SELECT * FROM policy;

+---------+--------+
| AcuNo   | Amount |
+---------+--------+
| Acu-048 |   4239 |
| Acu-049 |   2022 |
| Acu-050 |   9527 |
+---------+--------+
3 rows in set (0.00 sec)

## Add MySQL Connection Variables in Profile

For safety reasons, we'll put the connection information in the profile ([local.yaml](https://hackmd.io/JhAMB49rS4CrpNdhHed7YA?view#Create-Local-Profile) in our example):

# docker mode (host network)
simpleRuntime:
  image: public.ecr.aws/m0s8j1u6/saffron/simple-runtime:release-0.5.1
  dockerNetwork: host
  eventstoreEndpoint: http://127.0.0.1:8087
  etcdEndpoints: http://127.0.0.1:2379

# mysql connection info
mysql_host: 127.0.0.1
mysql_port: 3306
mysql_username: root
mysql_password: "1234"
mysql_database: mydb

Remember that the port has to be a *number* and the password has to be a *string*. We also set the host as ```127.0.0.1``` so our data process can access the MySQL server without problem.

:::info
If you have a MySQL service installed locally outside the LOC local environment, change ```127.0.0.1``` to ```host.docker.internal``` instead.
:::

Now update your profile:

loc profile set -f local.yaml -p local

If you haven't set the local profile as default, run

loc profile default local

## Query the Database in Data Process

Here we have one single generic logic, which will connect the database ```mydb```, query any rows from the ```policy``` table that has the account number ```Acu-048```:

export async function run(ctx) {

    // import database-related classes
    const database = Saffron.Database;

    // connection info (which are defined in your profile)
    const connectionInfo = {
        host: process.env.mysql_host,  // host.docker.internal
        port: process.env.mysql_port,  // 3306
        database: process.env.mysql_database,  // root
        username: process.env.mysql_username,  // "1234"
        password: process.env.mysql_password,  // mydb
    };

    let db = null;

    try {

        // connect to a MySQL database
        db = await ctx.agents.database.connect({
            databaseDriver: "MySQL",
            connection: new database.MySqlParameters(connectionInfo),
        });

        // SQL query
        const resp = await db.query(
            "SELECT * FROM policy WHERE AcuNo = ?",  // prepared statement
            ['Acu-048']  // parameters (replace the ? in prepared statement)
        );

        // read the returned rows
        const rows = resp?.rows;

        // iterate through the rows and log them
        rows.forEach(row => {
            ctx.agents.logging.info(`Account number: ${row.AcuNo} => $${row.Amount}`);
        });

    } catch (error) {
        
        // log error if something went wrong
        ctx.agents.logging.error(error.message);
        
    } finally {
        
        // close the connection at the end whether or not there were errors
        await db?.disconnect();
        
    }

}

If you execute this in a data process in local runtime, you should see a log output like this:

INFO saffron_process::agent::logger: [execution_id=...,id=...]@d3482d10-11c8-487d-9135-11c03afc2b9a-0 Account number: Acu-048 => $4239

This proves that we've successfully queried the MySQL database.

:::info
### Plain Query vs. Prepared Statement

Of course, you can simply write the query as

const resp = await db.query(
    "SELECT * FROM policy WHERE AcuNo = " + "'Acu-048'",
    []
);

or if the parameter is loaded from somewhere

const accno = body?.accno;  // read from request body

...

const resp = await db.query(
    `SELECT * FROM policy WHERE AcuNo = '${accno}'`,
    []
);

However, this would be dangerous if the parameter itself *contains* SQL commands (++[**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection))++. ++[Prepared statements](https://en.wikipedia.org/wiki/Prepared_statement)++ separates parameters with the raw SQL so any injected SQL won't be executed. This is a safer and widely-accepted good practice.

The parameter syntax may differ depending on the database.
:::

## Insert, Update and Delete Row(s)

// connect to db

...

// add a new policy with account number Acu-051
// but the payment is too low!
await db.execute(
    "INSERT INTO policy (AcuNo, Amount) VALUES (?, ?);",
    ['Acu-051', 99]
);

...

// update the payment to correct amount
await db.execute(
    "UPDATE policy SET Amount = ? WHERE AcuNo = ?;",
    [5000, 'Acu-051']
);

...

// the policy has been written off, delete it
await db.execute(
    "DELETE FROM policy WHERE AcuNo = ?;",
    ['Acu-051']
);

...

// disconnect db

You can check the table at anytime in MySQL client:

mysql> SELECT * FROM policy;

### Remove MySQL from Local Environment

You can stop the container in Docker Desktop anytime.

If you want to remove MySQL from LOC local environment:

1. Delete or comment out (in VS Code select and press ```Ctrl + \```) the MySQL section in ```saffron-evenstore.yaml```
2. Delete local environment containers in Docker Desktop
3. Run ```docker-compose -f saffron-evenstore.yaml up``` again

## Other Supported Databases

### PostgresSQL

1. Modify ```saffron-evenstore.yaml```

service:
...

  postgres:
    image: postgres
    restart: "always"
    container_name: postgres
    environment:
      - POSTGRES_PASSWORD=1234
    volumes:
      - ./sql/postgres-setup.sql:/docker-entrypoint-initdb.d/setup.sql
    ports:
      - "5432:5432"
    networks:
      - saffron

- Container name: ```postgres```
- Port: ```5432```
- Default user: ```postgres```
- Password: ```1234```

2. Add ```/sql/postgres-setup.sql```

CREATE DATABASE mydb;

\c mydb

CREATE TABLE policy (acuno VARCHAR(20) NOT NULL, amount INT);

INSERT INTO policy (acuno, amount) VALUES ('Acu-048', 4239);
INSERT INTO policy (acuno, amount) VALUES ('Acu-049', 2022);
INSERT INTO policy (acuno, amount) VALUES ('Acu-050', 9527);

:::info
Column names in PostgresSQL **are not case sensitive**. Which means even if you set it as ```AcuNo``` in the SQL script, it will still be lower cased ```acuno``` in PostgresSQL *and* in LOC db object.
:::

3. Startup Database

docker-compose -f saffron-evenstore.yaml up -d

4. (Optional) Connect to PostgresSQL client:

docker exec -it postgres psql -U postgres

5. Setup ```local.yaml``` profile

...

# postgres
pgsql_host: 127.0.0.1
pgsql_port: 5432
pgsql_username: postgres
pgsql_password: "1234"
pgsql_database: mydb

Then update it:

loc profile set -f local.yaml -p local

6. Query data

const connectionInfo = {
    host: process.env.pgsql_host,
    port: process.env.pgsql_port,
    database: process.env.pgsql_database,
    username: process.env.pgsql_username,
    password: process.env.pgsql_password,
};

db = await ctx.agents.database.connect({
    databaseDriver: "Postgres",
    connection: new database.PostgresParameters(connectionInfo),
});

...

const resp = await db.query(
    // !!! beware that PostgresDB columns are all lower case!
    "SELECT * FROM policy WHERE acuno = $1",  // prepared statement
    ['Acu-048']  // parameters (replace the $1 in prepared statement)
);

const rows = resp?.rows;

rows.forEach(row => {
    ctx.agents.logging.info(`Account number: ${row.acuno} => $${row.amount}`);
});

### MS SQL Server

MS SQL Server is slightly complicated to setup, but the principle is still the same.

1. Modify ```saffron-evenstore.yaml```

service:
...

  mssql:
    build:
      context: .
      dockerfile: ./sql/mssql-Dockerfile
    restart: "always"
    container_name: mssql
    ports:
      - "1433:1433"
    networks:
      - saffron

2. Add ```/sql/mssql-Dockerfile```

Since the MSSQL container does not have a Docker entrypoint, we'll have to build a custom image with the entrypoint script in it:

# using SQL Server 2019
FROM mcr.microsoft.com/mssql/server:2019-latest

USER root

RUN mkdir -p /usr/config
WORKDIR /usr/config

ENV ACCEPT_EULA Y
ENV MSSQL_PID Express  # running as Express version
ENV SA_PASSWORD MsSql1234  # root user password

COPY ./sql/mssql-entrypoint.sh /usr/config/entrypoint.sh
COPY ./sql/mssql-init.sh /usr/config/init.sh
COPY ./sql/mssql-setup.sql /usr/config/setup.sql 

EXPOSE 1433

RUN chmod +x /usr/config/entrypoint.sh
RUN chmod +x /usr/config/init.sh

CMD ./entrypoint.sh

The ```saffron-evenstore.yaml``` will use this file to build a MSSQL image and run a container based on it:

- Container name: ```postgres```
- Port: ```5432```
- Default user: ```postgres```
- Password: ```1234```

3. Add ```sql/mssql-setup.sql```

CREATE DATABASE mydb;
GO

USE mydb;
GO

CREATE TABLE policy (AcuNo  VARCHAR(20) NOT NULL, Amount INT);
GO

INSERT INTO policy (AcuNo, Amount) VALUES ('Acu-048', 4239);
INSERT INTO policy (AcuNo, Amount) VALUES ('Acu-049', 2022);
INSERT INTO policy (AcuNo, Amount) VALUES ('Acu-050', 9527);
GO

:::info
In MS SQL Server, ```GO``` is required to confirm one or multiple SQL commands. See: ++[SQL Server Utilities Statements - GO](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go?view=sql-server-ver16)++
:::

4. Add ```/sql/mssql-entrypoint.sh```

/usr/config/init.sh & /opt/mssql/bin/sqlservr

5. Add ```/sql/mssql-init.sh```

sleep 20s

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P MsSql1234 -d master -i setup.sql

This script will wait 20 seconds to make sure the DB service is up and running. Then it will run ```sql/mssql-setup.sql``` to initialize the data. Change the time if you encounter problems.

:::info
If you modified anything in the .sh scripts (including the password), you have to delete **both** image and container before re-deploying them; otherwise Docker won't update them.
:::

6. Startup Database

docker-compose -f saffron-evenstore.yaml up -d

4. (Optional) Connect to MSSQL client:

docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P MsSql1234

5. Setup ```local.yaml``` profile

...

# mssql
mssql_host: 127.0.0.1
mssql_port: 1433
mssql_username: sa
mssql_password: "MsSql1234"
mssql_database: mydb

Then update it:

loc profile set -f local.yaml -p local

6. Query data

const connectionInfo = {
    host: process.env.mssql_host,
    port: process.env.mssql_port,
    database: process.env.mssql_database,
    username: process.env.mssql_username,
    password: process.env.mssql_password,
    trustCert: true,  // optional
};

db = await ctx.agents.database.connect({
    databaseDriver: "MSSQL",
    connection: new database.MssqlParameters(connectionInfo),
});

...

const resp = await db.query(
    "SELECT * FROM policy WHERE acuno = @P1",  // prepared statement
    ['Acu-048']  // parameters (replace the @P1 in prepared statement)
);

const rows = resp?.rows;

rows.forEach(row => {
    ctx.agents.logging.info(`Account number: ${row.AcuNo} => $${row.Amount}`);
});

###### tags: `LOC CLI`