djangosqlite

How to use UUIDField for SQLite?


How can I generate a UUIDField that works for SQLite?

I want to use SQLite instead of Postgres for my tests so they run faster.

# settings.py
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        # ...
    }
}

# Tests use sqlite instead of postgres
import sys

if (
    "test" in sys.argv or "test_coverage" in sys.argv
):  # Covers regular testing and django-coverage
    DATABASES["default"]["ENGINE"] = "django.db.backends.sqlite3"

However, I don't seem to be able to create a UUID that fits Django's UUIDField for SQLite:

A field for storing universally unique identifiers. Uses Python’s UUID class. When used on PostgreSQL, this stores in a uuid datatype, otherwise in a char(32).

The following doesn't work even though the uuid value is 32 chars:

# models.py
class Item(models.Model):
    uuid = models.UUIDField()

# tests.py
uuid = str(uuid.uuid4()).replace("-", "")

Item.objects.create(uuid=uuid)

I get this error: django.db.utils.InterfaceError: Error binding parameter 4 - probably unsupported type.

Edit:

Here is the full error:

----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/sqlite3/base.py", line 423, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.InterfaceError: Error binding parameter 4 - probably unsupported type.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/app/library/tests/tests_views_auth.py", line 423, in test_post_list_items_works
    c1 = Chapter.objects.create(title="B1C1", body="B1C1", parent=b1)
  File "/24reads/library/models.py", line 117, in create
    return super().create(**kwargs)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/query.py", line 453, in create
    obj.save(force_insert=True, using=self.db)
  File "/usr/local/lib/python3.9/site-packages/django_lifecycle/mixins.py", line 134, in save
    save(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/mptt/models.py", line 1091, in save
    self.insert_at(
  File "/usr/local/lib/python3.9/site-packages/mptt/models.py", line 771, in insert_at
    self._tree_manager.insert_node(
  File "/usr/local/lib/python3.9/site-packages/mptt/managers.py", line 42, in wrapped
    return getattr(self._base_manager, method.__name__)(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/mptt/managers.py", line 43, in wrapped
    return method(self, *args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/mptt/managers.py", line 537, in insert_node
    self._create_space(2, space_target, tree_id)
  File "/usr/local/lib/python3.9/site-packages/mptt/managers.py", line 816, in _create_space
    self._manage_space(size, target, tree_id)
  File "/usr/local/lib/python3.9/site-packages/mptt/managers.py", line 1052, in _manage_space
    cursor.execute(
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/sqlite3/base.py", line 423, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.InterfaceError: Error binding parameter 4 - probably unsupported type.

----------------------------------------------------------------------

My specific use case sees me override the base MPTTModel and TreeManager to use a UUID instead of a continuguous integer to avoid concurrency issues as outlined here: https://github.com/django-mptt/django-mptt/issues/555#issuecomment-331315715

class AsyncSafeTreeManager(TreeManager):
    def _get_next_tree_id(self):
        if (
            "test" in sys.argv or "test_coverage" in sys.argv
        ):  # Covers regular testing and django-coverage
            return uuid.uuid4()
        return generate_ulid_as_uuid()


class AsyncSafeMPTTModel(MPTTModel):

    objects = AsyncSafeTreeManager()
    tree_id = models.UUIDField()

    class Meta:
        abstract = True

This works fine when I'm using PostGRE but does not work with SQLite.


Solution

  • Regardless what the underlying type is, Django will transform a UUID to the correct format, and insert it in the database. For SQLite that thus means that the UUIDField will tranform it to a string.

    You thus can create an item with:

    import uuid
    
    Item.objects.create(
        uuid=uuid.uuid4()
    )