MariaDB Connector

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

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

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

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

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

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

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

[[sources]]
id = "mariadb_db"
dsn = "mariadb://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
mariadb://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="mariadb://root:password@localhost:3306/mydb"

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

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

# Generate IAM token
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="mariadb://dbuser:$TOKEN@mydb.abc123.us-east-1.rds.amazonaws.com:3306/production"

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

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

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

-- Create temporal table
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255)
) WITH SYSTEM VERSIONING;

-- Query historical data
SELECT * FROM users FOR SYSTEM_TIME AS OF TIMESTAMP '2024-01-01 00:00:00';

-- Get all versions of a record
SELECT * FROM users FOR SYSTEM_TIME ALL WHERE id = 123;

-- MariaDB JSON functions
SELECT 
  JSON_VALUE(data, '$.name') as name,
  JSON_QUERY(data, '$.tags') as tags
FROM events
WHERE JSON_EXISTS(data, '$.type');

-- Create sequence
CREATE SEQUENCE user_id_seq START WITH 1000 INCREMENT BY 1;

-- Use in INSERT
INSERT INTO users (id, name) VALUES (NEXT VALUE FOR user_id_seq, 'Alice');

-- Get current value
SELECT LASTVAL(user_id_seq);

SELECT 
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total,
  RANK() OVER (PARTITION BY category ORDER BY amount DESC) as rank_in_category
FROM transactions;

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 as level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

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