Support for default values on SQL side or something like that #6454

New Issue

admin · 2026-01-22T15:33:33+01:00

admin commented

2026-01-22 15:33:33 +01:00

Originally created by @ctxnop on GitHub (Apr 23, 2020).

The issue

I looked for a similar issue as I'm pretty sure I'm not the first one to post that, but couldn't find one... "Default" is a bit too common...

I also now that it's a frequent question: https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/faq.html#how-can-i-add-default-values-to-a-column

But it's exactly because of this answer that I feel the urge to post this festure request!
"This is not necessary however, you can just use your class properties as default values." ?!

This is completly and utterly wrong! It's not the same thing at all!
Ok, in some cases it can be used as a workaround, but it's very very limited. But only on some super simple cases.

Default values are way more complex than just a literal value...
Here is a small and easy example using postgresql

-- Drop every thing if already exist
DROP TABLE IF EXISTS movies;
DROP TABLE IF EXISTS recommandations;
DROP FUNCTION IF EXISTS bestmovie;

-- Create a movie table
CREATE TABLE movies(
	id SERIAL PRIMARY KEY,
	name VARCHAR(64) NOT NULL,
	rank INT NOT NULL DEFAULT(-1)
);

-- Insert some data
INSERT INTO movies(name, rank)
VALUES ('AAA', 5),('BBB', 10);

-- Create a function
CREATE FUNCTION bestmovie()
	RETURNS INT AS $$
DECLARE
	movieid INT;
BEGIN
	SELECT id into movieid FROM movies ORDER BY rank DESC LIMIT 1;
	RETURN movieid;
END
$$ LANGUAGE plpgsql;

-- Create a recommandation table
CREATE TABLE recommandations(
	id SERIAL PRIMARY KEY,
	created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT(NOW()::timestamp),
	expire TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT(NOW()::timestamp + INTERVAL '1 day'),
	movie INT NOT NULL DEFAULT(bestmovie()) REFERENCES movies(id)
);

-- Create a recommandation (note that there is no data provided)
INSERT INTO recommandations DEFAULT VALUES;

-- Check the recommandations table
SELECT * FROM recommandations;

So, no, it's not the same thing a setting a default value to the entity's properties...
The whole point of having default values is to benefit from the database settings (which my not use the same datetime/timezone as your code), without having to query data, send them to the client, so that the client can forge a new query to send them back to the database.

The feature

At this point everybody should agree that the documentation linked above is wrong when it says that it's not necessary because defaults values on client side are the same thing.

So, first thing first, the documentation should be edited to present default values on client side as a limited workaround, not as the right solution. It never be and never will be the right solution.

Second, there is another workaround that better answer the question as it is asked: use columnDefinition to write your self the full definition so that you can use the DEFAULT keyword. The downsides of this are that you have to write the full definition your self (other attributes are ignored like type or nullability) and you may loose portability over some databases. But when you ask for default values it's because you known which database will be used. Also most databases that support defaults are using the same syntax so the loss will be limited.

And finally, you really should implement a support for default value, it's an important feature for many people who care about code quality, performances and data reliability.

There is two way of doing so. The first and obvious one is a full support of it. I understand that it can be really complex, slow to implement and requires a lot of testing.

You also can think of it differently: do not support it, just add a "customColumnDefinitionExtra" (pick the name you want) attribute which is added as is at the end of the column definition. So that users can write whatever they want without having to drop all other generated text (types, nullability, etc...)
Document that as a no warranty attribute and you are good to go. I can live with that.

Originally created by @ctxnop on GitHub (Apr 23, 2020). # The issue I looked for a similar issue as I'm pretty sure I'm not the first one to post that, but couldn't find one... "Default" is a bit too common... I also now that it's a frequent question: https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/faq.html#how-can-i-add-default-values-to-a-column But it's exactly because of this answer that I feel the urge to post this festure request! "This is not necessary however, you can just use your class properties as default values." ?! **This is completly and utterly wrong! It's not the same thing at all!** Ok, in some cases it can be used as a workaround, but it's very very limited. But only on some super simple cases. Default values are way more complex than just a literal value... Here is a small and easy example using postgresql ```sql -- Drop every thing if already exist DROP TABLE IF EXISTS movies; DROP TABLE IF EXISTS recommandations; DROP FUNCTION IF EXISTS bestmovie; -- Create a movie table CREATE TABLE movies( id SERIAL PRIMARY KEY, name VARCHAR(64) NOT NULL, rank INT NOT NULL DEFAULT(-1) ); -- Insert some data INSERT INTO movies(name, rank) VALUES ('AAA', 5),('BBB', 10); -- Create a function CREATE FUNCTION bestmovie() RETURNS INT AS $$ DECLARE movieid INT; BEGIN SELECT id into movieid FROM movies ORDER BY rank DESC LIMIT 1; RETURN movieid; END $$ LANGUAGE plpgsql; -- Create a recommandation table CREATE TABLE recommandations( id SERIAL PRIMARY KEY, created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT(NOW()::timestamp), expire TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT(NOW()::timestamp + INTERVAL '1 day'), movie INT NOT NULL DEFAULT(bestmovie()) REFERENCES movies(id) ); -- Create a recommandation (note that there is no data provided) INSERT INTO recommandations DEFAULT VALUES; -- Check the recommandations table SELECT * FROM recommandations; ``` So, no, it's not the same thing a setting a default value to the entity's properties... The whole point of having default values is to benefit from the database settings (which my not use the same datetime/timezone as your code), without having to query data, send them to the client, so that the client can forge a new query to send them back to the database. # The feature At this point everybody should agree that the documentation linked above is wrong when it says that it's not necessary because defaults values on client side are the same thing. So, first thing first, the documentation should be edited to present default values on client side as a limited workaround, not as the right solution. It never be and never will be the right solution. Second, there is another workaround that better answer the question as it is asked: use columnDefinition to write your self the full definition so that you can use the DEFAULT keyword. The downsides of this are that you have to write the full definition your self (other attributes are ignored like type or nullability) and you may loose portability over some databases. But when you ask for default values it's because you known which database will be used. Also most databases that support defaults are using the same syntax so the loss will be limited. And finally, you really should implement a support for default value, it's an important feature for many people who care about code quality, performances and data reliability. There is two way of doing so. The first and obvious one is a full support of it. I understand that it can be really complex, slow to implement and requires a lot of testing. You also can think of it differently: do not support it, just add a "customColumnDefinitionExtra" (pick the name you want) attribute which is added as is at the end of the column definition. So that users can write whatever they want without having to drop all other generated text (types, nullability, etc...) Document that as a no warranty attribute and you are good to go. I can live with that.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6454