SeaTable now has a new SQL query interface - More convenience and boosted performance

Since SeaTable 2.3, we’ll have an SQL query interface. It is a step forward in data analysis and performance benchmarking. Together with 2.3, we also deployed an SQL Query plugin in the plugins market (Cloud users don’t have to visit the plugins market as they can directly add the plugins in their cloud interface).

SQL in SeaTable

The SQL query in SeaTable is the same as MySQL, so you can directly start using it in SeaTable if you are familiar with MySQL.

Basically, an SQL query in SeaTable looks like this:

SELECT [DISTINCT] fields FROM table_name [WhereClause] [OrderByClause] [GroupByClause] [Limit Option]

The response is a JSON object. Currently, there’re still some limitations, for example, the JOIN method is not yet supported.

For further information, refer to the SQL Reference - SeaTable Scripts Programming Manual.

To query your base’s data, you can use SQL in

Query with the SQL Query Plugin

Yes - you can directly use the SQL query language on the web UI. Just open the SQL query plugin and start typing your command. The results are in a table that is very friendly for the eyes:

Query with The REST API

With the SeaTable API, you can query a table, or query the linked records of certain rows.

Let’s say, we have a very simple table with the dates and sales volumn, recording multiple sales activities of each day:

Now, with the SQL query API request, I would like to get the sum of each day’s sales volumn. So I could send the following API request and get the desired results as in the screenshot below:

Query with the Python SDK

The REST API has been packed into the SeaTable Python SDK.
Here are two simple examples:

base.query('select name, price, year from Bill')

or

base.query('select name, sum(price) from Bill group by name')

will return the results, respectively:

[
    {'_id': 'PzBiZklNTGiGJS-4c0_VLw', 'name': 'Bob', 'price': 300, 'year': 2019},
    {'_id': 'Ep7odyv1QC2vDQR2raMvSA', 'name': 'Bob', 'price': 300, 'year': 2021},
    {'_id': 'f1x3X_8uTtSDUe9D60VlYQ', 'name': 'Tom', 'price': 100, 'year': 2019},
    {'_id': 'NxeaB5pDRFKOItUs_Ugxug', 'name': 'Tom', 'price': 100, 'year': 2020},
    {'_id': 'W0BrjGQpSES9nfSytvXgMA', 'name': 'Tom', 'price': 200, 'year': 2021},
    {'_id': 'EvwCWtX3RmKYKHQO9w2kLg', 'name': 'Jane', 'price': 200, 'year': 2020},
    {'_id': 'BTiIGSTgR06UhPLhejFctA', 'name': 'Jane', 'price': 200, 'year': 2021}
]

or

[
    {'SUM(price)': 600, 'name': 'Bob'},
    {'SUM(price)': 400, 'name': 'Tom'},
    {'SUM(price)': 400, 'name': 'Jane'}
]

Specialities in SeaTable SQL

The most common use cases in SeaTable involve data collection and analysis. For this reason, we have made our user’s lives easier by making SeaTable SQL support the following special functions:

  • STARTOFWEEK(date, weekStart): returns the first day of the week where date is in. weekStart can be either “Sunday” or “Monday” as per the first day of a week.
  • Quarter(date): Returns the quater number (1, 2, 3, or 4) of the date.
  • ISODate(date): Returns ISO format string for the date. E.g. “2020-09-08”.
  • ISOMonth(date): Returns ISO format string for the month where date is in. E.g. “07”.
1 Like

At the moment, it is not possible to download the plugin from https://market.seatable.io/plugins/?name=sql-query

Great work, thank you!
To make it really powerfull it would be usefull to allow “link to other records” fields and joins to other tables
Can this may be added as feature request for a future version? For the API as well for the UI
Thank you

Thanks for the reminder, this problem is solved now and you can download the plugin!

1 Like