MySQL Connector

mysql://[username[:password]@]host[:port]/database[?options]

# Standard connection
mysql://root:password@localhost:3306/mydb

# With SSL enabled
mysql://user:[email protected]:3306/production?sslmode=require

# Custom port
mysql://user:pass@localhost:3307/mydb

# No SSL (development only)
mysql://user:pass@localhost:3306/db?sslmode=disable

# SSL without certificate verification (recommended for most use cases)
mysql://user:pass@host:3306/db?sslmode=require

# SSL with certificate verification
mysql://user:pass@host:3306/db

[[sources]]
id = "mysql_db"
dsn = "mysql://user:pass@host:3306/db"
connection_timeout = 30  # Seconds to establish connection
query_timeout = 60       # Seconds for query execution

# IAM token contains "X-Amz-Credential" - detected automatically
mysql://dbuser:TOKEN_CONTAINING_X-Amz-Credential@mydb.abc123.us-east-1.rds.amazonaws.com:3306/production

SELECT SCHEMA_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA
ORDER BY SCHEMA_NAME;

// Current database (from connection)
await connector.getTables();

// Specific database
await connector.getTables('mydatabase');

CREATE TABLE users (
  email VARCHAR(255) NOT NULL COMMENT 'User email address'
);

const schema = await connector.getTableSchema('users');
// Returns: [
//   {
//     column_name: 'email',
//     data_type: 'varchar',
//     is_nullable: 'NO',
//     column_default: null,
//     description: 'User email address'
//   }
// ]

const indexes = await connector.getTableIndexes('users');
// Returns: [
//   {
//     index_name: 'PRIMARY',
//     column_names: ['id'],
//     is_unique: true,
//     is_primary: true
//   },
//   {
//     index_name: 'idx_email',
//     column_names: ['email'],
//     is_unique: true,
//     is_primary: false
//   }
// ]

// Get all routines
await connector.getStoredProcedures('mydatabase');

// Only functions
await connector.getStoredProcedures('mydatabase', 'function');

// Only procedures
await connector.getStoredProcedures('mydatabase', 'procedure');

const proc = await connector.getStoredProcedureDetail('my_procedure', 'mydatabase');
// Returns: {
//   procedure_name: 'my_procedure',
//   procedure_type: 'procedure',
//   language: 'sql',
//   parameter_list: 'user_id IN int',
//   definition: 'CREATE PROCEDURE...'
// }

CREATE TABLE users (
  id INT PRIMARY KEY
) COMMENT='Application user accounts';

const comment = await connector.getTableComment('users');
// Returns: 'Application user accounts'

await connector.executeSQL(
  "SELECT * FROM users WHERE email = ? AND active = ?",
  { maxRows: 100 },
  ['[email protected]', true]
);

await connector.executeSQL(`
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  SELECT * FROM accounts WHERE id IN (1, 2);
`, { maxRows: undefined });

// Each executeSQL gets a dedicated connection
// This ensures LAST_INSERT_ID() and session variables work correctly
await connector.executeSQL(
  "INSERT INTO users (name) VALUES ('Alice'); SELECT LAST_INSERT_ID();",
  {}
);

await connector.executeSQL(
  "SELECT * FROM large_table",
  { maxRows: 100 }
);
// Executes: SELECT * FROM large_table LIMIT 100

# Environment variable
export DSN="mysql://root:password@localhost:3306/mydb"

# Or in TOML
[[sources]]
id = "local"
dsn = "mysql://root:password@localhost:3306/mydb"

[[sources]]
id = "rds_mysql"
dsn = "mysql://admin:[email protected]:3306/production?sslmode=require"
connection_timeout = 30

# Generate IAM token (expires in 15 minutes)
TOKEN=$(aws rds generate-db-auth-token \
  --hostname mydb.abc123.us-east-1.rds.amazonaws.com \
  --port 3306 \
  --username dbuser \
  --region us-east-1)

# Use token in DSN
export DSN="mysql://dbuser:$TOKEN@mydb.abc123.us-east-1.rds.amazonaws.com:3306/production"

[[sources]]
id = "remote_mysql"
dsn = "mysql://dbuser:dbpass@internal-db:3306/mydb"
ssh_host = "bastion.example.com"
ssh_user = "admin"
ssh_key = "~/.ssh/id_rsa"

[[sources]]
id = "db1"
dsn = "mysql://user:pass@localhost:3306/database1"

[[sources]]
id = "db2"
dsn = "mysql://user:pass@localhost:3306/database2"

// Query db1
await execute_sql("SELECT * FROM users", { source_id: "db1" });

// Query db2
await execute_sql("SELECT * FROM orders", { source_id: "db2" });

-- Query tables from different databases
SELECT 
  u.name,
  COUNT(o.id) as order_count
FROM database1.users u
LEFT JOIN database2.orders o ON u.id = o.user_id
GROUP BY u.id;

-- Query JSON columns (MySQL 5.7+)
SELECT 
  JSON_EXTRACT(data, '$.name') as name,
  JSON_EXTRACT(data, '$.tags') as tags
FROM events
WHERE JSON_EXTRACT(data, '$.type') = 'purchase';

-- MySQL 8.0+ shorthand
SELECT 
  data->>'$.name' as name,
  data->'$.tags' as tags
FROM events
WHERE data->>'$.type' = 'purchase';

SELECT 
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) as recency_rank
FROM transactions;

WITH recent_orders AS (
  SELECT customer_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
  GROUP BY customer_id
)
SELECT c.name, ro.order_count
FROM customers c
JOIN recent_orders ro ON c.id = ro.customer_id
WHERE ro.order_count > 5;

-- Create full-text index
ALTER TABLE articles ADD FULLTEXT(title, content);

-- Search with MATCH...AGAINST
SELECT title, MATCH(title, content) AGAINST('mysql database') as relevance
FROM articles
WHERE MATCH(title, content) AGAINST('mysql database')
ORDER BY relevance DESC;

mysql://user:pass@host:3306/db?sslmode=require