How to Connect Your Database to TransformZ

Introduction

Data accessibility shouldn't be complicated. Whether you're running MySQL, PostgreSQL, SQL Server, or Oracle, TransformZ makes it simple to connect your database and instantly expose it as secure, standardized REST APIs. No boilerplate code, no complex configurations—just straightforward data access in minutes.

In this guide, you'll learn how to connect your database to TransformZ, regardless of which database management system you're using.

Why Connect Your Database to TransformZ?

Before diving into the technical steps, let's understand why this matters:

• Instant API Generation: Your database tables become queryable via OData REST APIs automatically
• No Code Required: Skip weeks of custom development and boilerplate code
• Secure by Default: Built-in JWT authentication, HTTPS encryption, IP Restriction, and API key management
• Real-Time Data: Query live database directly, no syncing or batch jobs needed
• Universal Standards: OData protocol ensures compatibility with Excel, Power BI, Salesforce, and hundreds of other tools

According to recent surveys, 76% of enterprises make decisions without consulting available data simply because it's too hard to access. TransformZ solves this problem by making data accessibility instant and effortless.

Supported Databases

TransformZ currently supports the following database systems, while more are coming soon:

Currently Available

• MySQL: The world's most popular open-source database.
• PostgreSQL: Advanced and performant open-source relational database.

Coming Soon

• SQL Server: Microsoft's enterprise database platform.

• Oracle: Enterprise-grade relational database.

• SQLite: Lightweight, serverless database.

• MariaDB: MySQL-compatible open-source database.

• AWS Aurora (MySQL Edition): Cloud-native MySQL-compatible database.

• AWS Aurora (PostgreSQL Edition): Cloud-native PostgreSQL-compatible database.

• Azure SQL Database: Microsoft's cloud database service.

Prerequisites

Before you start, make sure you have:

1. A TransformZ account - Sign up at transformz.ca.
2. Database credentials - Host, port, username, password, schema, and database name.
3. Network access - Ensure your database accepts connections from TransformZ servers.
4. Database permissions - Your credentials should have READ access (minimum) to the tables you want to expose.

For security, most production databases restrict network access. You'll need to:

• Whitelist TransformZ IP address: 148.113.220.40
• Configure firewall rules to allow incoming connections on your DB port.
• Enable remote connections in your database configuration.
• Use SSL/TLS connections when possible for added security.

Step-by-Step: Connecting Your Database to TransformZ

Let's walk through the complete process of connecting any database to TransformZ.

Step 1: Navigate to Data Sources

1. Log in to your TransformZ dashboard
2. From the left sidebar, click on Data Sources
3. Click the "Add New Data Source" button

This opens the data source creation wizard.

Step 2: Configure Basic Information

In the first tab, you'll provide essential details about your data source:

Data Source Name

• Choose a descriptive name that identifies this connection
• Example: 'production-mysql', 'analytics-postgres', 'crm-database'
• This name appears throughout your dashboard and API documentation

Database Type

• Select your database management system from the dropdown
• Options include: MySQL, PostgreSQL, more are coming soon.

Description (Optional)

• Add notes about what this database contains
• Example: "Production customer database" or "Analytics data warehouse"
• Helps team members understand the data source purpose

Once completed, click Next to proceed to the connection settings.

Step 3: Enter Database Credentials

This is where you provide the technical details for connecting to your database. The exact fields may vary slightly based on your database type, but typically include:

Click on Next to access the second tab, fill in your database credentials:

• Host: the database host.
• Port: the database port.
• Name: the database name.
• Schema: the database schema.
• Username: the username of the user who has access to the database.
• Password: the password of the user who has access to the database.

Once they are all in, the Create button to save the database.

Step 4: Test the Connection

Once saved, you’ll be redirected to the data sources listing, which display your newly created data source as Disconnected.

1. Click the three dots (⋮) on your data source card
2. Select "Test" to verify the connection again
3. You'll see one of two results:
   • ✅ Connected - Your credentials are correct and network access is working.
   • ❌ Disconnected - Review the error message for troubleshooting.

Common connection errors and solutions:

• "Connection timeout" - Check firewall rules and IP whitelisting.
• "Access denied" - Verify username and password.
• "Database does not exist" - Confirm the database name is correct.
• "Too many connections" - Check your database connection limits.

Step 5: Verify and Explore

From the data sources list, click on the data source name to access its details page, where you can:

• View all available tables and their schemas
• See auto-generated API endpoints
• Access the OData Playground for testing
• Configure security settings

Security Best Practices

1. Use Read-Only Credentials When Possible

   • If you only need to query data, create a database user with SELECT-only permissions
   • This prevents accidental data modifications through the API

2. Create Dedicated Database Users

   • Don't use your root or admin account
   • Create a specific user for TransformZ with minimal necessary permissions

3. Enable SSL/TLS Connections

   • Configure your database to require encrypted connections
   • Add SSL parameters to your connection string

4. Rotate Credentials Regularly

   • Update database passwords periodically
   • Update your TransformZ data source configuration accordingly

5. Monitor API Usage

   • Review TransformZ usage logs regularly
   • Set up alerts for unusual activity patterns

Troubleshooting Common Issues

Connection Failures

Problem: "Connection timeout" error

Solutions:

• Verify your database server is running
• Check firewall rules allow TransformZ IP addresses
• Confirm the host and port are correct
• Test connectivity using a database client from another location

Problem: "Access denied for user" error

Solutions:

• Double-check username and password
• Verify the user has appropriate permissions
• Check if the user is allowed to connect from remote hosts
• For MySQL: "GRANT ALL ON database.* TO 'user'@'%'"

Problem: "Unknown database" error

Solutions:

• Confirm the database name is spelled correctly
• Verify the database exists on the server
• Check if your user has access to this specific database

Performance Issues

Problem: Slow API responses

Solutions:

• Check if your database server has adequate resources
• Review slow query logs in your database
• Add indexes to frequently queried columns
• Implement pagination in your API calls

Problem: "Too many connections" error

Solutions:

• Increase your database's max_connections setting
• Close unused connections in your database
• Reduce connection pool size if you have multiple data sources

API Issues

Problem: Authentication errors (401 Unauthorized)

Solutions:

• Regenerate your JWT token or API key
• Ensure the token is correctly included in request headers
• Check if the token has expired
• Verify you're using the correct authentication method

Problem: Empty results when data should exist

Solutions:

• Test the same query directly in your database
• Check if your OData filter syntax is correct
• Verify your database user has SELECT permissions on the table
• Refresh the data source schema in TransformZ

Integrating with Other Tools

Once your database is connected to TransformZ, you can integrate with countless business tools: Power BI, Microsoft Excel (Power Query), Salesforce, Custom Applications, and other systems.

What's Next?

Now that your database is connected to TransformZ, you can:

1. Build Custom Dashboards - Use Excel templates or Power BI for instant analytics
2. Integrate with Your Apps - Connect your database to web and mobile applications
3. Scale Your Data Strategy - Add more databases and create a unified data platform
4. Explore Advanced Features - Configure custom permissions, IP restrictions, and audit trails

Real-World Impact

TransformZ eliminates the traditional barriers to data accessibility:

• ❌ Before: Weeks or months of custom API development
• ✅ After: 5 minutes to generate a REST API for a database
• ❌ Before: Complex ORM configurations and boilerplate code
• ✅ After: Automatic schema discovery and database API generation
• ❌ Before: Manual security implementation and testing
• ✅ After: Enterprise-grade security built-in by default
• ❌ Before: Separate tools for development, testing, and production
• ✅ After: Unified platform with built-in testing environment

Conclusion

Connecting your database to TransformZ transforms how you access and share data. Whether you're exposing MySQL data to Excel users, integrating PostgreSQL with custom applications, or building real-time dashboards from SQL Server, the process is the same: simple, secure, and instant.

Remember the five pillars of data accessibility that TransformZ embodies:

1. Availability: Your data is always accessible, never trapped in silos.
2. Usability: Standard APIs that work with any tool or application.
3. Security: Enterprise-grade protection for your sensitive data.
4. Interoperability: Open standards that eliminate vendor lock-in.
5. Timeliness: Real-time access to your latest data, no processing delays.

---

Need help?

• 📧 Email: info@automathing.ca
• 🌐 Website: transformz.ca
• 💼 LinkedIn: Follow Orléando Dassi

---

Related Articles:

• How to Expose MySQL Data as OData REST APIs in 5 Minutes with TransformZ
• The 5 Pillars of Data Accessibility