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.

  3. Step 1: Select Database Type

    • Choose from MySQL, PostgreSQL, SQL Server, or “Other.”
    • If your database is not one of the listed types, select Other to do a manual schema entry.
  4. Step 2: Enter Connection Name

    • Type in a recognizable name for your new connection (e.g., “Sales Production MySQL,” “Analytics Postgres,” etc.).
    • Click Continue to proceed.
  5. Step 3: Enter Database Credentials

    • You may either:
      • Provide a single connection URL, or
      • Enter Host, Port, Database Name, Username, and Password individually.
    • Enable SSL (optional) if your database requires a secure connection. You can also upload or paste your CA Certificate if needed.
    • Click Connect (or Test Connection) to validate your credentials.
      • Important: This software runs in the cloud from a static IP address. Your database must be accessible at that IP, or you must set up a secure tunnel/proxy. Connecting to localhost or 127.0.0.1 will not work.
  6. Step 4: Automatic Schema Extraction & Review

    • If the connection is successful, text2sql.ai will automatically run a read-only query to fetch your schema details.
    • You’ll see a Schema Viewer displaying all schemas, tables, columns, and any foreign key relationships.
  7. Step 5: Review, Select & Comment

    • In the Schema Viewer, you can check/uncheck the specific schemas, tables, or columns you want to include in your final connection. This is particularly useful if you only want to expose a portion of your database to the AI.
    • Click the edit (pen) icon next to a database, schema, table, or column to add descriptive comments. These comments guide the AI and often lead to more accurate generated SQL queries.
  8. 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.

3. Manual Schema Entry (Other Databases)

If your database type is not yet supported for direct credential-based extraction, or you cannot grant network access:

  1. Select “Other” when choosing a Database Type.
  2. Name Your Connection as usual.
  3. Choose Manual Entry or “Skip Database Connection” to proceed.
  4. Manually Provide Schema
    • You’ll be prompted to input your schema in JSON format, or another structured format that describes tables, columns, and optionally relationships.
    • For example, here's a MongoDB schema:
// Blog Platform
posts: {
  _id: ObjectId,
  title: String,           // Post headline
  content: String,         // Main post content
  author: {
    _id: ObjectId,
    name: String,         // Author's display name
    email: String         // Author's contact email
  },
  tags: Array<String>,    // Categories/topics for the post
  comments: [{
    user: String,         // Commenter's username
    content: String,      // Comment text
    created_at: Date      // When comment was posted
  }],
  created_at: Date,       // When post was first published
  updated_at: Date        // Last modification timestamp
}

users: {
  _id: ObjectId,
  username: String,       // Unique username for login
  email: String,         // Primary contact method
  profile: {
    bio: String,         // User's self description
    avatar_url: String,  // Profile picture URL
    social_links: {
      twitter: String,   // Twitter profile URL
      github: String     // GitHub profile URL
    }
  }
}
  • Tip: You can add comments in this schema to help the AI interpret the meaning of columns and tables.
  1. Save
    • Finally, click Create Connection to store the connection.

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

6. 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.

7. Supported Databases

The automated credential-based flow currently supports:

  • MySQL
  • PostgreSQL
  • SQL Server

If you choose Other, you can manually enter your schema. This works for:

  • Oracle PLSQL
  • MongoDB
  • DynamoDB
  • BigQuery
  • Snowflake
  • 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.