How Baserow lets users generate Django models on the fly

At Baserow, we use the Django ORM to create dynamic models for efficient data manipulation. Our goal is to make it easy for anyone, even those without technical skills, to build their own database without writing code. We have developed a hybrid relational database with an intuitive interface. To ensure security, we rely heavily on the Django ORM. In this blog post, we will explore how we have pushed the limits of the ORM while building important backend features for Baserow.

Django models

Models are one of Django’s most powerful features, allowing you to represent your database schema in Python and also create and migrate your schema from the models themselves. For example, if you want to store projects in a SQL table, your model would look like this:

class Project(models.Model):
    name = models.CharField(max_length=255)

To create the table in the database, you first need to generate and execute the migrations using the makemigrations and migrate management commands. This will detect the changes in your models and generate a Python file containing the changes, which are then executed with the second command.

Baserow tables

We use the same approach for our tables and schema changes. However, with Baserow, users can create their own relational database without needing to know Python, Django, or PostgreSQL. When we mention a PostgreSQL table, we mean a table created in the PostgreSQL database. A Baserow table is created by a user in Baserow through the web interface or the REST API. Every Baserow table is supported by a real PostgreSQL table in the database.

Dynamically generating models

How do we build a Django app that allows non-technical users to create and migrate their own models? The first step is realizing that Django models can be generated dynamically using the Python type function. Additionally, we can use the schema editor to make schema changes similar to migrations. The code for generating the project model would look like this:

from django.db import models

Project = type(
    "Project",
    (models.Model,),
    {
        "name": models.CharField(max_length=255),
        "Meta": type(
            "Meta",
            (),
            {"app_label": "test"}
        ),
        "__module__": "database.models"
    }
)

Sometimes, you may encounter a situation where you need to generate the same model or a model with the same name for a second time. Unfortunately, Django throws an error stating that the model is already registered.

At Baserow, we tackle this issue by regenerating the model whenever it is needed. This typically happens when a row is updated or requested. We adopt this approach because the table schema might have changed, and considering that a single Baserow instance could potentially have millions of tables, registering all of them could quickly deplete our memory resources.

To prevent registration conflicts, we extend the AppConfig and distinguish generated models by adding a _generated_table_model property to it.

# apps.py
class DatabaseConfig(AppConfig):
    name = "database"

    def ready(self):
        original_register_model = self.apps.register_model

        def register_model(app_label, model):
            if not hasattr(model, "_generated_table_model"):
                original_register_model(app_label, model)
            else:
                self.apps.do_pending_operations(model)
                self.apps.clear_cache()

        self.apps.register_model = register_model

Making schema changes

After generating the model, you cannot create new records because the table has not yet been created in the PostgreSQL database. Normally, the schema change is done by executing the migration using the migrate management command. When you apply a migration file in Django, it uses the schema_editor under the hood to make the change. The schema editor can also be used with generated models. If, for example, you want to create the project table and add a new record, you could do this:

from django.db import connection, models

# The model as described in the previous example.
Project = type(...)

with connection.schema_editor() as schema_editor:
    schema_editor.create_model(Project)

Project.objects.create(name="Baserow")

The schema editor has everything you need to make all the changes you need, from add_field to delete_model.

How Baserow generates models

Baserow works similarly to the approach mentioned above. The difference is that we generate models dynamically from metadata tables that define the structure of user tables. We have two important metadata tables: Table and Field. The Table table has a row for each user table, while the Field table has a row for each field created by the user in a table.

Table

Field

If we want to generate the Project model, we have to query the table and field metadata tables first and then use that data.

class Table(models.Model):
    name = models.CharField(max_length=255)

class Field(models.Model):
    table = models.ForeignKey(Table, on_delete=models.CASCADE)
    name = models.CharField(max_length=255)
    type = models.CharField(max_length=32)

table = Table.objects.get(pk=1)
fields = Fields.objects.filter(table=table)
attrs = {
    "Meta": type(
        "Meta",
        (),
        {"app_label": "test"}
    ),
    "__module__": "database.models"
}

for field in fields:
    attrs[field.name] = models.CharField(max_length=255)

GeneratedModel = type(
    f"Table{table.id}",
    (models.Model,),
    attrs
)

# Assuming the PostgreSQL table has already been created using the schema editor.
GeneratedModel.objects.all()

In Baserow, we offer a wide range of features, including support for various field types. To streamline the process, we have consolidated all the model generation code into a single method called get_model. This method allows you to create a new row in a Baserow table with just four lines of code.

from baserow.contrib.database.table.models import Table

table = Table.objects.get(pk=YOUR_TABLE_ID)
model = table.get_model()
model.objects.create()

If you create a Baserow plugin, you can easily fetch data from your Baserow table this way.

By using the Django ORM to manage our Baserow tables, we can avoid security mistakes like SQL injection, write clean and easy-to-understand Django ORM code when working on user data and finally provide a great API for Baserow plug-ins.

Caching models

Some Baserow tables have over 100 fields and the model needs to be generated often. Fetching and generating the model for all 100 fields can be time-consuming, especially if it has to be done for every request. To enhance performance, we cache the models by storing the field attributes in a Redis cache. Here’s a simplified representation:

from django.core import cache

fields = Fields.objects.filter(table=table)
for field in fields:
    attrs[field.name] = models.CharField(max_length=255)

cache.set(f"table_model_cache_{table.id}", attrs, timeout=None)

attrs = cache.get(f"table_model_cache_{table.id}")
GeneratedModel = type(
    f"Table{table.id}",
    (models.Model,),
    attrs
)

Sponsoring Django to support open-source projects

Thanks to Django, we at Baserow save tons of time: we build features faster, we write cleaner code, and we avoid many common security mistakes, to name just a few. Now it’s our turn to give back. We decided to sponsor the company and now Baserow is an official Corporate Member of the Django Foundation! This is the least we can do to support this important open source project.

Dive deeper into how we use Django at Baserow

All the use cases described above are very simplified. If you want to learn more about how we generate models, apply schema migrations and cache models, click here.

And the last thing, Baserow is also an open-source project, so everyone interested is welcome to contribute—whether through code, documentation, or bug reports!

Visit the Baserow website: https://baserow.io/

Check out the Baserow repo: https://gitlab.com/baserow/baserow