MySQL

Connect Brokoli to MySQL for reading and writing data.

Connection config

Field	Value	Example
`type`	`mysql`
`host`	Hostname or IP	`mysql.example.com`
`port`	Port (default: 3306)	`3306`
`schema`	Database name	`myapp`
`login`	Username	`etl_user`
`password`	Password	`secret123`

Create via API

curl -X POST http://localhost:8080/api/connections \
  -H "Content-Type: application/json" \
  -d '{
    "conn_id": "prod-mysql",
    "type": "mysql",
    "host": "mysql.example.com",
    "port": 3306,
    "schema": "myapp",
    "login": "etl_user",
    "password": "secret123"
  }'

URI format

Brokoli constructs the MySQL DSN internally:

etl_user:secret123@tcp(mysql.example.com:3306)/myapp

Reading data (source_db)

{
  "type": "source_db",
  "config": {
    "conn_id": "prod-mysql",
    "query": "SELECT id, name, email FROM users WHERE active = 1"
  }
}

Writing data (sink_db)

Insert

{
  "type": "sink_db",
  "config": {
    "conn_id": "prod-mysql",
    "table": "dim_users",
    "mode": "insert"
  }
}

Upsert

MySQL upsert uses INSERT ... ON DUPLICATE KEY UPDATE:

{
  "type": "sink_db",
  "config": {
    "conn_id": "prod-mysql",
    "table": "dim_users",
    "mode": "upsert",
    "conflict_key": "user_id"
  }
}

Migrations

{
  "type": "migrate",
  "config": {
    "conn_id": "prod-mysql",
    "sql": "CREATE TABLE IF NOT EXISTS events (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), created_at DATETIME DEFAULT CURRENT_TIMESTAMP)"
  }
}

Testing the connection

curl -X POST http://localhost:8080/api/connections/prod-mysql/test

{"success": true, "message": "Connected successfully (mysql)", "driver": "mysql"}

Notes

Brokoli uses the go-sql-driver/mysql driver
Character set defaults to UTF-8
For custom connection options, use the extra field with JSON config

On this page