Add Field.db_default for defining database defaults

[jws]

​https://github.com/django/django/pull/13709

Apply this diff to django/core/mamagement.py

Should work on any database.

75a76,77

if f.default <> meta.fields.NOT_PROVIDED:

field_output.append("DEFAULT '%s'" % (f.default,))

[Adrian Holovaty]

I'm marking this as a wontfix because we don't have a way of converting Python objects to SQL-friendly syntax, for insertion into the "DEFAULT" clause in a CREATE TABLE statement. For example, the default value "John's test", which has a quote in it, may have to be represented differently in SQL, depending on the backend. Unfortunately, not all of the database modules (psycopg, MySQLdb, etc.) expose functionality that quotes the values.

[jws]

GOAL:
Django should express the structure and relationships of the models in the database schema as fully as possible within the capabilities of a specific backend. This is important when working with other tools that do not use the Django ORM.

PROBLEM:
When django-admin creates sql statements in response to the 'sql','sqlclear' and related sub-commands, string concatenation is used rather than the parameterized queries that are used in the normal object manipulation code. If default values for fields contain delimiting characters(',",;,etc), the resulting sql will contain errors. The string describing the default value must be processed by a backend-specific character-escaping method that will alter any problem characters before it can be inserted into the sql statement.

SOLUTION:
When describing a field with a default value, if the backend has a method called escapechars(), a sql 'DEFAULT' clause is assembled and inserted. If no such method exists, no additional clause is inserted, though the Django objects still produce default values.

IMPLEMENTATION:
Postgresql: Updated and tested
Sqlite: Updated, not tested
Mysql: Not updated
MS-MSQL: Not updated

Extra notes:

pymysqldb exposes a quoting function that could simply be wrapped.

I'll update this patch again when I get the ability to test the other backends. Help is appreciated. However, patch only adds new functionality. Unmodified backends should continue to work as before.

[jws]

Second rev, with addition of escaping function

[Jacob]

Re-marking as wontfix.

[Jws]

patch updated for pending release of 0.95

[jws]

I have updated my patch to the current svn with magic-removal mainlined in. The patch now supports all backends, as well. I would like to get this in in time for the 0.95 release.

[jws]

update for 2900

[newpers]

it would be nice to see this merged into the trunk. as i stated in #django, an ORM can't do everything so there is a need for custom sql. it frustrates me when i do an INSERT, but it doesn't know what to default to. try doing:

I actually needed this today: INSERT INTO tbl DEFAULT VALUES;

[Gary Wilson <gary.wilson@…>]

0.92 is long gone.

[Jacob]

Again, this is wontfix for the reasons Adrian articulated.

[(none)]

Milestone Version 1.0 deleted

[Cedric Shock <cedric@…>]

I am reopening this as the reasons Adrian articulated were addressed in later patches without further rebuttal. Default values would be very useful in introspective upgrading of databases, although other means of providing values to new fields per existing row through a more complete upgrade architecture would also be desirable.

I have a few issues with how this patch works. The first is that the user is to provide the default value in the format of a string to the database, not in the format of a value of the field. This can be solved easily with the Field get_db_prep_save:

newest patch, management,py, new line 173:

escaped_string =  django.db.backend.escapechars(f.get_db_prep_save(f.default))

Furthermore the code makes the assumption that values shall be quoted with single quotes. It would be more appropriate for the escapechars interface to return an entire quoted string usable as a value (move the single quotes into each escapechars function, making new line 174 in the same file like:)

field_output.append(style.SQL_KEYWORD("DEFAULT %s" % (escaped_string,)))

Furthermore, the implementation of the escapechars (or otherwise named) function should be done as much as is possible and appropriate using routines provided by the various backends (psycodb, etc.).

The DB-API way of dealing with escaped values is via the cursor execute. That would change these two lines to something like:

field_output.append(style.SQL_KEYWORD("DEFAULT %%s")
field_output_params.append(f.get_db_prep_save(f.default))

However this last way would require complete reworking of the management.py everything to support passing these params around with the sql strings, and emulation of DB-API execute substitution, for each backend, to output SQL statements. It would be nice if the DB-API interface also provided something like execute_sql(...) and executemany_sql(...) which returned strings such that execute(execute_sql(...)) and execute(...) were functionally equivalent.

I'd also be tempted to rename escapechars to escapedefault due to its very specific intent, or to escapeparam if it is intended to work on what DB-API calls parameters in general.

Furthermore, default values for fields should be reflected in the admin interface.

[Simon G. <dev@…>]

& once again around the Design-Decision-needed loop...

[James Bennett]

Still needs to address the issue of callable defaults -- since those are calculated on the fly at the time of insertion, it's not going to be possible to express them in SQL.

[Jacob]

Once again, I'm marking wontfix. Let's bring this up on django-dev if more debate is needed.

[aron45]

Hi, bumping up here old ticket.
I think this is a very important feature, as I'm writing the sql my self for this reason.

any solutions?

[Marcin Nowak]

Please reopen it and implement using INSERT ... RETURNING (for postgres). This may be optional behaviour dependent on db engine used.
Defaults computed at the database level are very important. This may be optional feature, but it should be available. The database is most important and long-lived part of any bigger system.

Python-like callbacks passed as defaults shouldn't be supported, of course. They may behave same as before and they should not be listed in RETURNING clause.

[Shai Berger]

There was a discussion on this topic, with some partial work to support the feature, about two years ago: ​https://groups.google.com/d/topic/django-developers/3mcro17Gb40/discussion

[Tim Graham]

Reopening as per the mailing list discussion in the previous comment.

[Paul Tiplady]

Further justification for this feature -- it makes zero-downtime DB migrations easier: #29266.

[Marcin Nowak]

Hi.

Django 3.x generates sql with drop default:

        ALTER TABLE "x" ADD COLUMN "y" integer DEFAULT 0 NOT NULL CHECK ("y" >= 0);                                
        ALTER TABLE "x" ALTER COLUMN "y" DROP DEFAULT;  

The whole thing is about not adding DROP DEFAULT. How about some kind of option for database backend?

[Vaibhav Awachat]

Currently using this ​https://github.com/3YOURMIND/django-add-default-value to run migrations without downtime using MySQL in strict mode.

[Marcin Nowak]

Hi. 15 years have passed since this important bug report. Every time we're uploading new version of the application, which includes new not nullable fields in db tables, our service is failing. Db changes are applied first, then app services are restarting on all servers (it takes few minutes). During this time old version loaded into memory is failing due to missing defaults:

IntegrityError
null value in column "X" violates not-null constraint DETAIL: Failing row contains (...)

For us, default values must be set at the database layer. Please add support of db_default attribute.

[Anders Hovmöller]

I am getting bitten by this quite a lot.

I would suggest that long term it would be a good idea to have one field for the default value set in the database schema and one that is dynamic and set on the Django level. Maybe default and default_db to keep backwards compatibility, or default_dynamic and default to improve the default.

[John Whitlock]

Hi all. I've struggled with the lack of this feature as well. Thanks to Ayush Joshi for assigning me.

Ian Foote's previous work is in ​PR 13709. I've been able to rebase it and get tests passing, so it looks like a good approach. I'll start from there, guided by the feedback to Ian's PR.

[John Whitlock]

I'm still in the code-reading and planning phase. This is a big one!

A lot of the changes for PR 13709 were implemented in the database backend, such as quoting expressions and varying implementations. The reviewers prefer a DefaultExpression class, analogous to the IndexExpression class, and to use similar strategies as IndexExpression for backend-specific implementations. They also note that there is some support for database-level defaults in primary keys (PK), and wonder if a generic database-default field could borrow from the PK design, or if the PK feature could be built on top of a generic database-default field.

Here's a rough idea of how this should work:

• Tests may be needed to verify feature support in different backends, along with some new backend feature flags. For example:
  • ​sqlite3 supports ALTER TABLE ADD COLUMN for simple database defaults, but not for complex expressions. These can be supported by recreating the table, which is a more expensive option but commonly used for sqlite3 migrations.
  • ​mysql 8.0.13 added support for default expressions
  • ​postgres supports RETURNING to return database-created values, which may be useful
  • ​oracle does not support bind variables in DDL statements, breaking a strategy in PR 13709.
• Fields grow a new db_default parameter. It is distinct from the default parameter, and different combinations should be allowed. Simple values and more complicated expressions are allowed. Some expressions, such as a calculated value based on other fields, might be rejected at the Django level, and some may be rejected by the chosen backend. There are some checks that could be done at model initialization to verify a valid db_default value, with an appropriate new error message. There may be warnings for features supported on some but not all backends. The default value is preferred for saving, creating, and bulk-updating models - in other words, in every place where an application would pick an unspecified value - leaving current behavior unchanged.
• If the default is not given but the db_default is, then a database read may be needed to determine the database-created value (such as with refresh_from_db()). If this is done anyway (for example, to determine the database-created primary key), it would be nice to get the db_default-initialized fields as well.
• The migrations system should notice changes in the db_default field, and prepare a migration for it. There will be backend-specific strategies to implement migrations that change these fields.
• Database introspecting should understand column defaults, and if possible add them when creating models from introspection.

There may be more required features. I'm reading the Django database and test code to get familiar with them and to discover other features, and see if there is a way to break this into multiple PRs.

[John Whitlock]

I've reviewed the tests (so many tests!), the database code, and PR 13709. While PR 13709 worked, I'm going to try a different approach. As Marcin Nowak noted in May 2020, the SQL for adding a column looks like:

ALTER TABLE "x" ADD COLUMN "y" integer DEFAULT 0 NOT NULL CHECK ("y" >= 0);
ALTER TABLE "x" ALTER COLUMN "y" DROP DEFAULT;

That's the simple case. SQLite is a bit more complex, because it doesn't support the ALTER COLUMN ... DROP DEFAULT clause, and re-creates the table to get the same effect. A DateTimeField with auto_add_now looks like this on PostgreSQL:

BEGIN;
ALTER TABLE "x" ADD COLUMN "y" timestamp with time zone DEFAULT '2022-09-06T14:37:26.493371+00:00'::timestamptz NOT NULL;
ALTER TABLE "x" ALTER COLUMN "y" DROP DEFAULT;
COMMIT;

The current behaviour is:

1. Add the column with a DEFAULT of a static value of the field default (for example, computing the current time), to populate existing row
2. Drop the column DEFAULT

I want to keep this as the default behaviour, but allow a new behaviour for step 2:

1. Add the column with a DEFAULT of a static value of the field default (for example, computing the current time), to populate existing row
2. Update the column DEFAULT to the value of the field db_default value or expression. If it has the same value as step 1, do nothing

For the integer case, the SQL would just be the the first line

ALTER TABLE "x" ADD COLUMN "y" integer DEFAULT 0 NOT NULL CHECK ("y" >= 0);

For the datetime case, the SQL might be:

BEGIN;
ALTER TABLE "x" ADD COLUMN "y" timestamp with time zone DEFAULT '2022-09-06T14:37:26.493371+00:00'::timestamptz NOT NULL;
ALTER TABLE "x" ALTER COLUMN "y" DEFAULT CURRENT_TIMESTAMP AT TIME ZONE 'utc';
COMMIT;

Some other features:

• db_default takes a new constant NO_DEFAULT for the current behaviour of dropping the DEFAULT clause
• db_default takes a new constant USE_DEFAULT to copy the value of default
• db_default allows static values or a class derived from a new DefaultExpression class, a subclass of Expression
• db_default does not allow a generic callable, unlike default
• Classes like DateTimeField have logic to "do the right thing" when db_default=USE_DEFAULT is combined with auto_add_now or auto_now.
• Classes like AutoField may have more restrictive rules for db_default, since they maintain control of how the database chooses new values.

I think there should be a way to set a general preference for NO_DEFAULT or USE_DEFAULT, at the Model Options, AppConfig, and Settings level, similar to how DEFAULT_AUTO_FIELD was implemented in Django 3.2

A few use cases:

• Developers that prefer the current behavior do nothing.
• Developers that use simple default values and want similar database defaults use USE_DEFAULT at the Settings level. A migration is needed to add database defaults across the application. They can add overrides at the Field, Model Options, and AppConfig level to fix issues or avoid the new behavior.
• Developers that want a database-default for an individual field, to support a rolling deployment, can set db_default=USE_DEFAULT for added columns, and remove it (if desired) in a future migration and deployment.

I'm open to feedback, here or on the django-developers group.

[John Whitlock]

I hit a dead-end with that approach. The field code can not access the Model Options or App Config as initialization, so I would have to half-initialize a field until it gets added to a model. That is awkward, and would go against the existing code which fully initializes all fields (with the exception of a primary key) before adding them to the Model.

I was also finding myself wondering what happened if db_default was set, and default was not.

I'm attempting a new approach that does not use db_default. Instead, I have a BaseDefault class that has more direct control of the default policy. You can assign an instance of BaseDefault or a subclass to default, and the Field class behaves differently, and eventually the Schema class will as well.

My first goal is to replicate the DateField and DateTimeField with auto_now and auto_now_add, but through the new class. Then I'll extend the class and the migrations Schema code to tune the the pre-migration and post-migration column defaults. The behaviors I've identified so far:

• Override Field.__init__ parameters, such as editable and blank (auto_now)
• Set the field value on pre_save (auto_now)
• Test if there is a Python-level default (has_default)
• Get a callable version of the Python-level default (_get_default(), cached)
• Pick the DEFAULT x value when adding a column

and the new behavior:

• Pick the DEFAULT x value after adding a column

I _think_ this will allow a small changes to:

• Persist simple defaults (something like changing default=0 to default=DatabaseDefault(0))
• Persist DateTimeField and DateField (something like changing auto_now_add=True to DateTimeDatabaseDefault(auto_now_add=True))
• Allow for per-database formatting of the DEFAULT clause, via Expression or similar

[David Sanders]

Looks like Lily's opened a new PR with their newly rebased branch 🎉 Default + generated columns are in my top 10 list of wanted features for Django.

I wanted to just add my 2¢ worth to anyone interested:

If we look at supporting returning values from inserts (with RETURNING in pg and who knows what mechanism for other dbs) … could we also consider leaving the design "open" to possibly accommodate generated columns from both inserts and updates? 😊

[John Whitlock]

See ​https://github.com/django/django/pull/16092 - Good luck Lily Foote!

[Adrian Torres]

Has anybody thought of implementing this feature without an extra Field parameter? IMO it could work with Field.default already:

• If the value being passed is a Python literal, use a db default
• If the value is a DefaultExpression (or whatever it's actually called), use a db default
• If the value is a callable, use a Python default

Or am I missing something?

[bcail]

It may be useful to have a clear distinction between python and DB defaults, and not switch between the two based on what the default value is.

[Adrian Torres]

I would argue that there is no legitimate reason to want to set e.g. a literal value as default through python instead of through SQL.

Query expressions seem to be supported by Fields.default, I haven't seen the actual implementation but I imagine it generates SQL that calculates a value and is set for every row in python, so it would make sense that these are always treated as a database default as well.

Callables are not supported in the db_default implementation since it only makes sense to process them in python, calculating and setting the value for every row.

The only case I can think of where having both default and db_default in a single field definition might be useful is having a default based on a python callable and having the db_default as a fallback in case the callable returned None or some "undesirable" value, but that seems complicated and far from the common case, IMO.

For me having both default and db_default seems like an easy trap to fall into for beginners unaware of Django's implementation details, and for more seasoned developers a hassle and an easy mistake to make that can be costly.

[Lily Foote]

Replying to Adrian Torres:

I would argue that there is no legitimate reason to want to set e.g. a literal value as default through python instead of through SQL.

The main reason I did it this way is backward compatibility, especially with regards to migrations. If someone adds a field with a default in Django 4.1, what should we do when they upgrade to 4.2?

I agree that if we were starting from scratch, we could probably support both approaches with one default parameter, but I think it would cause too much pain to add that now.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 7414704:

Fixed #470 -- Added support for database defaults on fields.

Special thanks to Hannes Ljungberg for finding multiple implementation
gaps.

Thanks also to Simon Charette, Adam Johnson, and Mariusz Felisiak for
reviews.