Opened 17 years ago

Last modified 3 days ago

#470 assigned New feature

Add Field.db_default for defining database defaults

Reported by: jws Owned by: John Whitlock
Component: Database layer (models, ORM) Version:
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 Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
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 17 years ago.
470-2.patch (2.7 KB) - added by jws 17 years ago.
Second rev, with addition of escaping function
470-2813.patch (5.8 KB) - added by Jws 16 years ago.
patch updated for pending release of 0.95
470-2900.diff (4.8 KB) - added by jws 16 years ago.
update for 2900

Download all attachments as: .zip

Change History (59)

Changed 17 years ago by jws

Attachment: management.diff added

comment:1 Changed 17 years ago by Adrian Holovaty

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

comment:2 Changed 17 years ago by Adrian Holovaty

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 Changed 17 years ago by jws

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.

Changed 17 years ago by jws

Attachment: 470-2.patch added

Second rev, with addition of escaping function

comment:4 Changed 17 years ago by Jacob

Resolution: wontfix
Status: reopenedclosed

Re-marking as wontfix.

Changed 16 years ago by Jws

Attachment: 470-2813.patch added

patch updated for pending release of 0.95

comment:5 Changed 16 years ago by jws

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.

Changed 16 years ago by jws

Attachment: 470-2900.diff added

update for 2900

comment:6 Changed 16 years ago by anonymous

milestone: Version 0.92
Version: 0.91

comment:7 Changed 16 years ago by 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;

comment:8 Changed 16 years ago by Gary Wilson <gary.wilson@…>

milestone: Version 0.92Version 1.0

0.92 is long gone.

comment:9 Changed 16 years ago by Jacob

Resolution: wontfix
Status: reopenedclosed

Again, this is wontfix for the reasons Adrian articulated.

comment:10 Changed 16 years ago by (none)

milestone: Version 1.0

Milestone Version 1.0 deleted

comment:11 Changed 16 years ago by Cedric Shock <cedric@…>

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 Changed 16 years ago by Simon G. <dev@…>

Triage Stage: UnreviewedDesign decision needed
Version: 0.91

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

comment:13 Changed 15 years ago by 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.

comment:14 Changed 15 years ago by Jacob

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 Changed 8 years ago by aron45

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 Changed 5 years ago by 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.

comment:17 Changed 5 years ago by 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

comment:18 Changed 5 years ago by Tim Graham

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 Changed 5 years ago by Tim Graham

Resolution: wontfix
Status: closednew

comment:20 Changed 5 years ago by Paul Tiplady

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

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

comment:21 Changed 5 years ago by Ryan Hiebert

Cc: Ryan Hiebert added

comment:22 Changed 4 years ago by Simon Charette

Cc: Simon Charette added

comment:23 Changed 4 years ago by Ian Foote

Cc: Ian Foote added

comment:24 Changed 4 years ago by Ryan Moore

Cc: Ryan Moore added

comment:25 Changed 3 years ago by Václav Řehák

Cc: Václav Řehák added

comment:26 Changed 3 years ago by Hannes Ljungberg

Cc: Hannes Ljungberg added

comment:27 Changed 2 years ago by Marcin Nowak

Cc: Marcin Nowak added

comment:28 Changed 2 years ago by 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?

Last edited 2 years ago by Marcin Nowak (previous) (diff)

comment:29 Changed 2 years ago by Vaibhav Awachat

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

comment:30 Changed 2 years ago by 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.

comment:31 Changed 2 years ago by Simon Charette

Cc: Simon Charette removed

comment:32 Changed 23 months ago by Adrian Turjak

Cc: Adrian Turjak added

comment:33 Changed 23 months ago by Doug Harris

Cc: Doug Harris added

comment:34 Changed 22 months ago by Ian Foote

Owner: changed from nobody to Ian Foote
Status: newassigned

comment:35 Changed 22 months ago by Ian Foote

Has patch: set
Needs documentation: set

comment:36 Changed 20 months ago by Leigh Brenecki

Cc: Leigh Brenecki added

comment:37 Changed 18 months ago by Ian Foote

Needs documentation: unset

comment:38 Changed 16 months ago by Johannes Maron

Cc: Johannes Maron added

comment:39 Changed 15 months ago by Mariusz Felisiak

Patch needs improvement: set

comment:40 Changed 14 months ago by Charlie Denton

Cc: Charlie Denton added

comment:41 Changed 12 months ago by 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.

comment:42 Changed 12 months ago by Anders Hovmöller

Description: modified (diff)

comment:43 Changed 9 months ago by Matt Goldman

Cc: Matt Goldman added

comment:44 Changed 7 months ago by Ayush Joshi

Owner: changed from Ian Foote to Ayush Joshi

comment:45 Changed 6 months ago by raydeal

Cc: raydeal added

comment:46 Changed 4 months ago by Todor Velichkov

Cc: Todor Velichkov added

comment:47 Changed 4 months ago by Alex Scott

Cc: Alex Scott added

comment:48 Changed 7 weeks ago by Ayush Joshi

Owner: changed from Ayush Joshi to John Whitlock

comment:49 Changed 7 weeks ago by 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.

comment:50 Changed 6 weeks ago by 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.

comment:51 Changed 4 weeks ago by David Sanders

Cc: David Sanders added

comment:52 Changed 3 weeks ago by 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.

comment:53 Changed 2 weeks ago by 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

comment:54 Changed 3 days ago by Lily Foote

Cc: Lily Foote added

comment:55 Changed 3 days ago by 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? 😊

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