PostgreSQL and MySQL
Datafi connects to PostgreSQL and MySQL through native database drivers powered by the sqlx library. This provides high-performance, type-safe connectivity with support for password authentication, SSL/TLS encryption, and built-in connection pooling.
Supported Versions
| Database | Supported Versions | Driver |
|---|---|---|
| PostgreSQL | 12, 13, 14, 15, 16 | sqlx (native Rust driver) |
| MySQL | 5.7, 8.0, 8.1, 8.2 | sqlx (native Rust driver) |
Datafi also supports PostgreSQL-compatible databases such as Amazon Aurora PostgreSQL, Azure Database for PostgreSQL, and Google Cloud SQL for PostgreSQL. MySQL-compatible databases like Amazon Aurora MySQL and Azure Database for MySQL are also supported.
Authentication
Password Authentication
The simplest connection method. Provide a username and password that Datafi uses to authenticate with your database server.
| Parameter | Description | Required |
|---|---|---|
| Host | The hostname or IP address of your database server. | Yes |
| Port | The port number (PostgreSQL default: 5432, MySQL default: 3306). | Yes |
| Database | The name of the database to connect to. | Yes |
| Username | The database user account. | Yes |
| Password | The password for the user account. | Yes |
SSL/TLS Authentication
For production environments, you should enable SSL/TLS to encrypt data in transit between the Edge node and your database.
| SSL Parameter | Description | Required |
|---|---|---|
| SSL Mode | The level of SSL enforcement (see table below). | Yes |
| CA Certificate | The root certificate authority certificate file (PEM format). | Depends on mode |
| Client Certificate | The client certificate file for mutual TLS. | Optional |
| Client Key | The private key file corresponding to the client certificate. | Optional |
SSL Modes:
| Mode | Encryption | Server Verification | Client Certificate |
|---|---|---|---|
disable | No | No | No |
prefer | If available | No | No |
require | Yes | No | No |
verify-ca | Yes | CA validated | No |
verify-full | Yes | CA + hostname validated | No |
mutual | Yes | CA + hostname validated | Yes |
Using disable or prefer modes in production is strongly discouraged. At minimum, use require to ensure all traffic between the Edge node and your database is encrypted.
Setting Up a Connection
- Navigate to Integrations > Add Data Source.
- Select PostgreSQL or MySQL from the connector list.
- Enter the connection parameters (host, port, database, credentials).
- Optionally configure SSL by uploading certificates.
- Click Test Connection to verify connectivity.
- Click Save to add the data source.
Once saved, Datafi runs schema introspection to catalog tables, views, columns, and data types. The source appears in your Data Catalog within a few minutes.
Connection Pooling
Datafi maintains a connection pool for each configured data source to optimize performance and manage database connections efficiently. The pool is managed at the Edge node level.
| Pool Parameter | Description | Default |
|---|---|---|
| Min Connections | The minimum number of idle connections maintained in the pool. | 2 |
| Max Connections | The maximum number of concurrent connections allowed. | 10 |
| Idle Timeout | Duration (in seconds) before an idle connection is closed. | 300 |
| Max Lifetime | Maximum duration (in seconds) a connection can remain open. | 1800 |
| Acquire Timeout | Maximum wait time (in seconds) to acquire a connection from the pool. | 30 |
To adjust pool settings:
- Navigate to the data source configuration in Integrations.
- Open the Advanced tab.
- Modify pool parameters.
- Click Save. Changes take effect for new connections.
If you see connection pool exhausted errors in the connector log, increase the Max Connections value. Ensure your database server also allows enough concurrent connections to accommodate the pool size.
Query Execution
When you query a PostgreSQL or MySQL source, Datafi compiles the request to the appropriate SQL dialect.
| Feature | PostgreSQL | MySQL |
|---|---|---|
| CTEs | Supported | Supported (8.0+) |
| Window Functions | Supported | Supported (8.0+) |
| JSON Functions | jsonb_* functions | JSON_* functions |
| LIMIT/OFFSET | Supported | Supported |
| Prepared Statements | Supported | Supported |
The native driver handles type mapping automatically. PostgreSQL-specific types (e.g., JSONB, UUID, ARRAY) and MySQL-specific types (e.g., ENUM, SET) are mapped to Datafi's internal type system.
Troubleshooting
| Issue | Possible Cause | Resolution |
|---|---|---|
Connection refused | Firewall blocking the port or database not listening on the specified host. | Verify network access from the Edge node to the database server. |
SSL certificate verify failed | CA certificate mismatch or expired certificate. | Upload the correct CA certificate or renew expired certificates. |
Too many connections | Pool size exceeds database max_connections. | Reduce Datafi pool size or increase the database limit. |
Authentication failed | Incorrect username or password. | Verify credentials and check database authentication logs. |
Next Steps
- Snowflake -- Connect to Snowflake with JWT or OAuth.
- Cloud Warehouses -- Set up BigQuery, Databricks, Redshift, or Synapse.