background
PromptQuery LogoPromptQuery
Back to Blog

Connecting to Multiple Databases: A Complete Guide

November 3, 2025•By PromptQuery Team•Tutorial
TutorialDatabaseConnectionsGuideSetup
Connecting to Multiple Databases: A Complete Guide

Connecting to Multiple Databases: A Complete Guide

PromptQuery supports a wide range of database systems, allowing you to work with PostgreSQL, MySQL, SQL Server, Oracle, SQLite, MariaDB, and Supabase—all from a single interface. This comprehensive guide will walk you through connecting each database type, covering connection requirements, configuration options, and best practices.

Why Connect Multiple Databases?

Working with multiple databases in PromptQuery offers several advantages:

  • Unified Interface - Manage all your databases from one place
  • Cross-Database Queries - Generate SQL optimized for each database type
  • Schema Comparison - Compare structures across different databases
  • Flexible Workflows - Switch between databases without leaving the tool
  • Context-Aware AI - Each connection provides schema context for accurate SQL generation

Supported Database Systems

PromptQuery currently supports:

  • PostgreSQL (including Supabase)
  • MySQL
  • MariaDB
  • SQL Server
  • SQLite
  • Oracle

Each database type has specific connection requirements and configuration options. Let's explore each one in detail.

General Connection Process

Before diving into database-specific instructions, here's the general process for connecting any database:

  1. Navigate to Connections - Click "Connections" in the PromptQuery sidebar
  2. Create New Connection - Click "New Connection" and select your database type
  3. Enter Connection Details - Fill in the required fields (host, port, database, credentials)
  4. Configure Advanced Settings (optional) - Set SSL, timeouts, and metadata access
  5. Test Connection - Verify PromptQuery can connect successfully
  6. Save Connection - Store the connection for future use
**Security Note:** PromptQuery never stores your database passwords in plain text. All credentials are encrypted and handled securely. You can also configure what metadata PromptQuery accesses from your database.

PostgreSQL Connection Guide

PostgreSQL is one of the most popular open-source relational databases. PromptQuery fully supports PostgreSQL, including cloud-hosted instances like Supabase.

Connection Requirements

  • Host/Server - Your PostgreSQL server address
  • Port - Default is 5432
  • Database Name - The specific database you want to connect to
  • Username - Your PostgreSQL username
  • Password - Your PostgreSQL password
  • SSL Mode - Required for most cloud providers

Step-by-Step Connection

  1. Select PostgreSQL from the database type dropdown
  2. Enter connection details: 
    Host: your-postgres-server.com
Port: 5432
Database: your_database_name
Username: your_username
Password: your_password
  3. Configure SSL (recommended for production):
    • Enable SSL/TLS
    • Select SSL mode: require or verify-full (for cloud providers)
    • Upload CA certificate if required
  4. Test Connection - Click "Test" to verify connectivity
  5. Save Connection - Give it a memorable name like "Production PostgreSQL"

Supabase Connection

Supabase uses PostgreSQL, so the connection process is similar:

  1. Get your Supabase credentials:
    • Go to your Supabase project dashboard
    • Navigate to Settings → Database
    • Copy the connection string or individual parameters
  2. Use Supabase connection details: 
    Host: db.[project-ref].supabase.co
Port: 5432
Database: postgres
Username: postgres
Password: [your-database-password]
  3. Enable SSL - Supabase requires SSL connections
  4. Test and Save - Verify connection and save

PostgreSQL-Specific Features

  • Schema Support - PromptQuery recognizes PostgreSQL schemas (public, information_schema, etc.)
  • Advanced Types - Supports JSON, arrays, and custom types
  • Extensions - Recognizes PostgreSQL extensions and their functions
  • System Databases - Can access postgres, template0, template1 if needed

MySQL Connection Guide

MySQL is widely used in web applications and is fully supported by PromptQuery.

Connection Requirements

  • Host/Server - Your MySQL server address
  • Port - Default is 3306
  • Database Name - The specific database to connect to
  • Username - Your MySQL username
  • Password - Your MySQL password
  • SSL - Optional but recommended

Step-by-Step Connection

  1. Select MySQL from the database type dropdown
  2. Enter connection details: 
    Host: your-mysql-server.com
Port: 3306
Database: your_database_name
Username: your_username
Password: your_password
  3. Configure SSL (if required):
    • Enable SSL/TLS
    • Select SSL mode based on your server requirements
  4. Advanced Options (optional):
    • Connection timeout (default: 30 seconds)
    • Character set (default: utf8mb4)
    • Timezone settings
  5. Test Connection - Verify connectivity
  6. Save Connection - Store with a descriptive name

MySQL-Specific Features

  • Engine Support - Works with InnoDB, MyISAM, and other storage engines
  • Character Sets - Recognizes utf8mb4 and other character sets
  • System Databases - Can access information_schema, mysql, performance_schema, sys
  • Views and Procedures - Full support for MySQL views and stored procedures

MariaDB Connection Guide

MariaDB is a fork of MySQL and shares similar connection requirements. PromptQuery treats MariaDB connections similarly to MySQL but recognizes MariaDB-specific features.

Connection Requirements

  • Host/Server - Your MariaDB server address
  • Port - Default is 3306 (same as MySQL)
  • Database Name - The specific database to connect to
  • Username - Your MariaDB username
  • Password - Your MariaDB password

Step-by-Step Connection

  1. Select MariaDB from the database type dropdown
  2. Enter connection details (same format as MySQL): 
    Host: your-mariadb-server.com
Port: 3306
Database: your_database_name
Username: your_username
Password: your_password
  3. Configure SSL if required by your server
  4. Test and Save - Verify connection and store it

MariaDB-Specific Features

  • Compatibility - Fully compatible with MySQL connection protocol
  • Advanced Features - Supports MariaDB-specific features like sequences and window functions
  • System Databases - Recognizes MariaDB system databases

SQL Server Connection Guide

Microsoft SQL Server requires specific connection settings, especially for authentication.

Connection Requirements

  • Host/Server - Your SQL Server instance address
  • Port - Default is 1433
  • Database Name - The specific database to connect to
  • Authentication - Windows Authentication or SQL Server Authentication
  • Username/Password - Required for SQL Server Authentication
  • Encrypt - Recommended to enable encryption

Step-by-Step Connection

  1. Select SQL Server from the database type dropdown
  2. Choose Authentication Method:
    • SQL Server Authentication (most common): 
      Host: your-sql-server.com
Port: 1433
Database: your_database_name
Username: your_username
Password: your_password
    • Windows Authentication (if supported):
      • Uses your Windows credentials
      • May require additional configuration
  3. Enable Encryption - Check "Encrypt connection" for security
  4. Trust Server Certificate - Enable if using self-signed certificates
  5. Test Connection - Verify connectivity
  6. Save Connection - Store with a descriptive name

SQL Server-Specific Features

  • Schema Support - Recognizes SQL Server schemas (dbo, guest, etc.)
  • System Databases - Can access master, model, msdb, tempdb
  • Instance Names - Supports named instances (e.g., server\instance)
  • Azure SQL - Works with Azure SQL Database with proper configuration

Oracle Connection Guide

Oracle Database requires specific connection string formats and may need additional configuration.

Connection Requirements

  • Host/Server - Your Oracle server address
  • Port - Default is 1521
  • Service Name or SID - Your Oracle service name or SID
  • Username - Your Oracle username
  • Password - Your Oracle password
  • Connection Type - Basic or TNS connection string

Step-by-Step Connection

  1. Select Oracle from the database type dropdown
  2. Choose Connection Type:
    • Basic Connection: 
      Host: your-oracle-server.com
Port: 1521
Service Name: your_service_name
Username: your_username
Password: your_password
    • TNS Connection String (advanced):
      • Use full TNS connection string format
  3. Configure Advanced Settings:
    • Character set (default: AL32UTF8)
    • Connection timeout
    • Session timezone
  4. Test Connection - Verify connectivity
  5. Save Connection - Store with a descriptive name

Oracle-Specific Features

  • Schema Support - Full support for Oracle schemas and users
  • Tablespaces - Recognizes tablespace information
  • PL/SQL - Supports Oracle PL/SQL procedures and functions
  • Advanced Types - Supports Oracle-specific data types

SQLite Connection Guide

SQLite is a file-based database, making it unique among the supported databases. Connection requires pointing to a database file.

Connection Requirements

  • Database File Path - Path to your .db or .sqlite file
  • Read-Only Mode (optional) - Open database in read-only mode
  • No Username/Password - SQLite doesn't require authentication

Step-by-Step Connection

  1. Select SQLite from the database type dropdown
  2. Choose Database File:
    • Click "Browse" to select your .db or .sqlite file
    • Or enter the full path: /path/to/your/database.db
  3. Configure Options:
    • Read-Only Mode - Enable if you only need to query, not modify
    • WAL Mode - Enable Write-Ahead Logging if supported
  4. Test Connection - Verify file is accessible
  5. Save Connection - Store with a descriptive name

SQLite-Specific Features

  • File-Based - No server required, works with local files
  • Multiple Databases - Can attach multiple SQLite databases
  • Full SQL Support - Supports all SQLite SQL features
  • Lightweight - Perfect for development and testing

Managing Multiple Connections

Once you've connected multiple databases, here's how to manage them effectively:

Switching Between Databases

  • Connection Selector - Use the dropdown in the SQL Editor to switch databases
  • Sidebar - Click on a connection in the sidebar to make it active
  • Query Context - Each query tab remembers its database connection

Organizing Connections

  • Group by Type - Organize connections by database type
  • Naming Convention - Use clear names like "Production PostgreSQL" or "Dev MySQL"
  • Tags/Labels - Add tags to categorize connections (production, staging, development)
  • Favorites - Mark frequently used connections as favorites

Connection Status

PromptQuery shows connection status indicators:

  • 🟢 Green - Connected and active
  • 🟡 Yellow - Connection idle or reconnecting
  • 🔴 Red - Connection failed or disconnected

Testing Connections

Regularly test your connections:

  • Before Important Work - Verify connectivity before running critical queries
  • After Network Changes - Test after VPN or network configuration changes
  • Scheduled Checks - PromptQuery can automatically verify connections periodically

Advanced Configuration Options

SSL/TLS Settings

For secure connections, configure SSL appropriately:

  • SSL Mode Options:

    • disable - No SSL (not recommended for production)
    • require - Require SSL but don't verify certificate
    • verify-ca - Verify certificate authority
    • verify-full - Verify certificate and hostname (most secure)

  • Certificate Management:

    • Upload CA certificates for custom SSL setups
    • Configure client certificates if required
    • Set certificate validation levels

Connection Pooling

PromptQuery manages connection pooling automatically:

  • Idle Connections - Connections are kept alive for quick access
  • Connection Limits - Respects database connection limits
  • Timeout Handling - Automatically handles connection timeouts

Metadata Access Control

Control what schema information PromptQuery accesses:

  • Table Names - Include/exclude specific tables
  • Schema Information - Control access to schema metadata
  • Row Counts - Enable/disable row count statistics
  • Index Information - Control index metadata access
  • Constraint Details - Manage foreign key and constraint information

Best Practices

Security Best Practices

  1. Use SSL/TLS - Always enable SSL for production databases
  2. Limit Metadata Access - Only share necessary schema information
  3. Separate Credentials - Use different credentials for PromptQuery than application databases
  4. Regular Rotation - Rotate database passwords regularly
  5. Read-Only Access - Use read-only users when possible for querying

Performance Best Practices

  1. Connection Limits - Don't create unnecessary connections
  2. Close Unused Connections - Disconnect databases you're not actively using
  3. Optimize Queries - Use PromptQuery's optimization features
  4. Monitor Connections - Keep an eye on connection status

Organization Best Practices

  1. Clear Naming - Use descriptive names for connections
  2. Group by Environment - Separate production, staging, and development
  3. Document Connections - Add notes about connection purpose
  4. Regular Cleanup - Remove unused or outdated connections

Troubleshooting Common Connection Issues

Issue: Connection Timeout

Symptoms: Connection fails with timeout error

Solutions:

  • Check network connectivity to database server
  • Verify firewall rules allow connections
  • Increase connection timeout in advanced settings
  • Check if database server is running and accessible

Issue: Authentication Failed

Symptoms: "Access denied" or "Authentication failed" error

Solutions:

  • Verify username and password are correct
  • Check if user has necessary permissions
  • Verify user is allowed to connect from your IP address
  • Check database user permissions and roles

Issue: SSL/TLS Error

Symptoms: SSL handshake failed or certificate error

Solutions:

  • Verify SSL is properly configured on database server
  • Check SSL mode matches server requirements
  • Upload correct CA certificate if using custom SSL
  • Try different SSL modes (require vs verify-full)

Issue: Database Not Found

Symptoms: "Database does not exist" error

Solutions:

  • Verify database name is spelled correctly
  • Check database exists on the server
  • Verify user has access to the database
  • Check case sensitivity (especially for Linux servers)

Issue: Port Connection Refused

Symptoms: "Connection refused" on specific port

Solutions:

  • Verify port number is correct (check defaults: 5432 for PostgreSQL, 3306 for MySQL)
  • Check firewall allows connections on that port
  • Verify database server is listening on that port
  • Check if port is blocked by network policies

Issue: SQLite File Not Found

Symptoms: SQLite database file cannot be accessed

Solutions:

  • Verify file path is correct
  • Check file permissions (read/write access)
  • Ensure file exists at specified location
  • Check if file is locked by another process

Connection Examples by Use Case

Development Environment

PostgreSQL (Local):
Host: localhost
Port: 5432
Database: myapp_dev
Username: developer
SSL: Disabled

MySQL (Local):
Host: localhost
Port: 3306
Database: myapp_dev
Username: root
SSL: Disabled

Production Environment

PostgreSQL (Cloud):
Host: prod-db.example.com
Port: 5432
Database: myapp_prod
Username: app_user
SSL: Verify-full
SSL Certificate: [Upload CA cert]

MySQL (Cloud):
Host: prod-mysql.example.com
Port: 3306
Database: myapp_prod
Username: app_user
SSL: Require

Testing with SQLite

SQLite (Local File):
File Path: /path/to/test.db
Read-Only: No
WAL Mode: Enabled

Next Steps

Now that you've connected your databases:

  1. Generate Your First Query - Try writing a natural language query
  2. Explore Schema - Browse your database structure in the sidebar
  3. Test AI Generation - See how context-aware SQL generation works
  4. Optimize Queries - Use PromptQuery's optimization features
  5. Connect More Databases - Add additional databases as needed

Conclusion

PromptQuery's multi-database support allows you to work with all your databases from a single interface. Whether you're using PostgreSQL, MySQL, SQL Server, Oracle, SQLite, or MariaDB, the connection process is straightforward and secure.

Remember to:

  • ✅ Use SSL/TLS for production connections
  • ✅ Configure appropriate metadata access controls
  • ✅ Use clear naming conventions for connections
  • ✅ Test connections regularly
  • ✅ Follow security best practices

Ready to connect your databases? Get started with PromptQuery and experience unified database management with AI-powered SQL generation!

Need Help?

Back to Blog

© 2025 Prompt Query. All rights reserved.

Privacy PolicyTerms of Service