bitable-mysql-driver

bitable-mysql-driver

No longer maintained. For Feishu Bitable operations, use lark-cli instead. It already provides a capable direct interface, and modern AI models can work with it without this third-party SQL/MySQL driver. This repository remains available for existing users, but no further fixes, dependency updates, or security patches are planned. Do not use it for new projects.

Feishu open platform provides bi-table api. Changed the sdk as a mysql driver for quick start.

简体中文 README

quick start

# dsn = "bitable://<app_id>:<app_secret>@open.feishu.cn/<app_token>"
go run cmd/bsql/main.go 'bitable://<app_id>:<app_secret>@open.feishu.cn/<app_token>'

The driver does not emit verbose Feishu SDK request or response bodies because they can contain credentials and access tokens. Use application-level debug logs for SQL diagnostics.

> show tables;
id: tblTqyMTqUTFrDQc
name: table1
revision: 3

id: tblebGSJc65Km9qG
name: table2
revision: 5

> show create view tblTqyMTqUTFrDQc;
id: vewbe3eCpw
name: Grid
type: grid

> show columns from tblebGSJc65Km9qG;
field: fldACpt0Hp
type: 1
comment: Text
extra: null

field: fld5Iuk6lB
type: 3
comment: Select
extra: {"options":[{"name":"option_one","id":"opt832qE9t"}]}

field: fld0ItJzco
type: 11
comment: Person
extra: {"multiple":true}

Useful SQL

SHOW TABLES;
SHOW COLUMNS FROM table;
SHOW CREATE VIEW table;

# Select
SELECT * FROM table limit 10;
SELECT * FROM table WHERE `Number` >= 2 and `Person` in ('XX') limit 10;
SELECT * FROM table WHERE `Date` >= TODATE('2021-12-16');
SELECT * FROM table WHERE `Number` in (3, 1) order by `Number` desc limit 10;
SELECT * FROM table WHERE record_id = 'rec9eOiv5d';
SELECT * FROM table WHERE `Select` IS NOT NULL;
SELECT * FROM table WHERE `Select` IS NULL;


# DML
CREATE TABLE table
(
    `Text`   text,
    `Select` varchar(3) COMMENT '{"options":[{"name":"optione_one"}]}',
    `Person` varchar(11) COMMENT '{"multiple":true}'
) COMMENT 'Grid';
CREATE VIEW kanban.`kanban` AS SELECT * FROM table;
DROP TABLE table;

# DDL
ALTER TABLE table ADD COLUMN `Text` varchar(1) COMMENT '{"multiple":true}';
ALTER TABLE table CHANGE COLUMN `NewDate` `Date` varchar(5);
ALTER TABLE table MODIFY COLUMN `NewDate` text;
ALTER TABLE table RENAME COLUMN `Date` TO `NewDate`;
ALTER TABLE table DROP COLUMN `Date`;

# Records
INSERT INTO table (`Number`) VALUES (3), (3.0), (0.3), (3.3);
INSERT INTO table (`Text`, persons.`Person`) VALUES ('F1', ''), ('F2', '[{"id":"ou_<open_user_id>"}]');
Update table set `Select`='Y' WHERE record_id = 'XX';
Update table set `Select`='N' WHERE `Person` = '<person name>';
DELETE FROM table WHERE record_id = 'XX';
DELETE FROM table WHERE `person` = 'XX';

More examples, see driver_test.go

特殊用法:

• show create view: instead of 'show views'，use show create view show a view meta
• create view kanban.{view_name} as select * from table: when creating a view，kanban is the ViewType for view，more about ViewType: model ViewType。
• "persons.`person`": a special type for person fieldType

Special type: More about FieldType modelFieldType

• persons: json string， modelRecordPerson
• url: json string， modelRecordUrl
• attachments: json string， modelRecordAttachments
• options: json string， modelRecordOptions

example:

  url := `{"link":"https://www.google.com","text":"Google"}`
  options := `["option_one", "option_two"]`
  persons := `[{"id":"ou_fcb313360e8b813e8017771f6bbb9533"}]`
  attachments := `[{"file_token":"boxbcqtaK3s6cCsHPhzddAXVdhc"}]`

use driver for code

package main

import (
	"database/sql"
	"fmt"
	"os"

	// load bitable driver
	_ "github.com/luw2007/bitable-mysql-driver/driver"
	"github.com/sirupsen/logrus"
)

var (
	appID     = os.Getenv("APP_ID")
	appSecret = os.Getenv("APP_SECRET")
	appToken  = os.Getenv("APP_TOKEN")
	dsn   = fmt.Sprintf("bitable://%s:%s@open.feishu.cn/%s", appID, appSecret, appToken)
)

func main() {
	logrus.SetLevel(logrus.DebugLevel)
	logrus.SetOutput(os.Stdout)
	db, err := sql.Open("bitable", dsn)
	if err != nil {
		panic(err)
	}
	rows, err := db.Query("SHOW TABLES")
	if err != nil {
		panic(err)
	}
	var tid, name, version string
	for rows.Next() {
		if err := rows.Scan(&tid, &name, &version); err != nil {
			panic(err)
		}
		fmt.Println(tid, name, version)
	}
}

$ go run main.go

tblTqyMTqUTFrDQc table1 4
tblebGSJc65Km9qG table2 11
tblssnegcbTL6pp2 table_create 5

Bitable feature

App

• bitable meta

table

• list table
• create table
• delete table
• batch delete table
• batch create table

view

• list view
• add view
• delete view

record

• list record
• query record
• add record
• batch add record
• update record
• batch update record // use where condition，instead of record_id
• delete record
• multi delete record // use where condition，instead of record_id

field

• list field
• add field
• update field
• delete field

TODO:

• test suite，use lark mock api
• add to usql

Thanks：

• lark
• pingcap/parser