Opened 19 years ago
Closed 17 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 )
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)
Change History (73)
by , 19 years ago
Attachment: | management.diff added |
---|
comment:1 by , 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 , 19 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
comment:3 by , 19 years ago
Resolution: | wontfix |
---|---|
Status: | closed → reopened |
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.
comment:5 by , 18 years ago
Resolution: | wontfix |
---|---|
Status: | closed → reopened |
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.
comment:6 by , 18 years ago
milestone: | → Version 0.92 |
---|---|
Version: | → 0.91 |
comment:7 by , 18 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:9 by , 18 years ago
Resolution: | → wontfix |
---|---|
Status: | reopened → closed |
Again, this is wontfix for the reasons Adrian articulated.
comment:11 by , 18 years ago
Patch needs improvement: | set |
---|---|
Resolution: | wontfix |
Status: | closed → reopened |
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 , 18 years ago
Triage Stage: | Unreviewed → Design decision needed |
---|---|
Version: | 0.91 |
& once again around the Design-Decision-needed loop...
comment:13 by , 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 , 17 years ago
Resolution: | → wontfix |
---|---|
Status: | reopened → closed |
Once again, I'm marking wontfix. Let's bring this up on django-dev if more debate is needed.
comment:15 by , 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 , 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 , 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 , 7 years ago
Has patch: | unset |
---|---|
Patch needs improvement: | unset |
Summary: | [patch] "default" values should be expressed in SQL schema → Add Field.db_default for defining database defaults |
Triage Stage: | Design decision needed → Accepted |
Type: | enhancement → New feature |
Reopening as per the mailing list discussion in the previous comment.
comment:19 by , 7 years ago
Resolution: | wontfix |
---|---|
Status: | closed → new |
comment:20 by , 7 years ago
Further justification for this feature -- it makes zero-downtime DB migrations easier: #29266.
comment:21 by , 7 years ago
Cc: | added |
---|
comment:22 by , 6 years ago
Cc: | added |
---|
comment:23 by , 6 years ago
Cc: | added |
---|
comment:24 by , 6 years ago
Cc: | added |
---|
comment:25 by , 5 years ago
Cc: | added |
---|
comment:26 by , 5 years ago
Cc: | added |
---|
comment:27 by , 4 years ago
Cc: | added |
---|
comment:28 by , 4 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?
comment:29 by , 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 , 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 , 4 years ago
Cc: | removed |
---|
comment:32 by , 4 years ago
Cc: | added |
---|
comment:33 by , 4 years ago
Cc: | added |
---|
comment:34 by , 4 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:35 by , 4 years ago
Has patch: | set |
---|---|
Needs documentation: | set |
comment:36 by , 4 years ago
Cc: | added |
---|
comment:37 by , 4 years ago
Needs documentation: | unset |
---|
comment:38 by , 3 years ago
Cc: | added |
---|
comment:39 by , 3 years ago
Patch needs improvement: | set |
---|
comment:40 by , 3 years ago
Cc: | added |
---|
comment:41 by , 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 , 3 years ago
Description: | modified (diff) |
---|
comment:43 by , 3 years ago
Cc: | added |
---|
comment:44 by , 3 years ago
Owner: | changed from | to
---|
comment:45 by , 3 years ago
Cc: | added |
---|
comment:46 by , 2 years ago
Cc: | added |
---|
comment:47 by , 2 years ago
Cc: | added |
---|
comment:48 by , 2 years ago
Owner: | changed from | to
---|
comment:49 by , 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 , 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.
- sqlite3 supports
- Fields grow a new
db_default
parameter. It is distinct from thedefault
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 validdb_default
value, with an appropriate new error message. There may be warnings for features supported on some but not all backends. Thedefault
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 thedb_default
is, then a database read may be needed to determine the database-created value (such as withrefresh_from_db()
). If this is done anyway (for example, to determine the database-created primary key), it would be nice to get thedb_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 , 2 years ago
Cc: | added |
---|
comment:52 by , 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:
- Add the column with a
DEFAULT
of a static value of the fielddefault
(for example, computing the current time), to populate existing row - Drop the column
DEFAULT
I want to keep this as the default behaviour, but allow a new behaviour for step 2:
- Add the column with a
DEFAULT
of a static value of the fielddefault
(for example, computing the current time), to populate existing row - Update the column
DEFAULT
to the value of the fielddb_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 constantNO_DEFAULT
for the current behaviour of dropping theDEFAULT
clausedb_default
takes a new constantUSE_DEFAULT
to copy the value ofdefault
db_default
allows static values or a class derived from a newDefaultExpression
class, a subclass ofExpression
db_default
does not allow a generic callable, unlikedefault
- Classes like
DateTimeField
have logic to "do the right thing" whendb_default=USE_DEFAULT
is combined withauto_add_now
orauto_now
. - Classes like
AutoField
may have more restrictive rules fordb_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 useUSE_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 , 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 aseditable
andblank
(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
todefault=DatabaseDefault(0)
) - Persist
DateTimeField
andDateField
(something like changingauto_now_add=True
toDateTimeDatabaseDefault(auto_now_add=True)
) - Allow for per-database formatting of the
DEFAULT
clause, viaExpression
or similar
comment:54 by , 2 years ago
Cc: | added |
---|
comment:55 by , 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 , 2 years ago
Owner: | changed from | to
---|
See https://github.com/django/django/pull/16092 - Good luck Lily Foote!
comment:57 by , 23 months ago
Cc: | added |
---|
comment:58 by , 23 months ago
Cc: | 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 , 22 months 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.
follow-up: 62 comment:60 by , 22 months 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 , 22 months ago
Patch needs improvement: | unset |
---|
comment:62 by , 22 months 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.
comment:63 by , 22 months ago
Cc: | added |
---|
comment:64 by , 20 months ago
Needs documentation: | set |
---|---|
Patch needs improvement: | set |
Version: | → dev |
comment:65 by , 20 months ago
Needs documentation: | unset |
---|---|
Patch needs improvement: | unset |
comment:66 by , 19 months ago
Patch needs improvement: | set |
---|
comment:67 by , 18 months ago
Patch needs improvement: | unset |
---|
comment:68 by , 17 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
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.