📙 Documentation – 🚀 Comprehensive example
Caution: This project is in active development and should currently be considered alpha. Therefore, bugs and (fundamental) breaking changes to the API can occur at any time.
This package provides a CRUD (Create, Read, Update, Delete) client for Baserow, an open-source alternative to Airtable. Baserow offers a spreadsheet-like interface in the browser for accessing a relational database. Currently, there are numerous (partial) implementations of the Baserow API in Python. baserowdantic emerged from our specific needs and aims to achieve the following:
- Support CRUD operations on Baserow tables.
- Optionally abstract the operations to a Pydantic-like model with all the benefits of Pydantic (validation, automatic (de-)serialization of data). This is what is understood as ORM-like.
- Be fully asynchronous.
As such, it is quite opinionated and supports only a small subset of the API. Users seeking more functionality may consider alternatives like the python-baserow-client. Interaction with Baserow is facilitated through the definition of Pydantic models, ensuring data validation. The library is written to be fully asynchronous, integrating well with frameworks such as FastAPI.
The package can be used in two different ways:
- Direct Editing with API Basic Client: You can directly edit with Baserow using the API Basic Client.
- Executing Actions on a Pydantic Model: Actions can be executed on a pydantic model. In this case, the table structure only needs to be defined once, and the library uses this information for all actions such as creating tables, reading and writing entries, and more.
The installation is as simple as:
pip3 install baserowdanticThis sections offers a hands-on look at the ORM capabilities of baserowdantic. Not in the mood for lengthy explanations? Then check out the examples/orm.py example directly. It demonstrates how to work with all the implemented field types.
This introduction provides only a brief overview of the functions. For a more detailed description of all features, please refer to the sections below.
For more information please refer to the documentation below.
The connection to Baserow is managed through a client object. For this simple example, we will define a global client singleton which is used by default in all methods. Authentication is possible with a token or with login credentials. Creating tables requires login with credentials.
GlobalClient.configure(
"https://your.baserow.instance",
email="[email protected]",
password="your-secret-password",
)First, we need to define the structure of the two tables (authors and books) in a Table model. Please note the class variables table_id and table_name. These link the model to the corresponding table in Baserow.
from baserow.client import GlobalClient
from baserow.field import FileField, SelectEntry, SingleSelectField
from baserow.field_config import PrimaryField
from baserow.table import Table, TableLinkField
from pydantic import Field, ConfigDict
from typing_extensions import Annotated
class Author(Table):
# This class variable defines the ID of the table in Baserow. It can be
# omitted if the table has not been created yet.
table_id = 23
# Name of the Table in Baserow.
table_name = "Author"
# This model_config is necessary, otherwise it won't work.
model_config = ConfigDict(populate_by_name=True)
# Defines the name field as the primary field in Baserow
name: Annotated[str, Field(alias=str("Name")), PrimaryField()]
# Use the alias annotation if the field name in Baserow differs from the
# variable name.
age: Optional[int] = Field(
default=None,
alias=str("Age"),
description="This field description will be visible for Baserow users",
)
# Select fields are represented as enums. Therefore we define one for the genres.
class Genre(str, enum.Enum):
FICTION = "Fiction"
EDUCATION = "Education"
MYSTERY = "Mystery"
# The Book model demonstrates some more advanced field types.
class Book(Table):
table_id = 42
table_name = "Book"
model_config = ConfigDict(populate_by_name=True)
title: Annotated[str, Field(alias=str("Title")), PrimaryField()]
# Link to the Author.
author: Optional[TableLinkField[Author]] = Field(
default=None,
alias=str("Author"),
)
# A single select field.
genre: Optional[SingleSelectField[Genre]] = Field(
default=None,
alias=str("Genre"),
)
# Store files like a cover image.
cover: Optional[FileField] = Field(
default=None,
alias=str("Cover"),
)With the model defining the table structure, baserowdantic's Table.create_table() can create the tables based on it. This step requires authentication using login credentials (JWT Tokens, more info here). The table_id ClassVar does not need to be set in the model when initially creating the table in Baserow. However, it must be set afterward to allow further modifications to the table.
The method requires the database ID where the table should be created. You can find this ID in the Baserow user interface.
await Author.create_table(227)
await Book.create_table(227)Now that the tables are set up in the database, you can start populating them with entries using `Table.create(). The following example provides insights into the various methods available.
# Let's start by creating a few authors.
john = await Author(name="John Doe", age=23).create()
jane = await Author(name="Jane Smith", age=42).create()
anna = await Author(name="Anna Thompson", age=36).create()
# Let's continue with the books. Note how we link the newly created authors to
# the books and also add covers from both a local file and a URL.
first_book = await Book(
title="The Great Adventure",
genre=SingleSelectField.from_enum(Genre.FICTION),
author=TableLinkField[Author].from_value(john.id),
cover=await FileField.from_file(open("path/to/cover.png", "rb")),
).create()
second_book = await Book(
title="Mystery of the Night",
genre=SingleSelectField.from_enum(Genre.MYSTERY),
author=TableLinkField[Author].from_value(jane.id),
cover=await FileField.from_url("https://picsum.photos/id/14/400/300")
).create()Please note that Table.create() only returns a MinimalRow, which contains only the entry's id. The complete dataset must be retrieved using a Table.by_id() query.
complete_first_book_entry = await Book.by_id(first_book.id)Head now to your baserow installation. You'll find two new tables »Author« and »Book«. Which look something like this:
When adding large amounts of data, it is recommended to use the batch functionality of the BasicClient(). In this case, only one API call is made with all the newly added items. See this example in examples/orm.py.
Now that records are present in the table, you can start querying them. Besides a simple query by unique ID using Table.by_id(), you can also formulate complex query filters (based on a AndFilter or OrFilter). Additionally, you can set the sorting, result page size, and the number of results. If you want to fetch all entries, you can set the size parameter to -1.
# Getting the entry by its unique ID.
complete_jane_record = Author.by_id(jane.id)
print(complete_jane_record)
# An example of a more complex query: All authors between the ages of 30 and 40,
# sorted by age.
filtered_authors = await Author.query(
filter=AndFilter().higher_than_or_equal("Age", "30").lower_than_or_equal("Age", "40"),
order_by=["Age"],
)
print(filtered_authors)The results from Baserow are paginated (default is 100 per request, maximum can be set to 200 using the size parameter). If desired, the library can automatically handle querying larger tables through multiple requests by setting size to -1. Use this option with caution, as it can create server load for large tables.
all_books = await Author.query(size=-1)
print(f"All books: {all_books}")Let's now take a look at Linked Fields. For linked entries, initially only the key value and the row_id of the linked records are available. Using TableLinkField.query_linked_rows(), the complete entries of all linked records can be retrieved. When dealing with complex database structures where many rows have multiple linked entries, this can lead to significant wait times due to repeated queries. To address this, there is an option to cache the results. If TableLinkField.cached_query_linked_rows() is used, the dataset is queried only the first time.
book = Book.by_id(BOOK_ID)
authors = await book.author.query_linked_rows()
print(f"Author(s) of book {book.title}: {authors}")
# Because the query has already been performed once, the cached result is
# immediately available.
print(await book.author.cached_query_linked_rows())To access stored files, you can use the download URL. Please note that for security reasons, this links have a limited validity.
for file in random_book.cover.root:
print(f"Download the book cover: {file.url}")This section demonstrates how to modify existing entries in Baserow. The approach differs between basic types and advanced types like files or select fields. Let's start by looking at the basic types.
There are three distinct methods to update entries. Table.update_fields_by_id() is used when the ID of the row to be modified is known, but the full dataset is not yet available on the client. The fields to be updated are specified as keyword arguments.
Similarly, Table.update_fields uses keyword arguments but requires the complete instance to be available. Multiple fields can be updated by their names and corresponding values.
Lastly, you can modify the local instance and then use Table.update() to apply all changes to Baserow. This method is the only approach for advanced types. You have to remember to call the update() method. Otherwise your changes will be lost. The program will warn you, when a instance with unwritten changes was deleted by the garbage collector.
# Update by ID
await Book.update_fields_by_id(book_id, title="Gardening Basics")
print(f"Set title of book id={book_id} to 'Gardening Basics'")
# Update model instance: Manipulate by field name
book = await Book.by_id(book_id)
await book.update_fields(description="A beginner's guide to gardening.")
print(f"Set description of book id={book_id} to 'A beginner's guide to gardening.'") # noqa
# Update model instance: Update instance to Baserow
book.published_date = datetime(2021, 3, 5)
book.reading_duration = timedelta(hours=6)
book.rating = 5
await book.update()Manipulate single and multiple select fields. Again: Don't forget to update.
# Set a new value for the single select field.
book.genre.set(Genre.EDUCATION)
# Remove all current keywords.
book.keywords.clear()
# Add some new keywords.
book.keywords.append(
Keyword.EDUCATION, Keyword.BEGINNER, Keyword.MYSTERY, Keyword.FICTION,
)
# Remove keyword(s).
book.keywords.remove(Keyword.MYSTERY, Keyword.FICTION)
await book.update()Modify link fields. Works almost As multiple select fields.
# Remove all current linked entries.
book.author.clear()
# Append author entry by row id and instance.
author = await Author.by_id(author_ids[0])
book.author.append(author_ids[1], author)
await book.update()Modify file fields. As always: Don't forget to update in the end.
# Remove current file. And add two new ones.
book.cover.clear()
await book.cover.append_file(example_image())
await book.cover.append_file_from_url("https://picsum.photos/180/320")
await book.update()There are two ways: Delete by row_id using Table.delete_by_id() or call Table.delete() on a instance.
# Delete by id
await Author.delete_by_id(ROW_ID)
# Delete by instance
author = await Author.by_id(ROW_ID)
await author.delete()The Client manages the actual HTTP calls to the Baserow REST API. It can be used directly or, ideally, through the model abstraction provided by Pydantic, which is the primary purpose of this package.
Access to the Baserow API requires authentication, and there are two methods available for this:
- Database Tokens: These tokens are designed for delivering data to frontends and, as such, can only perform CRUD (Create, Read, Update, Delete) operations on a database. New tokens can be created in the User Settings, where their permissions can also be configured. For instance, it is possible to create a token that only allows reading. These tokens have unlimited validity.
- JWT Tokens: All other functionalities require a JWT token, which can be obtained by providing login credentials (email address and password) to the Baserow API. These tokens have a limited lifespan of 10 minutes and will be refreshed if needed.
The client in this package can handle both types of tokens. During initialization, you can provide either a Database Token or the email address and password of a user account. For most use cases, the Database Token is sufficient and recommended.
The following example demonstrates how to instantiate the client using either of the available authentication methods. Please note that only one of these methods should be used at a time.
from baserow import Client
# With a database token.
client = Client("baserow.example.com", token="<API-TOKEN>")
# With user email and password.
client = Client("baserow.example.com", email="[email protected]", password="<PASSWORD>")
# Usage example.
table_id = 23
total_count = await client.table_row_count(table_id)If a specific client is required for a table, it can be added as follows.
client = Client("baserow.example.com", token="<API-TOKEN>")
class Author(Table):
table_id = 23
table_name = "Author"
model_config = ConfigDict(populate_by_name=True)
client = clientIn many applications, maintaining a consistent connection to a single Baserow instance throughout the runtime is crucial. To facilitate this, the package provides a Global Client, which acts as a singleton. This means the client needs to be configured just once using GlobalClient.configure(). After this initial setup, the Global Client can be universally accessed and used throughout the program.
When utilizing the ORM functionality of the table models, all methods within the table models inherently use this Global Client. Please note that the Global Client can only be configured once. Attempting to call the GlobalClient.configure() method more than once will result in an exception.
from baserow import GlobalClient
# Either configure the global client with a database token...
GlobalClient.configure("baserow.example.com", token="<API-TOKEN>")
# ...or with the login credentials (email and password).
GlobalClient.configure(
"baserow.example.com",
email="[email protected]",
password="<PASSWORD>",
)
# Use the global client just like you would use any other client instance.
persons = await GlobalClient().get_row(23, 42, True, Person)This setup ensures that your application maintains optimal performance by reusing the same client instance, minimizing the overhead associated with establishing multiple connections or instances.
Even though Baserowdantic focuses on interacting with Pydantic using Pydantic data models, the Client class used can also be directly employed. The Client class provides CRUD (create, read, update, delete) operations on a Baserow table. It is entirely asynchronous.
This method returns the number of rows or records in a Baserow table. Filters can be optionally passed as parameters.
from baserow import Client, AndFilter
client = Client("baserow.example.com", token="<API-TOKEN>")
table_id = 23
total_count = await client.table_row_count(table_id)
dave_count = await client.table_row_count(
table_id,
filter=AndFilter().contains("Name", "Dave"),
)
print(f"Total persons: {total_count}, persons called Dave: {dave_count}")
client.close()This function retrieves the fields (also known as rows) present in a specified table along with their configurations. The return value contains the information in the form of the FieldConfig model.
table_id = 23
print(await client.list_fields(table_id))The method reads the entries or records of a table in Baserow. It is possible to filter, sort, select one of the pages (Baserow API uses paging), and determine the number (size) of returned records (between 1 to 200). If it is necessary to retrieve all entries of a table, the method Client().list_all_table_rows exists for this purpose. This method should be used with caution, as many API calls to Baserow may be triggered depending on the size of the table.
Setting the result_type parameter to a pydantic model the result will be deserialized into the given model. Otherwise a dict will be returned.
table_id = 23
# Get the first 20 person of the table as a dict.
first_20_person = await client.list_table_rows(table_id, True, size=20)
# Get all person where the field name contains the substring »Dave« or »Ann«.
ann_dave_person = await client.list_table_rows(
table_id,
True,
filter=OrFilter().contains("Name", "Dave").contains("Name", "Ann"),
)
# Get all entries of the table. This can take a long time.
all_person = await client.list_all_table_rows(table_id, True, result_type=Person)This methods facilitates the creation of one or multiple records in a specific table, identified by its ID. Data for the records can be provided either as a dictionary or as an instance of a BaseModel. This flexibility allows users to choose the format that best suits their needs, whether it's a simple dictionary for less complex data or a BaseModel for more structured and type-safe data handling.
To create multiple records at once, you can use the Client().create_rows() method. This uses Baserow's batch functionality and thus minimizes the number of API calls required to one.
table_id = 23
# Create on new row.
client.create_row(table_id, {"Name": "Ann"}, True)
# Create multiple rows in one go.
client.create_rows(
table_id,
[
Person(name="Tom", age=23),
Person(name="Anna", age=42),
],
True,
)This method updates a specific row (entry) within a table. Both the table and the row are identified by their unique IDs. The data for the update can be provided either as a Pydantic model or as a dictionary.
- Using a Dictionary: More commonly, a dictionary is used for targeted updates, allowing specific fields within the row to be modified. This method makes more sense in most cases where only certain fields need adjustment, rather than a full update.
- Using a Pydantic Model: When a Pydantic model is used, all values present within the model are applied to the row. This approach is comprehensive, as it updates all fields represented in the model.
table_id = 23
row_id = 42
# Change the name and age of the Row with ID 42 within the table with the ID 23.
rsl = await client.update_row(
table_id,
row_id,
{"Name": "Thomas Niederaichbach", "Age": 29},
True,
)
print(rsl)The method returns the complete updated row.
In the File field type, files can be stored. For this purpose, the file must first be uploaded to Baserow's storage. This can be done either with a local file read using open(...) or with a file accessible via a public URL. The method returns a field.File instance with all information about the uploaded file.
After the file is uploaded, it needs to be linked to the field in the table row. For this, either the complete field.File instance can be passed to the File field or simply an object containing the name (field.File.name, the name is unique in any case). The updated row data is then updated to Baserow.
# Upload a local file.
with open("my-image.png", "rb") as file:
local_rsl = await client.upload_file(file)
# Upload a file accessible via a public URL.
url_rsl = await client.upload_file_via_url("https://picsum.photos/500")
# Set image by passing the entire response object. Caution: this will overwrite
# all previously saved files in the field.
table_id = 23
row_id = 42
file_field_name = "Contract"
await client.update_row(
table_id,
row_id,
{file_field_name: FileField([local_rsl]).model_dump(mode="json")},
True
)
# Set image by passing just the name of the new file. Caution: this will overwrite
# all previously saved files in the field.
await GlobalClient().update_row(
table_id,
row_id,
{file_field_name: [{"name": url_rsl.name}]},
True
)This method is used to delete one or more rows within a specified table. Both the table and the row are identified by their unique IDs.
table_id = 23
# Delete the row with ID 23
await client.delete_row(table_id, 23)
# Delete rows with ID 29 and 31 in one go.
await client.delete_row(table_id, [29, 31])On success the method returns None otherwise an exception will be thrown.
This method facilitates the creation of a new table within a specified database, identified by the unique ID of the database. A human-readable name for the table must be provided. It's also possible to integrate the table creation action into the undo tree of a client session or an action group. This can be accomplished using optional parameters provided in the method.
For additional details on these optional parameters and other functionalities, please refer to the code documentation of this package and the Baserow documentation.
database_id = 19
# Create a new table with the name »Cars« in the database with the ID 19.
await client.create_database_table(database_id, "Cars")This method retrieves a list of all tables within a specified database. The result includes essential information about each table, such as its ID and name.
database_id = 19
# List all tables within the database with the ID 19.
rsl = await client.list_database_table(database_id)
print(rsl)The Client class supports the creation, updating, and deletion of table fields (referred to as 'Rows').
For both creating and updating a field, the appropriate instance of FieldConfigType is provided. For each field type in Baserow, there is a corresponding field config class that supports the specific settings of the field.
To modify selected properties of an existing field, the configuration of the field can be retrieved using Client().list_fields(), the resulting object can then be modified and subsequently updated.
table_id = 23
# Adds a new text field (»row«) to the person table with the name pronoun.
client.create_database_field(
table_id,
TextFieldConfig(name"Pronoun")
)Note: This former part of the README was removed in favor of a more in dept introduction example, the comprehensive ORM example and more examples in the API documentation.