SQL Query

​

Creating a Database Connection

1. Open the Table: First, open the table you want to connect to.
2. Select API Button: Find and click the API button on the right.
3. Choose PostgreSQL Connection: In the popup options, select PostgreSQL Connection.
4. Enter Settings Interface: Click create, and the system will automatically create a read-only PostgreSQL user, including username, password, and database connection address.

​

Connection Methods

• Standard PostgreSQL Clients: Such as DataGrip, Navcat, TablePlus, etc.
• BI Products: Such as PowerBI, Metabase, Superset, etc.
• Low-Code Software: Such as Appsmith, Budibase, etc.
• Application Code: Through corresponding PostgreSQL drivers for different programming languages.

​

Getting the BaseId

1. Click on any table to enter the interface. The string starting with bse in the URL is the baseId.

2. Enter the base design interface (⚙ icon in the top right corner of the table), where the schema name is the baseId.

​

Connecting to the Database

While connection methods may vary slightly across different products, they follow a similar pattern. Here’s a simple example:

​

Closing the Database Connection

1. Access Database Connection Interface: Click delete in the Database Connection interface to close the connection.
2. Password Clearing: After deletion, the old password will be cleared. Creating a new connection will generate new credentials.

​

Database Table Contents

Click the ⚙️ icon in the top right corner to enter the Base Design Interface, which contains details about the table’s name and structure in the database. This is very helpful for external data queries.

​

Database Table Names

Changing a table name in Teable won’t automatically sync with the table name in the database. You must explicitly modify the table name in the physical database.

After modifying the table name, you can directly use SQL for queries. Here’s an example query to fetch the first 100 rows from the current table:

SELECT * from "bseamGnQT65TVSCzIaC"."clients" limit 100

When querying, you need to include the schema name (BaseId) and use the physical database table name. Note that in PostgreSQL, you must use double quotes " around schema and table names to preserve case sensitivity.

​

User-Created Fields

All fields you create in the table.

Note that the field (column) names in the database won’t exactly match the names in the Teable interface. Please check the field names in the database design interface before querying and make necessary modifications.

Changing field names in Teable won’t automatically sync with dbFieldName. You must explicitly modify the dbFieldName to change the actual column name in PostgreSQL.

​

Teable System Fields

System fields cannot be renamed:

1. __id Unique ID
2. __version Version number
3. __auto_number Auto-incrementing number, primary key
4. __created_time Creation time
5. __last_modified_time Last modified time
6. __created_by Creator ID
7. __last_modified_by Last modifier ID
8. View index fields: Fields starting with __row, used to maintain record order in the current view
9. Foreign keys: If there are relationship fields, logical foreign keys will be created, starting with __fk

​

Junction Tables

Tables starting with junction_ are used to handle ManyToMany and unidirectional relationships.

​

Connection Limits

Database connections act as bridges between applications and the database for information exchange. Our system allows a maximum of 3 simultaneous connections. This means only 3 applications can communicate directly with the database at the same time (one application might use multiple connections, depending on your settings). Beyond this limit, other users must wait until a connection becomes available. This limitation helps maintain system stability and efficiency, ensuring a good experience for all users. You can view the current number of connections in the connection settings interface.

​

Permission Notes

The created connection user’s access scope is limited to the current base (called database in the Teable interface), which corresponds to a schema in PostgreSQL. Schemas in PostgreSQL provide excellent permission isolation, ensuring the current connection can only access tables within that schema. However, PostgreSQL roles created will have the ability to view all schema names, which is why you might see many baseIds (actually schema names) as shown in the image below when connecting through external applications. Rest assured that besides this ID, no other user content can be accessed.

​

Direct Database Writing

Teable does not allow direct database operations through external connections. Directly modifying the database content is dangerous and may break application logic, leading to crashes or data loss. Teable does not provide writable database connections. If you need to modify data, please use the Teable API, which provides more granular permission control and well-encapsulated operations.

​