Text2SQL.ai logo

Database Connections

Published on

Below you will find instructions for setting up a new database connection in text2sql.ai, including how to automatically extract and review your schema.

1. Overview of the Connection Flow

When adding a new database connection, the app guides you through the following steps:

  1. Database Type – Choose which database you want to connect to (PostgreSQL, MySQL, SQL Server, etc.).
  2. Connection Name – Give your connection a recognizable name.
  3. Database Credentials – Enter either a single connection URL or specify the host, port, database name, username, and password. You can also enable SSL/TLS (and optionally provide a CA certificate) if required by your environment.
  4. Test & Extract Schema – The app will automatically test your connection and attempt to extract your database schema (i.e., tables and columns).
  5. Review, Select & Comment – You’ll see your database schema in a viewer, where you can add comments to help the AI better understand your data and choose specific schemas, tables, or columns you want to import.
  6. Create Connection – Confirm and save the connection. It will then appear in your list of connections.

Below is a more detailed walkthrough:

2. Adding a New Connection

  1. Open the Connections Page
    Navigate to Connections in the app.

  2. Click “Add New Connection”
    You’ll land on the first step, which is Select Database Type.

Step 1: Connection Name

Provide a clear name for your connection, such as “Sales-Production” or “Analytics-Postgres.”

Step 2: Select Your Database Type

Choose from:

  • MySQL, PostgreSQL, SQL Server (direct connection supported)
  • Oracle PLSQL, SQLite, Snowflake, Redshift, BigQuery, MariaDB, or Other (use Copy / Paste Schema or AI Schema Parsing)

Step 3: Choose Your Configuration Method

  • Connect Your Database
    If your database is MySQL, PostgreSQL, or SQL Server, you can establish a direct connection. You’ll then be guided to enter credentials or a connection URL.
  • Copy / Paste Schema
    If direct connections are unavailable or you prefer manual control, copy an extraction script (if offered) or simply paste SQL scripts or JSON describing your schema.
  • AI Schema Parsing
    Paste any text describing your schema, such as a SQL Script, migration files or textual ER diagrams, and let our AI extract the schema.

Step 4: Enter Credentials or Import Schema

Depending on your selection:

  • Direct Connection
    Fill in host, port, database, username, and password, or provide a connection URL. Enable SSL if needed, and supply a CA certificate if required. Whitelist our static IP 165.227.187.107.
    You can test the connection before proceeding.
  • Copy / Paste Schema
    Copy the extraction script into your database client (if applicable), run it, then paste the output here. If a direct parse fails, the system tries AI automatically.
  • AI Schema Parsing
    Paste any text containing your table definitions, schema references, or migrations, then let our AI analyze and build the schema structure.

Step 5: Review Your Schema and Create Connection

  • Schema Viewer
    Examine each table and column. You can deselect unneeded elements and add comments for clarity. Comments are optional but can improve AI-generated queries.
  • Finalizing
    After reviewing, click Create Connection to save. Your new connection will appear in the Connections list.
  1. Step 6: Create Connection
    • After reviewing the schema, optionally selecting specific database objects, and adding any comments, click Create Connection.
    • You'll be redirected back to the Connections page, and your new connection will appear in the list.

4. Adding Comments to Your Schema

Comments help the AI better understand your database structure and can significantly improve query generation. This is especially useful if:

  • Column names are not descriptive.
  • You have multiple similar tables or columns.
  • You want to share specific business logic or constraints with the AI.

Where to Add Comments:

  • Database Level – Provide high-level context about the overall purpose of the database.
  • Schema Level – Document the purpose of schemas or special naming conventions.
  • Table Level – Explain why a table exists or how it's intended to be used.
  • Column Level – Describe any domain constraints, data types, or relationships not obvious from the column name alone.

In the Schema Viewer, click the Pen icon next to a specific element to open an editor and add your comment. Save your changes once finished.

5. Syncing Your Database Schema

Database schemas evolve over time as your application grows. With the Schema Sync feature, you can easily update your connection's schema while preserving all your valuable comments.

Why Sync Your Schema?

  • Keep Up With Changes – Tables, columns, and relationships change during development cycles
  • Preserve AI Context – Maintain all comments and context you've added to help the AI understand your data
  • Save Time – No need to re-create connections or manually transfer comments when schema changes occur

Sync Methods

Depending on how your connection was created, you have two synchronization options:

  1. Direct Sync – For connections with stored database credentials

    • One-click process that automatically reconnects and extracts the updated schema
    • Preserves all existing comments for tables and columns that still exist
    • Available for MySQL, PostgreSQL, and SQL Server

  2. Manual Sync – For connections created via AI parsing or manual schema input

    • Guided process to update your schema while keeping existing comments
    • Choose from the same methods available during connection creation (direct connection, copy/paste schema, or AI parsing)

Using the Sync Feature

  1. Navigate to Edit Connection

    • Go to the Connections page
    • Find the connection you want to update
    • Click "Edit" to open the connection details

  2. Initiate Sync

    • Click the "Sync Schema" button in the connection details page
    • For direct connections, the system will immediately attempt to extract the updated schema
    • For manual connections, you'll be prompted with connection options

  3. Review Changes

    • After synchronization, you'll see the updated schema with preserved comments
    • Review any new tables or columns that have been added
    • Add comments to new elements as needed

  4. Save Updated Connection

    • After reviewing, click "Save Changes" to update your connection with the new schema

Technical Notes

  • Comments are intelligently matched to schema elements that still exist after the update
  • The system optimizes performance for large schemas
  • All security protocols for credential handling remain in place during synchronization

6. Troubleshooting Common Issues

Below are some frequently encountered errors and tips on how to resolve them:

  • SSL/TLS Issues Check if your database requires SSL or TLS for connections and ensure you have enabled SSL in text2sql.ai. If you have a custom CA certificate, add it in the provided field.
  • Firewall / Whitelisting Issues Your database must allow inbound connections from our static IP (viewable in the application). Be sure to whitelist or open any necessary ports on your firewall or VPC.
  • DNS / Hostname Resolution Verify the hostname is correctly spelled and publicly resolvable. If DNS is misconfigured, your connection may fail.
  • Authentication Issues Confirm that the username and password you've entered are correct and that the user has appropriate permissions.
  • Timeouts / Network Issues Check for firewall rules, ensure the host is reachable, and consider whitelisting the IP above or setting up a VPN tunnel.
  • Cannot Connect to localhost The cloud service can't directly access localhost or 127.0.0.1. Use a public host, or set up a secure tunnel or proxy.
  • Firewall or VPC Settings If you're on a private network, you'll need to allow inbound connections from our static IP (viewable in the application) or set up a VPN tunnel.
  • Invalid Credentials Double-check you've entered the correct database name, username, and password. If using a URL, make sure it's properly formatted..

7. Best Practices

  1. Use Descriptive Names – Name your connection based on the environment and use case (e.g., "Prod MySQL").
  2. Document Thoroughly – The more context you provide, the better the AI's query suggestions will be.
  3. Maintain Consistency – Keep naming conventions consistent across columns, tables, and schemas.
  4. Stay Secure – Provide credentials only to the environment you trust; consider read-only credentials for text2sql.ai to limit risk.
  5. Sync Regularly – Keep your schema up-to-date as your database evolves to ensure accurate AI query generation.

7. Supported Databases

We support direct connections for the following databases:

  • MySQL
  • PostgreSQL
  • SQL Server

We support official extraction scripts for the following databases:

  • Oracle PLSQL
  • SQLite
  • Snowflake
  • Redshift
  • BigQuery
  • MariaDB

We support AI schema parsing for any other database type, including:

  • ClickHouse
  • Hive
  • Spark
  • Redshift
  • SQLite
  • DB2
  • …and more!

If your database type isn't listed, please contact us and we'll do our best to accommodate.

You’re all set! Head over to the Connections page to set up your first connection or create additional ones. If you have any questions or issues, feel free to reach out via email.