Opened 19 years ago

Closed 19 months ago

#470 closed New feature (fixed)

Add Field.db_default for defining database defaults

Reported by: jws Owned by: Lily Foote
Component: Database layer (models, ORM) Version: dev
Severity: normal Keywords: sql schema
Cc: Ryan Hiebert, Ian Foote, Ryan Moore, Václav Řehák, Hannes Ljungberg, Marcin Nowak, Adrian Turjak, Doug Harris, Leigh Brenecki, Johannes Maron, Charlie Denton, Matt Goldman, raydeal, Todor Velichkov, Alex Scott, David Sanders, Lily Foote, bcail, Adrian Torres, Michael Rosner Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Anders Hovmöller)

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,))

Attachments (4)

management.diff (145 bytes ) - added by jws 19 years ago.
470-2.patch (2.7 KB ) - added by jws 19 years ago.
Second rev, with addition of escaping function
470-2813.patch (5.8 KB ) - added by Jws 19 years ago.
patch updated for pending release of 0.95
470-2900.diff (4.8 KB ) - added by jws 19 years ago.
update for 2900

Download all attachments as: .zip

Change History (73)

by jws, 19 years ago

Attachment: management.diff added

comment:1 by Adrian Holovaty, 19 years ago

Summary: diff to cause 'default' values to be expressed in sql schema[patch] "default" values should be expressed in SQL schema

comment:2 by Adrian Holovaty, 19 years ago

Resolution: wontfix
Status: newclosed

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.

comment:3 by jws, 19 years ago

Resolution: wontfix
Status: closedreopened

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.

by jws, 19 years ago

Attachment: 470-2.patch added

Second rev, with addition of escaping function

comment:4 by Jacob, 19 years ago

Resolution: wontfix
Status: reopenedclosed

Re-marking as wontfix.

by Jws, 19 years ago

Attachment: 470-2813.patch added

patch updated for pending release of 0.95

comment:5 by jws, 19 years ago

Resolution: wontfix
Status: closedreopened

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.

by jws, 19 years ago

Attachment: 470-2900.diff added

update for 2900

comment:6 by anonymous, 19 years ago

milestone: Version 0.92
Version: 0.91

comment:7 by newpers, 19 years ago

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;

comment:8 by Gary Wilson <gary.wilson@…>, 18 years ago

milestone: Version 0.92Version 1.0

0.92 is long gone.

comment:9 by Jacob, 18 years ago

Resolution: wontfix
Status: reopenedclosed

Again, this is wontfix for the reasons Adrian articulated.

comment:10 by (none), 18 years ago

milestone: Version 1.0

Milestone Version 1.0 deleted

comment:11 by Cedric Shock <cedric@…>, 18 years ago

Patch needs improvement: set
Resolution: wontfix
Status: closedreopened

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.

comment:12 by Simon G. <dev@…>, 18 years ago

Triage Stage: UnreviewedDesign decision needed
Version: 0.91

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

comment:13 by James Bennett, 17 years ago

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.

comment:14 by Jacob, 17 years ago

Resolution: wontfix
Status: reopenedclosed

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

comment:15 by aron45, 10 years ago

Easy pickings: unset
UI/UX: unset

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?

comment:16 by Marcin Nowak, 7 years ago

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.

comment:17 by Shai Berger, 7 years ago

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

comment:18 by Tim Graham, 7 years ago

Has patch: unset
Patch needs improvement: unset
Summary: [patch] "default" values should be expressed in SQL schemaAdd Field.db_default for defining database defaults
Triage Stage: Design decision neededAccepted
Type: enhancementNew feature

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

comment:19 by Tim Graham, 7 years ago

Resolution: wontfix
Status: closednew

comment:20 by Paul Tiplady, 7 years ago

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

Last edited 7 years ago by Tim Graham (previous) (diff)

comment:21 by Ryan Hiebert, 7 years ago

Cc: Ryan Hiebert added

comment:22 by Simon Charette, 6 years ago

Cc: Simon Charette added

comment:23 by Ian Foote, 6 years ago

Cc: Ian Foote added

comment:24 by Ryan Moore, 6 years ago

Cc: Ryan Moore added

comment:25 by Václav Řehák, 5 years ago

Cc: Václav Řehák added

comment:26 by Hannes Ljungberg, 5 years ago

Cc: Hannes Ljungberg added

comment:27 by Marcin Nowak, 5 years ago

Cc: Marcin Nowak added

comment:28 by Marcin Nowak, 5 years ago

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?

Version 0, edited 5 years ago by Marcin Nowak (next)

comment:29 by Vaibhav Awachat, 4 years ago

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

comment:30 by Marcin Nowak, 4 years ago

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.

comment:31 by Simon Charette, 4 years ago

Cc: Simon Charette removed

comment:32 by Adrian Turjak, 4 years ago

Cc: Adrian Turjak added

comment:33 by Doug Harris, 4 years ago

Cc: Doug Harris added

comment:34 by Ian Foote, 4 years ago

Owner: changed from nobody to Ian Foote
Status: newassigned

comment:35 by Ian Foote, 4 years ago

Has patch: set
Needs documentation: set

comment:36 by Leigh Brenecki, 4 years ago

Cc: Leigh Brenecki added

comment:37 by Ian Foote, 4 years ago

Needs documentation: unset

comment:38 by Johannes Maron, 4 years ago

Cc: Johannes Maron added

comment:39 by Mariusz Felisiak, 3 years ago

Patch needs improvement: set

comment:40 by Charlie Denton, 3 years ago

Cc: Charlie Denton added

comment:41 by Anders Hovmöller, 3 years ago

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.

comment:42 by Anders Hovmöller, 3 years ago

Description: modified (diff)

comment:43 by Matt Goldman, 3 years ago

Cc: Matt Goldman added

comment:44 by Ayush Joshi, 3 years ago

Owner: changed from Ian Foote to Ayush Joshi

comment:45 by raydeal, 3 years ago

Cc: raydeal added

comment:46 by Todor Velichkov, 3 years ago

Cc: Todor Velichkov added

comment:47 by Alex Scott, 3 years ago

Cc: Alex Scott added

comment:48 by Ayush Joshi, 2 years ago

Owner: changed from Ayush Joshi to John Whitlock

comment:49 by John Whitlock, 2 years ago

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.

comment:50 by John Whitlock, 2 years ago

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.

comment:51 by David Sanders, 2 years ago

Cc: David Sanders added

comment:52 by John Whitlock, 2 years ago

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.

comment:53 by John Whitlock, 2 years ago

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

comment:54 by Lily Foote, 2 years ago

Cc: Lily Foote added

comment:55 by David Sanders, 2 years ago

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? 😊

comment:56 by John Whitlock, 2 years ago

Owner: changed from John Whitlock to Lily Foote

comment:57 by bcail, 2 years ago

Cc: bcail added

comment:58 by Adrian Torres, 2 years ago

Cc: Adrian Torres added

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?

comment:59 by bcail, 2 years ago

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.

comment:60 by Adrian Torres, 2 years ago

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.

comment:61 by Lily Foote, 2 years ago

Patch needs improvement: unset

in reply to:  60 comment:62 by Lily Foote, 2 years ago

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.

Last edited 2 years ago by Lily Foote (previous) (diff)

comment:63 by Michael Rosner, 2 years ago

Cc: Michael Rosner added

comment:64 by Mariusz Felisiak, 22 months ago

Needs documentation: set
Patch needs improvement: set
Version: dev

comment:65 by Lily Foote, 22 months ago

Needs documentation: unset
Patch needs improvement: unset

comment:66 by Mariusz Felisiak, 21 months ago

Patch needs improvement: set

comment:67 by Lily Foote, 21 months ago

Patch needs improvement: unset

comment:68 by Mariusz Felisiak, 19 months ago

Triage Stage: AcceptedReady for checkin

comment:69 by Mariusz Felisiak <felisiak.mariusz@…>, 19 months ago

Resolution: fixed
Status: assignedclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top