# Empty string in database instead of NULL

**URL:** <https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039>\
**Category:** Need Help\
**Created:** [December 29, 2021, 7:06pm UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039 "2021-12-29T19:06:53Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![shadowx.jb](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shadowx.jb](https://discourse.cakephp.org/u/shadowx.jb)\
**Post date:** [December 29, 2021, 7:06pm UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/1 "2021-12-29T19:06:53Z")

</div>

If I enter a value from a field that is empty via a form, an entry with an empty string is stored in the database. Could it be NULL instead?

Is there anything to set, or is it a PHP CAKE feature?

Thank You

---

<div class="post-metadata">

**Author:** ![KevinPfeifer](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/kevinpfeifer/32/3365_2.png) [@KevinPfeifer](https://discourse.cakephp.org/u/KevinPfeifer)\
**Post date:** [December 29, 2021, 11:04pm UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/2 "2021-12-29T23:04:35Z")

</div>

Well first of all the DB column needs be allowed to have `NULL` values.  
You can check that in the structure/schema of your table.

If so none other than a validator like

```auto
$validator->scalar( 'my_columnname' )
  ->allowEmptyString( 'my_columnname' );

```

allows you to save `null` values as well.

---

<div class="post-metadata">

**Author:** ![shadowx.jb](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shadowx.jb](https://discourse.cakephp.org/u/shadowx.jb)\
**Post date:** [December 30, 2021, 7:46am UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/3 "2021-12-30T07:46:03Z")

</div>

Yes, I have the column set to NULL, I have validation set in the model `->allowEmptyString('my_columnname');` after save in the DB it is not NULL but empty.  
How to save a record so that it is NULL in the DB?

---

<div class="post-metadata">

**Author:** ![KevinPfeifer](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/kevinpfeifer/32/3365_2.png) [@KevinPfeifer](https://discourse.cakephp.org/u/KevinPfeifer)\
**Post date:** [December 30, 2021, 10:18am UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/4 "2021-12-30T10:18:34Z")

</div>

Which DBMS are you using and which type does your column have?

---

<div class="post-metadata">

**Author:** ![shadowx.jb](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shadowx.jb](https://discourse.cakephp.org/u/shadowx.jb)\
**Post date:** [December 30, 2021, 10:37am UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/5 "2021-12-30T10:37:34Z")

</div>

Maria DB 10.4.13, type column is TEXT, or VARCHAR (utf8mb4\_unicode\_ci)

---

<div class="post-metadata">

**Author:** ![KevinPfeifer](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/kevinpfeifer/32/3365_2.png) [@KevinPfeifer](https://discourse.cakephp.org/u/KevinPfeifer)\
**Post date:** [December 30, 2021, 10:45am UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/6 "2021-12-30T10:45:50Z")

</div>

You will need the Nullable behavior of the Shim Plugin.

Follow the Install instructions here

> <https://github.com/dereuromark/cakephp-shim/blob/master/docs/Install.md>

And then how to configure the behavior here

> <https://github.com/dereuromark/cakephp-shim/blob/master/docs/Model/Nullable.md>

---

<div class="post-metadata">

**Author:** ![shadowx.jb](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shadowx.jb](https://discourse.cakephp.org/u/shadowx.jb)\
**Post date:** [December 30, 2021, 10:56am UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/7 "2021-12-30T10:56:55Z")

</div>

Thank you, it is not possible without the plugin, or do I have to have another type of db?

---

<div class="post-metadata">

**Author:** ![KevinPfeifer](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/kevinpfeifer/32/3365_2.png) [@KevinPfeifer](https://discourse.cakephp.org/u/KevinPfeifer)\
**Post date:** [December 30, 2021, 11:05am UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/8 "2021-12-30T11:05:49Z")

</div>

No, this is not related to your DB Type.  
CakePHP in general tries to keep the data as consistent as possible so you don’t run into problems based upon this structure (like having to do `where field != '' AND field IS NOT NULL` queries)

There has already been a lengthy discussion about this problem here:

> <https://github.com/cakephp/cakephp/issues/9678>
>
> I faced this issue multiple times already. You can work around this issue but I …think it should be a part of the ORM.
> 
> Table:
> \`\`\`
> CREATE TABLE categories
> (
> id INTEGER PRIMARY KEY NOT NULL,
> name VARCHAR(128) NOT NULL,
> meta\_title VARCHAR(255)
> );
> \`\`\`
> 
> ORM usage:
> \`\`\`
> $entity = $this-\>Categories-\>newEntity($this-\>request-\>data);
> $this-\>Categories-\>save($entity);
> \`\`\`
> 
> Current result:
> 
> | id | name | meta\_title |
> | -------- |:------------:| ---------:|
> | 1 | Test | |
> 
> Should be:
> 
> | id | name | meta\_title |
> | -------- |:------------:| ---------:|
> | 1 | Test | \_NULL\_ |
> 
> As I mentioned above you can work around this behavior with \`Behaviors/Callbacks\` but I think this should be something which is handled directly inside the ORM. Saving an empty string inside a NULLable field isn't the proper usage of \`NULL\`.

---

<div class="post-metadata">

**Author:** ![shadowx.jb](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shadowx.jb](https://discourse.cakephp.org/u/shadowx.jb)\
**Post date:** [December 30, 2021, 11:12am UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/9 "2021-12-30T11:12:17Z")

</div>

Therefore, it is recommended that you do not enter a NULL value in the column.

---

<div class="post-metadata">

**Author:** ![KevinPfeifer](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/kevinpfeifer/32/3365_2.png) [@KevinPfeifer](https://discourse.cakephp.org/u/KevinPfeifer)\
**Post date:** [December 30, 2021, 11:41am UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/10 "2021-12-30T11:41:47Z")

</div>

actually if you do the same with a `int` column CakePHP will save `NULL` instead of`""` because there is no `""` value for int columns.

You do know, that in the HTML form there is no difference between `""` and `null` right? You can’t represent those 2 states just with an `<input type="text">` and therefore the submitted form will always return `""` for that input regardless of if it needs to be `null` or `""` in the database.

CakePHP just used this approach to keep the data in the DB consistent.

---

<div class="post-metadata">

**Author:** ![shadowx.jb](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shadowx.jb](https://discourse.cakephp.org/u/shadowx.jb)\
**Post date:** [December 30, 2021, 2:19pm UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/11 "2021-12-30T14:19:19Z")

</div>

I understand, thank you very much for the explanation

---

<div class="post-metadata">

**Author:** ![jarekgol](https://avatars.discourse-cdn.com/v4/letter/j/58956e/32.png) [@jarekgol](https://discourse.cakephp.org/u/jarekgol)\
**Post date:** [January 5, 2022, 10:24am UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/12 "2022-01-05T10:24:04Z")

</div>

you can debug (display) your entity after patch or before save and you can try change field manually to NULL and save it. If this works, (check in DB via other tool) just add in beforeSave

```auto
if (emtpy($entity->your_string) ) $entity->your_string = null;

```

---

<div class="post-metadata">

**Author:** ![magic77](https://avatars.discourse-cdn.com/v4/letter/m/ee59a6/32.png) [@magic77](https://discourse.cakephp.org/u/magic77)\
**Post date:** [January 3, 2024, 2:28am UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/13 "2024-01-03T02:28:46Z")

</div>

I see it differently, because why does a previously defined field or fields have to change the field type if no data has been transferred to this/these field(s)?  
The simplest solution would be to define the following without any shims/plugins in the corresponding model(s):

```auto
public function beforeMarshal(EventInterface $event, ArrayObject $data, ArrayObject $options): void
    {
        foreach ($data as $key => $value) {
            if ($value === '') {
                $data[$key] = null;
            }
        }
    }

```

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [January 3, 2024, 6:25am UTC](https://discourse.cakephp.org/t/empty-string-in-database-instead-of-null/10039/14 "2024-01-03T06:25:00Z")

</div>

This would work if you have _only_ fields that prefer null values to empty strings. There are use cases where null and empty strings say very different things (e.g. null means “not initialized”, while empty string means “initialized to an empty value”).