Web Frameworks

This page serves a reference guide for common tasks within web frameworks. Squawk doesn't have special support for Django or any other web ORM. This page is merely a guide.

Django ORM

Django can auto generate schema changes, but many times the generated SQL won't pass Squawk's rules. These custom database backends for zero downtime migrations might work for your use case:

making a field non-nullable

In the Django ORM, making a nullable field non-nullable requires care to make the change safe.

This example shows how to make a nullable boolean field a non-nullable field.

# before
class Post(Model):
    published = models.BooleanField(null=True)

# after
class Post(Model):
    published = models.BooleanField(null=False, default=False)

migration steps

We cannot use the auto generated SQL from Django's migration system. We must use django.db.migrations.operations.SeparateDatabaseAndState to make changes to our Django models while using custom SQL.

Update the model with a default so newly created objects in Django get assigned a default value. This change must be deployed before making the SQL migrations.
```
class Post(Model):
    published = models.BooleanField(default=False)
```

Create trigger to set default value false for inserts published is null.

CREATE FUNCTION null_published_trigger()
RETURNS trigger AS '
BEGIN
IF NEW.published IS NULL THEN
    NEW.published := false;
END IF;
RETURN NEW;
END' LANGUAGE 'plpgsql';

CREATE TRIGGER published_default_trigger
BEFORE INSERT ON post
FOR EACH ROW
EXECUTE PROCEDURE null_published_trigger();

Set a default value for existing objects.

UPDATE "post" SET "published" = false WHERE "published" IS NULL;

Create a not null constraint.

ALTER TABLE "post" ADD CONSTRAINT published_not_null
    CHECK ("published" IS NOT NULL) NOT VALID;

Validate constraint.

ALTER TABLE "post" VALIDATE CONSTRAINT published_not_null;

Remove trigger.

DROP TRIGGER IF EXISTS published_default_trigger on post;
DROP FUNCTION IF EXISTS null_published_trigger;

Alembic and SQLAlchemy

usage

alembic upgrade head --sql | squawk

or you can choose revisions

alembic upgrade first_revision:last_revision --sql | squawk

settings

Use transaction_per_migration = True in configure

# env.py
...
context.configure(
   ...
   transaction_per_migration=True
   ...
)
...

Set lock_timeout and statement_timeout

# env.py
...
def run_migrations_online() -> None:
    """Run migrations in 'online' mode.
    In this scenario we need to create an Engine
    and associate a connection with the context.
    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
        connect_args={'options': '-c lock_timeout=4000 -c statement_timeout=5000'}
    )
...

Django ORM​

making a field non-nullable​

migration steps​

Alembic and SQLAlchemy​

usage​

settings​