Skip to content

MariaDB Connection Strings in API Maker

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@sspa%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

mariadb://localhost:3306

Localhost with Database Name

mariadb://localhost:3306/mydb

Localhost with Username & Password

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

Localhost with SSL Enabled

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

Localhost with Connection Timeout

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

Localhost with Charset Setting

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

Localhost with Multiple Options

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

Multi-Host / Cluster Connection

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

Replication Connection

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

Cloud Provider Connection Example

mariadb://user:[email protected]:3306/mydb

Cloud with SSL Required

mariadb://user:[email protected]:3306/mydb?ssl=true

Cloud with Multi-Host Failover

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 DashboardSecret ManagementDefault.

  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:[email protected]: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

  • 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

  • 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

  • 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

  • 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

  • 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