A Django-ORM-Raised Engineer Relearns Raw SQL [Model Changes]

Introduction

This article is the second in my series where I, raised on the Django ORM, relearn raw SQL: Model Changes. The series has four parts.

• Model Definition: what tables a models.py definition becomes
• Model Changes (this article): what ALTER TABLE runs behind makemigrations and migrate
• Queries: what queries run behind filter and update
• Performance Tuning: the N+1 problem, select_related / prefetch_related, and the internals of aggregation

In the previous Model Definition, I checked what CREATE TABLE the model definitions I wrote in models.py actually become, reading the sqlmigrate output.

I can now create tables, but in real development a model isn’t created once and done. You want to add a field, rename one, or rethink types and constraints. What SQL runs behind this work, which I normally just handle by running makemigrations and migrate? This time I’ll check that. The star will be ALTER TABLE, the DDL that modifies an already-created table afterward. Last time it made a brief appearance when adding a ForeignKey constraint, but this time I’ll deal with it as the main subject.

The approach is the same as last time. I’ll actually apply changes like adding, renaming, and changing the type of fields to the sample models (User, Profile, Post, Tag, Comment) built in the Model Definition article, reading the generated SQL with sqlmigrate. For the test environment and the full sample models, see the Model Definition article.

What I want to learn isn’t Django’s mechanism itself, but the SQL underneath it. Much of what comes up this time is knowledge common to the migration mechanism, and it works the same way when using Prisma or Rails. Being able to read DDL like ALTER TABLE myself is the goal this time.

Since this is based on my own research, it may contain errors. If you notice anything, I’d greatly appreciate a heads-up.

The models I’ll change this time

What I’ll modify in this article are two models, Post and Tag. Writing the changes I’ll add in this article as a diff onto the definitions as of the end of the Model Definition article looks like this. User, Profile, and Comment don’t appear this time, so I’ve omitted them.

class Tag(models.Model):
    name = models.CharField(
        max_length=50,  
        max_length=20,  
        unique=True,
    )


class Post(models.Model):
    class Status(models.TextChoices):
        DRAFT = "draft", "下書き"
        PUBLISHED = "published", "公開"
        ARCHIVED = "archived", "アーカイブ"

    author = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="posts",
    )

    title = models.CharField(
        max_length=200,
    )

    subtitle = models.CharField(  
        max_length=200,  
        blank=True,  
    )  

    slug = models.SlugField(
        max_length=200,
    )

    body = models.TextField()  
    content = models.TextField()  

    status = models.CharField(
        max_length=20,
        choices=Status.choices,
        default=Status.DRAFT,
    )

    view_count = models.PositiveIntegerField(
        default=0,
    )

    published_at = models.DateTimeField(
        null=True,  
        blank=True,
        db_index=True,  
    )

    created_at = models.DateTimeField(
        auto_now_add=True,
    )

    updated_at = models.DateTimeField(
        auto_now=True,
    )

    tags = models.ManyToManyField(
        Tag,
        related_name="posts",
        blank=True,
    )

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=["author", "slug"],
                name="unique_author_slug",
            )
        ]

In the article, rather than applying this diff all at once, I’ll add the changes one at a time in the following order and check what SQL each becomes.

1. Add a subtitle field to Post
2. Rename Post.body to Post.content
3. Shrink Tag.name’s max_length from 50 to 20
4. Add an index to Post.published_at and remove null=True

Adding a field is ALTER TABLE ADD COLUMN

The first change is the most common one, “adding a field.” I’ll give Post a subtitle.

    title = models.CharField(
        max_length=200,
    )

    subtitle = models.CharField(  
        max_length=200,  
        blank=True,  
    )  

Running makemigrations generated a migration file called 0002_post_subtitle.py. Before migrating, I check the SQL with sqlmigrate.

python manage.py sqlmigrate blog 0002

BEGIN;
--
-- Add field subtitle to post
--
ALTER TABLE "blog_post" ADD COLUMN "subtitle" varchar(200) DEFAULT '' NOT NULL;
ALTER TABLE "blog_post" ALTER COLUMN "subtitle" DROP DEFAULT;
COMMIT;

The star, ALTER TABLE, appeared. Last time it showed up for adding a ForeignKey constraint (ADD CONSTRAINT), but this time it’s ADD COLUMN — adding a column, as the name says. ALTER TABLE is DDL that “modifies an already-created table afterward,” and the subcommand that follows determines what it does. Let me line up the basic forms that appear in this article first¹.

ALTER TABLE table_name ADD COLUMN column_name type constraint;      -- add a column
ALTER TABLE table_name DROP COLUMN column_name;                     -- drop a column
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;          -- rename a column
ALTER TABLE table_name ALTER COLUMN column_name ...;                -- change a column's type or constraint

After ADD COLUMN comes "subtitle" varchar(200) ... NOT NULL, the same three-part set of “column name, type, constraint” as in the column definitions inside CREATE TABLE I learned last time. Since this change just adds one column, I expected only a single ALTER TABLE statement, but there are actually two. And the first one has DEFAULT '', which I didn’t write in models.py, and the second one goes out of its way to remove it. Add a default value, then immediately remove it. At a glance it looks pointless.

Looking into it, this is for existing rows. This table may already contain rows. If you add a NOT NULL — meaning “NULL forbidden” — column there, the existing rows’ subtitle needs some value. The DEFAULT clause resolves the contradiction of “adding a column that forbids the absence of a value to existing rows that have no value”; adding a DEFAULT to ADD COLUMN fills existing rows with that value. This is RDB-side logic, not Django’s, so you hit the same problem no matter which tool runs the ALTER TABLE.

So why does the second statement remove the DEFAULT? Last time’s discovery is the answer directly. Django’s design doesn’t place default’s value filling on the DB side but fills it on the Python side at INSERT time. In other words, the DEFAULT clause is used only as a temporary tool to fill existing rows, and once its job is done it’s removed immediately, so from then on new rows are handled by Django as usual.

By the way, this '' value is the value representing “empty” for a CharField. If you try to add a field without blank=True, makemigrations asks in an interactive prompt to “decide the value to put into existing rows.” The answer to that prompt goes into this DEFAULT clause — that’s the correspondence.

Renaming a field is RENAME COLUMN

The second change is renaming a field. I’d named Post’s body field body, but since it’s confusingly similar to Comment.body, I’ll change it to content.

    body = models.TextField()  
    content = models.TextField()  

When I ran makemigrations, this time it asked for confirmation before the file was generated.

The question is “was post.body renamed to post.content?” Answering y generates 0003_rename_body_post_content.py. Let me look at the SQL.

python manage.py sqlmigrate blog 0003

BEGIN;
--
-- Rename field body on post to content
--
ALTER TABLE "blog_post" RENAME COLUMN "body" TO "content";
COMMIT;

Just as the basic form I noted earlier: RENAME COLUMN old_name TO new_name. Only the definition of the column’s name changes; the data inside stays. A straightforward SQL that finishes in one statement.

What’s more interesting than the SQL is that makemigrations asked for confirmation. When adding a field it silently created the file, so why does it ask a human when renaming?

The reason is that it can’t be determined from the models.py diff alone. What makemigrations sees is only the result “the field body disappeared and the field content was added.” Whether this is a rename of one field or a deletion of the old field plus an addition of the new one is, in principle, indistinguishable from the diff. So Django has no choice but to ask a human.

What’s scary is when this is judged as delete + add. The SQL generated then would be this.

-- if judged as delete + add rather than rename
ALTER TABLE "blog_post" DROP COLUMN "body";
ALTER TABLE "blog_post" ADD COLUMN "content" text NOT NULL;

This SQL is grammatically correct and won’t error when executed. The table also looks as intended in the sense that there’s a content column. But the contents are entirely different. At the point of DROP COLUMN, all the articles’ body data is gone.

And data lost by DROP COLUMN can’t be recovered no matter how you write the SQL. There’s no way to know the lost values to write back. Inside a transaction you can undo with ROLLBACK, but once you pass COMMIT, the only option left is restoring from a backup. This is why misjudged renames are said to be especially dangerous among migration mistakes.

This isn’t a matter of Django being poorly made. Not being able to tell a rename from a diff is a limit common to any tool with migration functionality; only how each tool copes differs. Django asks interactively; Prisma generates it as delete + add, so if needed you fix the generated migration yourself; Rails doesn’t auto-detect at all and has you write rename_column by hand. Whatever ORM you use, it’s worth checking before execution “whether what I meant as a rename became delete + add.” The habit of reading the SQL with sqlmigrate before migrate pays off exactly here — because you can judge before execution: if it’s RENAME COLUMN you’re safe, and if you see DROP COLUMN, stop.

Changing types and constraints

Next are changes to types and constraints rather than the field itself. Here I’ll add two changes together: shrinking Tag.name’s max_length from 50 to 20, and db_index=True to add an index to Post.published_at.

class Tag(models.Model):
    name = models.CharField(
        max_length=50,  
        max_length=20,  
        unique=True,
    )

    published_at = models.DateTimeField(
        null=True,
        blank=True,
        db_index=True,  
    )

Running makemigrations in this state generated a single file (0004_alter_post_published_at_alter_tag_name.py). makemigrations bundles multiple unapplied changes into a single migration file. Let me look at the SQL.

python manage.py sqlmigrate blog 0004

BEGIN;
--
-- Alter field published_at on post
--
CREATE INDEX "blog_post_published_at_9524a659" ON "blog_post" ("published_at");
--
-- Alter field name on tag
--
ALTER TABLE "blog_tag" ALTER COLUMN "name" TYPE varchar(20);
COMMIT;

Both statements are appearing for the first time. I’ll leave the first one, CREATE INDEX, for the next section and read the second one first.

The fourth basic form, ALTER COLUMN. Following it with TYPE varchar(20) changes the column’s type. A max_length change is a field-attribute change from Django’s viewpoint, but from SQL’s viewpoint it was “a type change from varchar(50) to varchar(20).”

A type-shrinking change can fail

This SQL looks calm as a single statement, but its danger level differs from a change that increases max_length. A table that’s now varchar(20) can only hold values up to 20 characters, but the previous varchar(50) table may already contain values longer than 20 characters.

Let me actually try it. After inserting a tag longer than 20 characters in psql, I ran the same ALTER TABLE.

INSERT INTO blog_tag (name) VALUES ('database-performance-tuning');  -- 27 characters
ALTER TABLE blog_tag ALTER COLUMN name TYPE varchar(20);

ERROR:  value too long for type character varying(20)

It errored. If existing data doesn’t fit the new type, ALTER TABLE fails on the spot. It’s the same when run via migrate, and this error stops the entire migration. Since a migration is wrapped in BEGIN and COMMIT, even the CREATE INDEX that was in the same file rolls back, and it isn’t applied halfway. The transaction I learned last time is at work here.

This isn’t limited to type shrinking; it’s a common structure for all “changes that existing data could violate.” The NOT NULL constraint addition that comes up later is the same: it fails if even one row is NULL. As for order: fix the data first, then change the type or constraint. I’ll cover this in the next chapter.

One more thing worth knowing when changing types in PostgreSQL is the USING clause. Close types like varchar to varchar convert as-is, but for something like changing a varchar column to integer, the DB can’t automatically decide how to convert existing values. You explicitly specify the conversion method with a USING clause, like ALTER COLUMN column_name TYPE integer USING column_name::integer¹. Django’s makemigrations doesn’t handle this far, so for such type changes you end up editing the migration.

Adding an index is CREATE INDEX

Back to the first statement of 0004, which I put off.

CREATE INDEX "blog_post_published_at_9524a659" ON "blog_post" ("published_at");

The real form of db_index=True was CREATE INDEX. The syntax is CREATE INDEX index_name ON table_name (column_name), and the index name, like a ForeignKey’s constraint name, is auto-generated by Django in the form “table name_column name_hash.” The reason it’s an independent statement rather than ALTER TABLE is that an index isn’t part of the table definition but a separate object attached to the table.

An index is index data that arranges values to make searching by a specific column easier. Like the index of a book, when a query filtering by published_at comes in, instead of scanning all rows of the table in order, you can reach the target rows directly from the index. It’s an addition made in anticipation of queries like “list articles by most recent publish date.” In exchange, there’s an index-update cost on every write, so it’s not something to attach to everything. The contents and effects of indexes will be dealt with head-on in the Performance Tuning article, so here I’ll just note “db_index=True becomes CREATE INDEX” and move on.

By the way, removing it is DROP INDEX, also an independent statement. Just like the relationship between adding a column and DROP COLUMN, having a creating command paired with a destroying command is a symmetry that’s consistent across SQL.

Fix the data before changing the constraint: the UPDATE statement

Last is the homework from the previous chapter: “fix the data first, then change the type or constraint.” This chapter I won’t move my hands; I’ll just note it as knowledge.

Consider a case where I want to remove published_at’s null=True. A migration that adds a NOT NULL constraint fails if any rows with published_at NULL remain. So before changing the constraint, I need to fill the NULL rows with some value. This “filling” is data rewriting, and in SQL it’s the job of the UPDATE statement. The basic form is this².

UPDATE table_name SET column_name = value WHERE condition;

WHERE narrows the target rows, and SET rewrites the value. What I want to write this time is one statement that fills a NULL published_at with the created timestamp for now.

UPDATE blog_post SET published_at = created_at WHERE published_at IS NULL;

The SET value can use not only a fixed value but also another column of the same row. Another SQL-like point is that WHERE uses IS NULL, not = NULL. SQL’s NULL is the state “no value,” not a value, so comparing it with = doesn’t evaluate to true. To test whether something is NULL, you use the dedicated IS NULL / IS NOT NULL.

And this UPDATE can be written as a single migration rather than executed directly in psql. In Django, with a mechanism called RunSQL, you write the SQL as-is in a migration file, and it’s executed at migrate time.

operations = [
    migrations.RunSQL(
        "UPDATE blog_post SET published_at = created_at WHERE published_at IS NULL;"
    ),
]

If you put this in before continuing with the migration that removes null=True, there are no more NULL rows, so adding the NOT NULL constraint passes safely. You can leave the order “UPDATE to fix the data → ALTER TABLE to change the constraint” recorded as the sequence of migrations.

Wrap-up

I’ve checked, one at a time, what SQL model changes become. Let me re-list the SQL that appeared this time.

• The four basic forms of ALTER TABLE. ADD COLUMN to add a column, DROP COLUMN to remove one, RENAME COLUMN to rename one, and ALTER COLUMN to change types or constraints
• CREATE INDEX to create an index. Since it’s an object separate from the table definition, it was an independent statement rather than ALTER TABLE
• UPDATE to rewrite data. You specify the value with SET, the target rows with WHERE, and write NULL tests with IS NULL

And I think this time’s takeaway, more than the individual syntax, is the sense that “change SQL has order and irreversibility.”

• Rename and delete + add are, in principle, indistinguishable from a diff. If misjudged, DROP COLUMN runs, and lost data can’t be recovered no matter how you write the SQL
• Changes like shrinking a type or adding NOT NULL fail if existing data violates them. Structure it in the order of fixing data with UPDATE first, then changing the type or constraint

None of these are Django knowledge but properties on the SQL side, so they should carry over to other ORMs as-is.

The practical conclusion is unchanged from the Model Definition article: “read with sqlmigrate before migrate.” Whether a rename became DROP COLUMN, and whether a change conflicting with existing data is mixed in, can be seen by reading the SQL before execution. Equivalent features exist in other ORMs (for Prisma it’s prisma migrate diff), so I think it’s a habit usable anywhere.

Next up is the Queries article. Now that I can create tables and change them, I’ll finally learn the queries themselves — the SELECT and UPDATE that run behind filter and update.

References

IT入門書籍 スッキリシリーズ

わかる・たのしい・実は深い!

https://sukkiri.jp/books/sukkiri_sql4

初めてのSQL 第3版

SQL言語に初めて触れるプログラマを対象に、SQL言語の基本を解説します。データベースの歴史、概念などの基礎知識から、データベースの作成、クエリの基本、フィルタリング、トランザクションなどまで、実践に必要なポイントを過不足なくコンパクトにまとめ、理解を深められるよう練習問題を用意しています。改訂にあたり、「ビュー」「メタデータ」「解析関数」「大規模なデータベースの操作」「SQLとビッグデータ」の章が追加され、さらに充実しました。SQLを学び始めるのに最適の一冊です。

https://www.oreilly.co.jp/books/9784873119588/