# Different models for different databases

**URL:** <https://forum.djangoproject.com/t/different-models-for-different-databases/8471>\
**Category:** ORM\
**Created:** [June 22, 2021, 4:44pm UTC](https://forum.djangoproject.com/t/different-models-for-different-databases/8471 "2021-06-22T16:44:32Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![ACMCMC](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/acmcmc/32/4929_2.png) [@ACMCMC](https://forum.djangoproject.com/u/ACMCMC)\
**Post date:** [June 22, 2021, 4:44pm UTC](https://forum.djangoproject.com/t/different-models-for-different-databases/8471/1 "2021-06-22T16:44:32Z")

</div>

Hi all! I’m currently working on a GSoC project to improve the search backend provided in the Wagtail CMS, which is based on Django.

The issue I’ve run into is that I need to create different models for each specific database system that the user may have. That’s because different database systems implement full-text search capabilities in varying approaches.

For example, in PostgreSQL you may want to have a table with the following definition:

```sql
CREATE TABLE fts(original_text text, stemmed_text tsvector);

```

On top of that, you could use a `GIN` index on the `stemmed_text` column, to improve full-text search performance.

However, this could be implemented in SQLite as:

```sql
CREATE VIRTUAL TABLE fts USING FTS5(original_text);

```

You would not use a `GIN` index here. They’re not even supported by SQLite.

As you see, the syntax is **fundamentally different**. On PostgreSQL, the `stemmed_text` column needs to be created, while there’s no need to have additional columns on SQLite. Therefore, it looks like **the models themselves are different**.

This seems like a reason to use _conditional declaration of the models_, meaning that _only one of the models would get defined_, depending on which database system we’re using.

```auto
if connection.vendor == 'postgresql':
    class PostgreSQLModel(models.Model):
        original_text = TextField()
        stemmed_text = SearchVectorField()
elif connection.vendor == 'sqlite':
    class SQLiteModel(models.Model):
        original_text = TextField()

```

This means that, when the user is using a PostgreSQL database, the `PostgreSQLModel` will get picked up by Django, but not the `SQLiteModel`; and vice versa.

Is this a reasonable approach? Can you think of a better, more orthodox implementation?

---

<div class="post-metadata">

**Author:** ![massover](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/massover/32/1792_2.png) [@massover](https://forum.djangoproject.com/u/massover)\
**Post date:** [June 22, 2021, 7:41pm UTC](https://forum.djangoproject.com/t/different-models-for-different-databases/8471/2 "2021-06-22T19:41:09Z")

</div>

I’m not sure if it helps, or if your question is past this, but have you seen the documentation on [database backends](https://docs.djangoproject.com/en/3.2/ref/databases/)? And for migration specific logic, the [SchemaEditor](https://docs.djangoproject.com/en/3.2/ref/schema-editor/)? In the simplest case, you can have 1 Field type that will generate the appropriate sql depending on the backend configuration.

---

<div class="post-metadata">

**Author:** ![felixxm](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/felixxm/32/2479_2.png) [@felixxm](https://forum.djangoproject.com/u/felixxm)\
**Post date:** [June 23, 2021, 6:56am UTC](https://forum.djangoproject.com/t/different-models-for-different-databases/8471/3 "2021-06-23T06:56:44Z")

</div>

> [@ACMCMC](#):
>
> Is this a reasonable approach? Can you think of a better, more orthodox implementation?

I would try to keep the same model and conditionally define the fields e.g.

```auto
if connection.vendor == 'postgresql':
    from django.contrib.postgres.search import SearchVectorField
else:
    SearchVectorField = object

# Create your models here.
class MyModel(models.Model):
    original_text = models.TextField()
    stemmed_text = SearchVectorField()

```

I don’t know how exactly you would like to handle `VIRTUAL` tables on SQLite, so my advice may not be the best.

---

<div class="post-metadata">

**Author:** ![ACMCMC](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/acmcmc/32/4929_2.png) [@ACMCMC](https://forum.djangoproject.com/u/ACMCMC)\
**Post date:** [June 26, 2021, 2:21pm UTC](https://forum.djangoproject.com/t/different-models-for-different-databases/8471/4 "2021-06-26T14:21:02Z")

</div>

Thanks for your suggestions! I had thought about defining custom `Field` types that generate the appropriate SQL types for each backend, but I had originally discarded that possibility because of the fact that some database systems use a completely different syntax, like the one I showed above (_virtual tables_).

However, I didn’t know about the possibility of customising the `SchemaEditor`. I’ve been looking into that recently, and it looks like a possible way to tackle this issue. It would enable the customization of the creation of the table, so that Django runs `CREATE VIRTUAL TABLE` instead of the usual `CREATE TABLE`. I will test it to see if it is actually a feasible approach, but it seems like a really good idea in forethought. Thank you!

---

<div class="post-metadata">

**Author:** ![ACMCMC](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/acmcmc/32/4929_2.png) [@ACMCMC](https://forum.djangoproject.com/u/ACMCMC)\
**Post date:** [June 26, 2021, 2:26pm UTC](https://forum.djangoproject.com/t/different-models-for-different-databases/8471/5 "2021-06-26T14:26:13Z")

</div>

Do you think that it is a better approach to define the fields conditionally, instead of creating a custom `Field` type that exports different types for each database (`tsvector`s for PostgreSQL, `None` for SQLite, …)?

---

<div class="post-metadata">

**Author:** ![massover](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/massover/32/1792_2.png) [@massover](https://forum.djangoproject.com/u/massover)\
**Post date:** [June 27, 2021, 2:41pm UTC](https://forum.djangoproject.com/t/different-models-for-different-databases/8471/6 "2021-06-27T14:41:36Z")

</div>

For inspiration it might also help to look at how [JSONField](https://github.com/django/django/blob/main/django/db/models/fields/json.py) was implemented. While not exactly, it seems like has to deal with “each vendor requires its own implementation” sort of thing.

Also, it might help to look at the existing database backend [features](https://github.com/django/django/blob/main/django/db/models/fields/json.py) and see how they are handled across vendors.

I think what you’re trying to do is challenging because there is no vendor ORM agnostic way to handle search yet. there is only support for full text search for postgresql in the core.

---

<div class="post-metadata">

**Author:** ![felixxm](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/felixxm/32/2479_2.png) [@felixxm](https://forum.djangoproject.com/u/felixxm)\
**Post date:** [June 28, 2021, 7:29am UTC](https://forum.djangoproject.com/t/different-models-for-different-databases/8471/7 "2021-06-28T07:29:19Z")

</div>

> [@ACMCMC](#):
>
> Do you think that it is a better approach to define the fields conditionally, instead of creating a custom `Field` type that exports different types for each database ( `tsvector` s for PostgreSQL, `None` for SQLite, …)?

As far as I’m aware `None` data type will not work. You could use `SearchVectorField = models.Field` on `SQLite` which will be ignored by migrations, however all other attempts to use `MyModel` via the ORM will crash with _“missing column”_ errors. That’s why I proposed to use `object`.

---

<div class="post-metadata">

**Author:** ![ACMCMC](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/acmcmc/32/4929_2.png) [@ACMCMC](https://forum.djangoproject.com/u/ACMCMC)\
**Post date:** [June 28, 2021, 8:12am UTC](https://forum.djangoproject.com/t/different-models-for-different-databases/8471/8 "2021-06-28T08:12:30Z")

</div>

What I’m referring to is implementing `SearchVectorField` like so:

```python3
class SearchVectorField(models.Field):
    def db_type(self, connection):
        if connection.vendor == 'postgresql':
            return 'tsvector'
        else:
            return None

```

Which I believe should work, according to [the Django docs on writing custom `Field`s](https://docs.djangoproject.com/en/3.2/howto/custom-model-fields/#custom-database-types):

> if your column requires truly complex SQL setup, return `None` from [`db_type()`](https://docs.djangoproject.com/en/3.2/ref/models/fields/#django.db.models.Field.db_type). This will cause Django’s SQL creation code to skip over this field. You are then responsible for creating the column in the right table in some other way, but this gives you a way to tell Django to get out of the way

---

<div class="post-metadata">

**Author:** ![felixxm](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/felixxm/32/2479_2.png) [@felixxm](https://forum.djangoproject.com/u/felixxm)\
**Post date:** [June 28, 2021, 8:29am UTC](https://forum.djangoproject.com/t/different-models-for-different-databases/8471/9 "2021-06-28T08:29:18Z")

</div>

Yes, but you don’t really want to add `stemmed_text` database column on SQLite you want to use a proxy `VIRTUAL TABLE`. According to docs _“You are then responsible for creating the column in the right table in some other way”_, that’s why you will not be able to use a model with such column via ORM.