Mycelial: Getting started with a local Control Plane

In this tutorial you will move data from a SQLite database to a Postgres database using Mycelial but hosting your own local Control Plane.

Prerequisites

Installing SQLite

Install SQLite on your system as described below:

Mac

brew install sqlite

Linux

Ubuntu/Debian-based systems

sudo apt-get install sqlite3

Fedora

sudo dnf install sqlite

CentOS/Redhat 7

sudo yum install sqlite

CentOS/Redhat 8+

sudo dnf install sqlite

Installing Docker

Follow the instructions here to install docker on your system.

Install the Mycelial CLI

Follow the below instructions to install the Mycelial CLI:

Mac

brew install mycelial/tap/mycelial

Linux

Debian Based Linux x86_64

curl -L https://github.com/mycelial/cli/releases/latest/download/mycelial-v-1.x86_64.deb --output mycelial_amd64.deb
dpkg -i ./mycelial_amd64.deb

Debian Based Linux ARM64

curl -L https://github.com/mycelial/cli/releases/latest/download/mycelial-v-1.arm64.deb --output mycelial_arm64.deb
dpkg -i ./mycelial_arm64.deb

Debian Based Linux ARM

curl -L https://github.com/mycelial/cli/releases/latest/download/mycelial-v-1.armhf.deb --output mycelial_armhf.deb
dpkg -i ./mycelial_armhf.deb

Redhat Based Linux x86_64

yum install https://github.com/mycelial/cli/releases/latest/download/mycelial-v-1.x86_64.rpm 

Redhat Based Linux ARM64

yum install https://github.com/mycelial/cli/releases/latest/download/mycelial-v-1.arm64.rpm 

Redhat Based Linux ARM

yum install https://github.com/mycelial/cli/releases/latest/download/mycelial-v-1.armhf.rpm

Create a demo directory

mkdir mycelial-demo
cd mycelial-demo

Creating the source SQLite database

In the command line, open sqlite:

sqlite3 source.db

Create a new users table with the following command:

CREATE TABLE users(id integer primary key, name text) STRICT;

Insert a new row in the users table:

INSERT INTO users(name) VALUES ('James');

Exit the SQLite command line program:

.exit

Setup Postgres

Start Postgres in a docker container with the following command:

docker run --name postgres-db -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres

Enter the container and run the psql application with the following command:

docker exec -it postgres-db psql -U postgres

Create a new database with the following command:

CREATE DATABASE tutorial;

Exit out of the psql application with the following command:

\q

Download and start the Mycelial Control plane (server) and daemon

Run the following Mycelial CLI command:

mycelial init --local

Running the above command will download both the control plane (server) and the daemon then you will be prompted with a series of questions. Press enter (⏎) to accept the default values as shown below:

? Daemon Name: (My Daemon)› ⏎
? Daemon ID: (daemon)› ⏎
? Control Plane: (http://localhost:7777) › ⏎

When prompted for the token, enter token:

? Security Token: › token ⏎

When prompted with What would you like to do?, press the down arrow to highlight Add Source and press enter.

? What would you like to do? ›
❯ Add Source
  Add Destination
  Exit

When prompted with What type of source would you like to add?, press the down arrow to highlight Append only SQLite source and press enter.

? What type of source would you like to add? ›
❯ Append only SQLite source ⏎
  Excel source
  Append only Postgres source
  Append only MySQL source
  File source
  Cancel

When prompted for the Display Name press enter to accept the default name.

? Display name: (SQLite Append Only Source) › ⏎

When prompted for the Tables press enter to accept the default value.

? Tables: (*) › ⏎

When prompted for the Database Path, enter source.db.

? Database Path: (data.db) › source.db ⏎

When prompted with What would you like to do?, press the down arrow key to highlight Add Destination then press enter.

? What would you like to do? ›
  Add Source
❯ Add Destination
  Exit

When prompted with What type of destination would you like to add?, press the down arrow to hightlight Append only Postgres destination and press enter.

? What type of destination would you like to add? ›
  Append only SQLite destination
❯ Append only Postgres destination ⏎
  Append only MySQL destination
  Kafka destination
  Snowflake destination
  File destination
  Cancel

When prompted with Display name, press enter to accept the default.

? Display name: (Postgres Append Only Destination) › ⏎

When prompted with Postgres username, enter postgres

? Postgres username: (user) > postgres ⏎

When prompted with Postgres password, enter secret

? Postgres password: secret ⏎

When prompted with Server address, press enter to accept the default.

? Server address: (127.0.0.1) › ⏎

When prompted with Postgres port, press enter to accept the default.

? Postgres port: (5432) › ⏎

When prompted with Database name, enter tutorial.

? Database name: (db) › tutorial ⏎

When prompted with What would you like to do?, press the down arrow to hightlight Exit and press enter.

? What would you like to do? ›
  Add Source
  Add Destination
❯ Exit ⏎

After Exiting the mycelial CLI, a config.toml file is created.

Now start both the daemon and control plane by running the command:

mycelial start ⏎

When prompted for the Security token enter token.

? Security Token: › token ⏎

Next, you'll open up the control plane web interface at http://localhost:7777.

When prompted to sign in, enter token in the username field and leave the password field blank, then press enter.

When prompted with a workspace, click on the Default workspace.

Now you'll need to create workflow by doing the following steps:

1. Drag and drop the SQLite Append Only Source node onto the canvas.
2. Drag and drop the Mycelial Server node onto the canvas.
3. Drag and drop the Postgres Append Only Destination node onto the canvas.
4. Connect the SQLite Append Only Source to the Mycelial Server and then connect the Mycelial Server to the Postgres Append Only Destination node.
5. Lastly, press the Publish button to start the workflow.

At this point, the users table from the SQLite source database should be synchronized with the Postgres destination database.

You can verify the users table exists in the postgres database by running the following commands.

docker exec -it postgres-db psql -U postgres

When you see the postgres=# prompt, enter \c tutorial to start using the tutorial database.

To display the tables enter \dt, and you should see the users table listed.

To query the users table, enter the following SQL statement:

select * from users;

After running the above command, you should see user named James.

Enter the below command to exit out of the psql CLI tool.

\q

Cleanup

Enter the following command to terminate the control plane and the daemon:

mycelial destroy

Run the following commands to stop and remove the Postgres database:

docker stop postgres-db
docker rm postgres-db