Skip to content

Working with tables

TiDB uses tables to organize and store collections of related data. It provides flexible schema definition capabilities, allowing you to structure your tables according to your specific requirements.

A table can contain multiple columns with different data types to store various kinds of data. Supported data types include text, numbers, vectors, binary data (BLOB), JSON, and more.

Tip

For a complete working example, see the basic example in our repository.

Create a table

Using TableModel

TiDB provides a TableModel class that represents the schema of a table. This class is compatible with the Pydantic Model and allows you to define the table structure in a declarative way.

In the following example, you create a table named items with these columns:

  • id: a primary key column with an integer type
  • content: a text type column
  • embedding: a vector type column with 3 dimensions
  • meta: a JSON type column

After you connect to the database using PyTiDB and obtain a client instance, you can create a table with the create_table method.

from pytidb.schema import TableModel, Field, VectorField
from pytidb.datatype import TEXT, JSON

class Item(TableModel):
    __tablename__ = "items"

    id: int = Field(primary_key=True)
    content: str = Field(sa_type=TEXT)
    embedding: list[float] = VectorField(dimensions=3)
    meta: dict = Field(sa_type=JSON, default_factory=dict)

table = client.create_table(schema=Item, mode="overwrite")

The create_table method accepts these parameters:

  • schema: The TableModel class that defines your table structure.
  • mode: The creation mode of the table.
    • create (default): Creates the table if it does not exist; raises an error if it already exists.
    • exists_ok: Creates the table if it does not exist; does nothing if it already exists.
    • overwrite: Drops the existing table and creates a new one. This is useful for testing and development, but not recommended for production environments.

Once the table is created, you can use the table object to insert, update, delete, and query data.

Use the CREATE TABLE statement to create a table.

CREATE TABLE items (
    id INT PRIMARY KEY,
    content TEXT,
    embedding VECTOR(3),
    meta JSON
);

Add data to a table

With TableModel

You can use a TableModel instance to represent a record and insert it into the table.

To insert a single record:

Use the table.insert() method to insert a single record into the table.

table.insert(
    Item(
        id=1,
        content="TiDB is a distributed SQL database",
        embedding=[0.1, 0.2, 0.3],
        meta={"category": "database"},
    )
)

Use the INSERT INTO statement to insert a single record into the table.

INSERT INTO items(id, content, embedding, meta)
VALUES (1, 'TiDB is a distributed SQL database', '[0.1, 0.2, 0.3]', '{"category": "database"}');

To insert multiple records:

Use the table.bulk_insert() method to insert multiple records into the table.

table.bulk_insert([
    Item(
        id=2,
        content="GPT-4 is a large language model",
        embedding=[0.4, 0.5, 0.6],
        meta={"category": "llm"},
    ),
    Item(
        id=3,
        content="LlamaIndex is a Python library for building AI-powered applications",
        embedding=[0.7, 0.8, 0.9],
        meta={"category": "rag"},
    ),
])

Use the INSERT INTO statement to insert multiple records into the table.

INSERT INTO items(id, content, embedding, meta)
VALUES
    (2, 'GPT-4 is a large language model', '[0.4, 0.5, 0.6]', '{"category": "llm"}'),
    (3, 'LlamaIndex is a Python library for building AI-powered applications', '[0.7, 0.8, 0.9]', '{"category": "rag"}');

Query data from a table

To fetch records from a table:

Use the table.query() method to fetch the records from the table.

Example: Fetch the first 10 records

result = table.query(limit=10).to_list()

Use the SELECT statement to fetch the records from the table.

Example: Fetch the first 10 records

SELECT * FROM items LIMIT 10;

To fetch records based on query conditions:

Pass the filters parameter to the table.query() method.

result = table.query(
    filters={"meta.category": "database"},
    limit=10
).to_list()

Use the WHERE clause to filter records.

Example: Fetch the 10 records with category "database"

SELECT * FROM items WHERE meta->>'$.category' = 'database' LIMIT 10;

For a complete list of supported filter operations and examples, refer to the filtering guide.

Update data in a table

Use the table.update() method to update records with filters.

Example: Update the record whose id equals 1

table.update(
    values={
        "content": "TiDB Cloud Serverless is a fully managed, auto-scaling cloud database service",
        "embedding": [0.1, 0.2, 0.4],
        "meta": {"category": "dbass"},
    },
    filters={
        "id": 1
    },
)

Use the UPDATE statement to update records with filters.

Example: Update the record whose id equals 1

UPDATE items
SET
    content = 'TiDB Cloud Serverless is a fully managed, auto-scaling cloud database service',
    embedding = '[0.1, 0.2, 0.4]',
    meta = '{"category": "dbass"}'
WHERE
    id = 1;

Delete from a table

Use the table.delete() method to delete records with filters.

Example: Delete the record where id equals 2

table.delete(
    filters={
        "id": 2
    }
)

Use the DELETE statement to delete records with filters.

Example: Delete the record where id equals 2

DELETE FROM items WHERE id = 2;

Truncate a table

To remove all data from the table but keep the table structure, use the table.truncate() method.

table.truncate()

To check that the table is truncated, verify that it contains 0 rows.

table.rows()

To remove all data from the table but keep the table structure, use the TRUNCATE TABLE statement.

TRUNCATE TABLE items;

To check that the table is truncated, verify that it contains 0 rows.

SELECT COUNT(*) FROM items;

Drop a table

To permanently remove a table from the database, use the client.drop_table() method.

client.drop_table("items")

To check that the table is removed from the database:

client.table_names()

To permanently remove a table from the database, use the DROP TABLE statement.

DROP TABLE items;

To check that the table is removed from the database:

SHOW TABLES;