Waterline sets unspecified fields to NULL by itself. (Waterline sets type: 'ref' fields to null, the base value.)

[dilame]

I have a model

{
attributes: {
    text: 'string',
    createdAt: {
      type: 'ref',
      columnType: 'timestamp',
    }
  },
}

If i do

model.create({text: 'MESSAGE'})

I get createdAt field NULL in my database. But i have DEFAULTS TO on that field, and i want database to fill createdAt by itself. I think waterline is taking on too much.

[sailsbot]

Hi @dilame! It looks like you may have removed some required elements from the initial comment template, without which I can't verify that this post meets our contribution guidelines. To re-open this issue, please copy the template from here, paste it at the beginning of your initial comment, and follow the instructions in the text. Then post a new comment (e.g. "ok, fixed!") so that I know to go back and check.

Sorry to be a hassle, but following these instructions ensures that we can help you in the best way possible and keep the Sails project running smoothly.

*If you feel this message is in error, or you want to debate the merits of my existence (sniffle), please contact inquiries@sailsjs.com

[johnabrams7]

@dilame This is the expected behavior because null is the base value for the ref type.

[dilame]

What shoud i do if i want my database to decide what value to put in the field?
I developed database with the rich-featured RDBMS. I have

createdAt timestamp with time zone default now()

I definitely need this field filled on RDBMS layer, so i faithfully don't specify it in .create() method. But waterline for some reason specifies it for me, and gives me no chance to fix it.
But i need to read this value, so i can't just remove it from attributes.
Maybe waterline could choose a girlfriend for me 🤣?

[mikermcneil]

@dilame Good question!

Sails is an opinionated framework, and Waterline is an opinionated ORM. Waterline is only concerned with the logical layer of your data model.

Why?

Default literals set at the physical layer (in the database) do not always translate neatly to JavaScript R-values, and thinking about business processes and intermediate data with physical-layer data types instead of JavaScript types has been a major mistake I've made too many times to count. I got sick of it.

Consider the possibilities: Should your app consider a "TIMESTAMP" from MySQL a string? Or a number? Or should it try to build a Date instance, mixing in convoluted UTC offset information?

More importantly, what business value does spending time on that problem even provide? In my experience, it's essentially just a huge time-suck.

But you can't ignore logical datatypes either-- because that causes its own set of problems. For example, what happens if you try to call .length on the data you get back from the database, assuming it's a string? What about if you assume it is a number and try to do +1000*60 to push it one minute into the future? Or worst yet, maybe your app assumes it's a JavaScript Date and starts trying to use Date.prototype methods on it.

Sails ≥1.0 and Waterline ≥0.13 aim to eliminate those kinds of problems by providing simple, opinionated tools for working with data using logical, JavaScript-compatible (and therefore also browser-compatible) data types.

The data type system in Waterline isn't optimized for variations in programming style, it's about conventions. But I know from experience that it is flexible enough to support the data model of every app I've ever worked on.

I hope this makes sense-- and please let us know what you think if you get the opportunity to try out Waterline's logical data types in an app!

PS. Unfortunately, Waterline won't be able to automatically pick perfectly-matched romantic partners until v3.0 🏩

[johnabrams7]

Hey @dilame , we're moving all the Waterline issues to the main Sails repo (balderdashy/sails). Feel free to update this case as needed. We appreciate all the info so far. I see a response to this case - feel free to update us if the issue is ongoing. Thank you!

[hassench]

I have the same issue, in my Database I have a default value
usr_creation_datetimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
Waterline sets usr_creation_date to null automatically, the database refuses to set it and I have
Error: ER_BAD_NULL_ERROR: Column 'usr_creation_date' cannot be null

[hassench]

The temporary solution was to deactivate explicit_defaults_for_timestamp from MySql

SET GLOBAL explicit_defaults_for_timestamp = 0;

[eashaw]

Hey @hassench, glad you found something that works for you.

[dilame]

I am still sure - such waterline behavior is the abuse of authority, not opinion. The database is the priority - the client code working with the database is secondary. You should not need to change your database just for waterline quirks.
If i don't set some field - let it be my own decision. Don't decide for me that I've forgotten something, because I haven't forgotten.

[eashaw]

Thanks for your feedback @dilame. Waterline is opinionated, and as you might expect we didn't make these decisions lightly. Here's how we reached the logical data types that are in Waterline today: https://docs.google.com/spreadsheets/d/1whV739iW6O9SxRZLCIe2lpvuAUqm-ie7j7tn_Pjir3s/edit?usp=sharing