Django projects often call for a robust, powerful setup to ensure a smooth development and deployment process. Cookiecutter Django is a popular framework that aims to offer Django users a comprehensive, out-of-the-box setup, including configurations for databases, templates, and much more. Cookiecutter Django exclusively supports PostgreSQL, reflecting its intention for production-level applications where PostgreSQL's advanced features can be a real asset. However, there might be scenarios where a developer wants to use SQLite instead, perhaps for small-scale applications, quick prototypes, or simply due to personal preference or familiarity with SQLite.

Unfortunately, those choosing SQLite may run into issues right from the start, particularly with initial migrations. An example of such an issue is the following error encountered when applying migrations:

django.db.utils.OperationalError: near "AUTO_INCREMENT": syntax error

This blog post will guide you through resolving this specific issue and getting your Cookiecutter Django project up and running with SQLite.

The Root of the Problem

The problem stems from differences in how PostgreSQL and SQLite handle auto-incrementing IDs and their syntax. Cookiecutter Django's setup does not account for these differences, leading to syntax errors when migrations are run on SQLite databases.

A Tailored Solution

To overcome this issue, we need to tweak the migration files, specifically ensuring that our migrations are compatible with SQLite's syntax and behaviors. Here's a solution that addresses the “AUTO_INCREMENT” syntax error by modifying the migration file responsible for the error, which in this case is:

my-awesome-project/contrib/sites/migrations/0003_set_site_domain_and_name.py

Add the following function to the migration file:

def _update_or_create_site_with_sequence(site_model, connection, domain, name):
    """Update or create the site with default ID and keep the DB sequence in sync."""
    site, created = site_model.objects.update_or_create(
        id=settings.SITE_ID,
        defaults={
            "domain": domain,
            "name": name,
        },
    )
    if created:
        # We provided the ID explicitly when creating the Site entry, therefore the DB
        # sequence to auto-generate IDs wasn't used and is now out of sync. If we
        # don't do anything, we'll get a unique constraint violation the next time a
        # site is created.
        # To avoid this, we need to manually update DB sequence and make sure it's
        # greater than the maximum value.
        max_id = site_model.objects.order_by('-id').first().id

        with connection.cursor() as cursor:
            if connection.vendor == "postgresql":
                cursor.execute("SELECT last_value from django_site_id_seq")
                (current_id,) = cursor.fetchone()
                if current_id <= max_id:
                    cursor.execute(
                        "ALTER SEQUENCE django_site_id_seq RESTART WITH %s",
                        [max_id + 1],
                    )
            elif connection.vendor == "sqlite":
                cursor.execute("SELECT MAX(id) FROM django_site")
                current_id = cursor.fetchone()[0] or 0
                if current_id <= max_id:
                    try:
                        cursor.execute("INSERT INTO django_site (id, domain, name) VALUES (?, 'temp', 'temp')",
                                       (max_id + 1,))
                    except Exception:
                        pass
                    finally:
                        cursor.execute("DELETE FROM django_site WHERE domain='temp' AND name='temp'")

What this solution does is adapt the process of updating or creating a site entry to manage SQLite's handling of ID sequences. For SQLite, instead of modifying a sequence directly (as sequences as such don't exist in SQLite in the way they do in PostgreSQL), it inserts and then deletes a dummy record to ensure that the SQLite ID generation mechanism remains consistent and does not try to reuse an ID.

This workaround preserves the flexibility of Cookiecutter Django by allowing developers to opt for SQLite while still leveraging the structure and configuration that Cookiecutter Django provides. Though not officially supported, this solution makes it possible to bridge the gap between the default PostgreSQL setup and the simplicity and ease of use some developers find with SQLite.

Final Thoughts

While PostgreSQL is recommended for production due to its scalability and robust feature set, SQLite can be entirely sufficient for smaller applications, prototypes, or when the project's environment constraints dictate its use. By making slight adjustments, such as those detailed above, developers can enjoy the best of both worlds: the streamlined, comprehensive configuration of Cookiecutter Django and the simplicity and ease of setup afforded by SQLite.