# MariaDB Connection Strings in API Maker

> Learn how to format MariaDB URI strings in API Maker—username, password, host, port, database name, and advanced options. Includes SSL, URL encoding tips, and examples for cloud-hosted services.

Source: https://docs.apimaker.dev/v1/docs/Database-connection-string/mariadb-connection-strings.html

## Introduction

A **MariaDB connection string** defines how API Maker connects to your MariaDB database.  
It contains essential information such as **username, password, host, port, and database name**, along with optional parameters for **SSL, pooling, and timeouts**.

This connection string is the foundation for establishing a secure, reliable connection to MariaDB and leveraging API Maker features such as schema definition, cross-database joins, and API queries.

---

## MariaDB Connection String Formats

Below is a **visual breakdown** of the MariaDB connection string.

```
mariadb://username:password@host:port/DB_Name?options

 └───┬──┘ └───────┬───────┘ └───┬────┘ └──┬──┘ └──┬──┘
   Scheme      Credentials   Host + Port Database Options
```

## MariaDB Connection String Examples

### 🧩 Connection String Parts Explained

- **Protocol (scheme):**

    - `mariadb://` → Standard MariaDB connection.

- **Credentials (optional):**

    - Format: `username:password@`.

    - Use **URL-encoding** for special characters (`pa@ss` → `pa%40ss`).

- **Host + Port:**

    - Default host: `localhost`

    - Default port: `3306`

- **Database (optional):**

    - After `/`, e.g., `/mydb`.

    - If omitted, connection starts without selecting a default database.

- **Options (query params):**

    - Added after `?`, key-value pairs separated by `&`.

    - Examples:

        - `?ssl=true` → Enforce SSL/TLS.

        - `?connectTimeout=10000` → Timeout in ms.

        - `?allowPublicKeyRetrieval=true` → Needed for certain cloud setups.

---

### Basic Localhost Connection

```text
mariadb://localhost:3306
```

### Localhost with Database Name

```text
mariadb://localhost:3306/mydb
```

### Localhost with Username & Password

```text
mariadb://user:password@localhost:3306/mydb
```

### Localhost with SSL Enabled

```text
mariadb://user:password@localhost:3306/mydb?ssl=true
```

### Localhost with Connection Timeout

```text
mariadb://user:password@localhost:3306/mydb?connectTimeout=10000
```

### Localhost with Charset Setting

```text
mariadb://user:password@localhost:3306/mydb?charset=utf8mb4
```

### Localhost with Multiple Options

```text
mariadb://user:password@localhost:3306/mydb?ssl=true&charset=utf8mb4&connectTimeout=10000
```

### Multi-Host / Cluster Connection

```text
mariadb://user:password@host1:3306,host2:3306,host3:3306/mydb
```

### Replication Connection

```text
mariadb://replica_user:password@replica_host:3306/mydb?replication=true
```

### Cloud Provider Connection Example

```text
mariadb://user:password@mydb.instance.region.mdbcloud.com:3306/mydb
```

### Cloud with SSL Required

```text
mariadb://user:password@mydb.instance.region.mdbcloud.com:3306/mydb?ssl=true
```

### Cloud with Multi-Host Failover

```text
mariadb://user:password@host1:3306,host2:3306/mydb?ssl=true
```

---

## MariaDB Connection String Parts Breakdown

|Part|Description|Example|
|---|---|---|
|**Scheme**|Protocol prefix for MariaDB.|`mariadb://`|
|**Username** (optional)|Database username for authentication.|`user`|
|**Password** (optional)|Password for authentication (URL-encoded if special characters).|`p%40ss` for `p@ss`|
|**@**|Separator between credentials and host(s).|`user:password@`|
|**Host(s)**|One or more MariaDB server addresses.|`localhost`, `mydb.xxxxxx.us-east-1.rds.amazonaws.com`|
|**Port** (optional)|Port number (default: `3306`).|`:3306`|
|**Comma-separated Hosts**|Multiple hosts for HA / load balancing.|`host1:3306,host2:3306`|
|**/** (slash after hosts)|Separator between hosts and default database.|`/`|
|**Default Database**|Database name to connect to if none specified.|`mydb`|
|**?options** (query params)|Connection options in `key=value` format (joined with `&`).|`?ssl=true&connectTimeout=10000&charset=utf8mb4`|

---

## MariaDB Connection String Options Breakdown and Explanation

|Parameter|Description|Example Value|
|---|---|---|
|**ssl_mode**|Controls SSL/TLS usage (`DISABLED`, `PREFERRED`, `REQUIRED`, `VERIFY_CA`, `VERIFY_IDENTITY`).|`ssl_mode=REQUIRED`|
|**ssl_ca**|Path to the CA certificate for SSL validation.|`ssl_ca=/etc/ssl/ca.pem`|
|**ssl_cert**|Path to client SSL certificate.|`ssl_cert=/etc/ssl/client-cert.pem`|
|**ssl_key**|Path to client SSL key.|`ssl_key=/etc/ssl/client-key.pem`|
|**connect_timeout**|Maximum wait time (in seconds) for establishing a new connection.|`connect_timeout=10`|
|**database**|Default database to connect to.|`database=mydb`|
|**charset**|Sets the character set for the connection.|`charset=utf8mb4`|
|**autocommit**|Controls autocommit mode (`0` = off, `1` = on).|`autocommit=1`|
|**allow_multi_statements**|Allow executing multiple SQL statements in one query.|`allow_multi_statements=true`|
|**read_timeout**|Seconds to wait for a read operation before timing out.|`read_timeout=30`|
|**write_timeout**|Seconds to wait for a write operation before timing out.|`write_timeout=30`|
|**max_allowed_packet**|Maximum packet size (bytes) for sending/receiving data.|`max_allowed_packet=67108864`|
|**allowPublicKeyRetrieval**|Allows retrieval of RSA public key for secure password exchange.|`allowPublicKeyRetrieval=true`|
|**useSSL**|Deprecated SSL flag; prefer `ssl_mode`.|`useSSL=true`|
|**serverTimezone**|Sets the timezone for the connection.|`serverTimezone=UTC`|
|**replication**|For replication connections (`true` or `database`).|`replication=true`|

---

## Secure Connection Best Practices

- Always enable **SSL/TLS (`ssl=true` or `ssl-mode=REQUIRED`)** in production to protect data in transit.

- Use **environment variables** instead of hardcoding database credentials in your connection strings.

- Leverage **API Maker Secrets Management** to securely store, manage, and rotate sensitive keys.

- Restrict MariaDB users to **least privilege access**, only granting necessary permissions.

- Use **firewall rules** or `bind-address` settings to allow connections only from trusted IP addresses.

- Enable **connection and read/write timeouts** to prevent hanging connections and improve reliability.

---

## Connecting MariaDB in API Maker

1. Open **API Maker Dashboard** → **Secret Management** → **Default**.

2. Select **MariaDB** as the database type.

3. Paste your MariaDB connection string (including DB and SSL parameters).

4. Click **Test Connection** to verify connectivity.

5. Save the configuration.

Once connected, you can:

- Define **schemas** for your MariaDB tables.

- Query data using `/api/schema/...` endpoints.

- Perform **cross-database joins** with MySQL, PostgreSQL, or MongoDB.

---

## Troubleshooting

When connecting to MariaDB (local, self-hosted, or managed cloud instances), you may encounter several common errors.  
Below is a categorized list with explanations:

---

### 🔑 Authentication & Authorization Errors

|Error Code|Name|Description|Common Fix|
|---|---|---|---|
|1045|AccessDenied|Wrong username or password.|Verify username/password in connection string.|
|1130|HostNotAllowed|User cannot connect from this host.|Grant privileges using `GRANT` and whitelist IP.|
|1044|InsufficientPrivilege|User does not have privileges on the database.|Grant required privileges using `GRANT`.|
|1203|TooManyConnections|Connection limit exceeded.|Increase `max_connections` or close idle sessions.|

---

### 🌐 Network & Connectivity Errors

|Error Code|Name|Description|Common Fix|
|---|---|---|---|
|2002|ConnectionRefused|Client could not connect to server.|Verify hostname, port, and firewall rules.|
|2003|ConnectionFailure|Connection unexpectedly terminated.|Check server logs and network stability.|
|2013|LostConnection|Connection was closed unexpectedly.|Reconnect before executing queries.|
|1047|ServerShutdown|Server shutting down or not responding.|Wait for restart and reconnect.|

---

### 🗂️ Duplicate & Constraint Errors

|Error Code|Name|Description|Common Fix|
|---|---|---|---|
|1062|DuplicateEntry|Duplicate value violates unique constraint.|Ensure unique values or handle conflict with `ON DUPLICATE KEY UPDATE`.|
|1452|ForeignKeyViolation|Insert/update violates foreign key.|Ensure referenced key exists before inserting/updating.|
|3819|CheckConstraintViolation|Value violates a `CHECK` constraint.|Insert valid values matching constraint rules.|
|1215|ForeignKeyConstraintFail|Foreign key constraint creation failed.|Verify referenced keys and types match.|

---

### ⚡ Write & Transaction Errors

|Error Code|Name|Description|Common Fix|
|---|---|---|---|
|1213|DeadlockFound|Deadlock occurred between transactions.|Redesign queries/locking, or retry after backoff.|
|1205|LockWaitTimeout|Transaction timed out waiting for a lock.|Retry the transaction or increase lock wait timeout.|
|1048|NotNullViolation|Tried to insert NULL into NOT NULL column.|Provide a value or alter column to allow NULL.|
|1021|DiskFull|Server ran out of disk space.|Free disk space or increase storage.|

---

### 🗄️ Query & Syntax Errors

|Error Code|Name|Description|Common Fix|
|---|---|---|---|
|1064|SyntaxError|Invalid SQL syntax.|Fix query syntax.|
|1054|UnknownColumn|Column does not exist.|Check column name spelling or schema.|
|1146|TableDoesNotExist|Table does not exist.|Create table or use correct table name.|
|1305|UnknownFunction|Function/operator not defined.|Define function or cast arguments properly.|
|1065|EmptyQuery|Query is empty or invalid.|Provide a valid SQL statement.|

---

### 🔒 TLS/SSL & Security Errors

|Error Code|Name|Description|Common Fix|
|---|---|---|---|
|2026|SSLConnectionError|TLS/SSL negotiation failed.|Ensure certificates, SSL mode (`REQUIRED`, `VERIFY_CA`).|
|1045|AccessDeniedSSL|User not allowed for SSL/host restrictions.|Grant access or adjust SSL-related configs.|
|1040|TooManyConnectionsSSL|Too many SSL connections.|Increase `max_connections` or close idle sessions.|

---

### 🛠️ Miscellaneous Errors

|Error Code|Name|Description|Common Fix|
|---|---|---|---|
|1221|IncorrectKeyFile|Query exceeded internal limit or config issue.|Check table indexes or optimize query.|
|1114|TableIsFull|Table storage limit reached.|Free disk space or increase table size.|
|1406|DataTooLong|Value too long for column type.|Increase column size or truncate data.|
|1194|InternalError|Unexpected internal error.|Check server logs and MariaDB version.|

---

### 🧩 Error Types Summary

|Category|Typical Causes|Example Error Codes|
|---|---|---|
|**Authentication & Authorization**|Invalid login, role issues, too many connections|1045, 1130, 1044, 1203|
|**Network & Connectivity**|Host unreachable, server shutdown/recovery|2002, 2003, 2013, 1047|
|**Constraints & Duplicates**|Unique, foreign key, check constraint violations|1062, 1452, 3819, 1215|
|**Write & Transaction**|Deadlocks, lock timeouts, null inserts|1213, 1205, 1048, 1021|
|**Query & Syntax**|Invalid SQL syntax, undefined columns/functions|1064, 1054, 1146, 1305|
|**TLS/SSL & Security**|SSL handshake errors, host/user restrictions|2026, 1045, 1040|
|**Miscellaneous**|Data truncation, table full, internal errors|1221, 1114, 1406, 1194|

---

## FAQ

**Q1:** _Can I use MariaDB cloud services like Amazon RDS, Azure Database, or Google Cloud SQL?_  
Yes. Just provide the full connection string from your provider and whitelist API Maker’s IP.

**Q2:** _How do I connect to a MariaDB instance running in Docker?_  
Use the container’s IP or host machine IP, e.g.,  
`mariadb://user:pass@172.17.0.2:3306/mydb`.

**Q3:** _How do I handle timezone differences in MariaDB connections?_  
You can set the timezone in the connection string using `?serverTimezone=UTC` or another supported timezone.

**Q4:** _Is it possible to connect to multiple MariaDB instances for replication or failover?_  
Yes. You can provide multiple hosts in the connection string separated by commas, e.g.,  
`mariadb://user:pass@host1:3306,host2:3306/mydb?replication=true`.

**Q5:** _Can I enable automatic reconnection on connection drops?_  
Yes. Use the `?autoReconnect=true` option in your connection string to allow automatic reconnection.

---

---

## MariaDB Cloud Providers

- **MariaDB SkySQL**

    - Official cloud service by MariaDB Corporation, designed specifically for MariaDB.

    - Fully managed with automated backups, scaling, high availability, and monitoring.

    - Offers enterprise features like advanced security, clustering, and hybrid cloud deployment.

    - Ideal for businesses seeking production-ready MariaDB with official support.

    - [mariadb.com/products/skysql](https://mariadb.com/products/skysql)

- **DB4Free MariaDB**

    - Free-to-use MariaDB hosting for development, testing, and learning purposes.

    - Provides an easy-to-use web interface and standard MariaDB features.

    - Not intended for production, but great for prototyping and experiments.

    - Offers latest MariaDB versions to try new features.

    - [db4free.net](https://www.db4free.net/)

- **PlanetScale (MariaDB compatible)**

    - Serverless platform supporting MariaDB-compatible workloads for modern applications.

    - Offers horizontal scaling, zero-downtime schema changes, and automatic failover.

    - Designed for global, distributed cloud-native applications.

    - Ideal for developers requiring elasticity without managing database servers.

    - [planetscale.com](https://planetscale.com)

- **MariaDB SkyCloud**

    - Managed MariaDB with cloud-native optimizations.

    - High availability, automated backups, and global replication.

    - Enterprise-grade features like query optimization, security, and analytics integration.

    - Best for organizations looking for MariaDB-exclusive managed service.

    - [mariadb.com/products/skycloud](https://mariadb.com/)

- **API Maker Cloud**

    - Fully managed MariaDB instance with instant API generation and schema management.

    - Seamless integration with API Maker features such as auto-increment, joins, and advanced querying.

    - Perfect for small to medium projects needing rapid prototyping and production-ready deployments.

    - [apimaker.dev](https://apimaker.dev)

- **ScaleGrid (MariaDB)**

    - Managed MariaDB with root access for advanced configuration and automation.

    - Supports multi-cloud deployments with high availability, scaling, and monitoring.

    - Ideal for developers who want control without manual server management.

    - [scalegrid.io/mariadb-hosting](https://scalegrid.io/mariadb-hosting/)

---

## Related Links

- [MariaDB Connection String Documentation](https://mariadb.com/docs/server/server-usage/connecting/mariadb-connecting-guide-1)
- [API Maker Schemas Overview](https://docs.apimaker.dev/v1/docs/schema/schema.html)
- [API Maker Secrets Management](https://docs.apimaker.dev/v1/docs/secrets/secrets.html)