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:
- Database Type – Choose which database you want to connect to (PostgreSQL, MySQL, SQL Server, etc.).
- Connection Name – Give your connection a recognizable name.
- 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.
- Test & Extract Schema – The app will automatically test your connection and attempt to extract your database schema (i.e., tables and columns).
- 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.
- 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
Open the Connections Page
Navigate to Connections in the app.Click “Add New Connection”
You’ll land on the first step, which is Select Database Type.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.
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.
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
or127.0.0.1
will not work.
- 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
- You may either:
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.
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.
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:
- Select “Other” when choosing a Database Type.
- Name Your Connection as usual.
- Choose Manual Entry or “Skip Database Connection” to proceed.
- 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.
- 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
- Use Descriptive Names – Name your connection based on the environment and use case (e.g., “Prod MySQL”).
- Document Thoroughly – The more context you provide, the better the AI’s query suggestions will be.
- Maintain Consistency – Keep naming conventions consistent across columns, tables, and schemas.
- 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.