Waterline has very bad compatibility with PostgreSQL it's almost unusable

[sudo-apt-get-updates]

Node version: v18.18.2
Sails version (sails): 1.5.8
DB adapter & version (e.g. sails-mysql@5.55.5): "sails-postgresql": "^5.0.1"

---

When migrating from MariaDB to PostgreSQL there are a lot of issues that have more to do with Waterline than it has to do with the differences in the databases. I use the official Sails.js migration tool to create the database, however when it performs the JOINs PostgreSQL throws an error with the hint, "Do you mean table____table__table__.createdat?". Notice the lower-case, this means that Sails.js is creating tables with double quotes such as "createdAt" but when it performs a JOIN for some reason it is not handling these double quotes correctly. A fix for this seems to be to enforce lower case on all columns, that's fine but there's an even larger issue that just makes Waterline not usable:

0|project  | error: Sending 500 ("Server Error") response: 
0|project  |  message=Unexpected error from database adapter: invalid input syntax for type smallint: "true", stack=AdapterError: Unexpected error from database adapter: invalid input syntax for type smallint: "true"
0|project  |     at Object.fn (~/project/api/controllers/views/dashboard/design/template/view-template-widget.js:61:44)
0|project  |     at processTicksAndRejections (node:internal/process/task_queues:95:5), name=AdapterError, adapterMethodName=join, modelIdentity=template, leng
th=143, name=error, severity=ERROR, code=22P02, detail=undefined, hint=undefined, position=undefined, internalPosition=undefined, internalQuery=undefined, where
=unnamed portal parameter $1 = '...', schema=undefined, table=undefined, column=undefined, dataType=undefined, constraint=undefined, file=numutils.c, line=244, routine=pg_strtoint1

The error throws "invalid input syntax for type smallint: "true""

This is done via a .populate. So for example.

let dataStoreTemplate = await Template.findOne({
      id: templateId
    }).populate('templateContents', {
      where: {
        isHTML: true,
        isComponent: false,
      },
      sort: 'createdAt ASC'
    }).populate('templateComponents', {
      where: {
        isHTML: true,
        isComponent: true
      },
      sort: 'createdAt ASC'
    });

This is a very normal piece of code. Template finds id, then populates with "templateContents" and "templateComponents". However this throws the error: invalid input syntax for type smallint: "true". It works with MariaDB, it works with MongoDB. But the moment you switch to PostgreSQL it fails to work. Changing the columnType does not resolve the issue. The only thing that resolves it is removing "isHTML" and "isComponent". Both have the datatype "boolean" in it. This issue means that it's Waterline itself creating an issue when performing a JOIN operation. This is the isHTML, the isComponent is essentially the same.

isHTML: {
      type: 'boolean',
      description: 'Whether or not the templatecontent is HTML',
      extendedDescription: `If the content is not HTML then we assume that it is CSS`,
      required: true
    },

And in Template (the following is also done for templateComponents):

templateContents: {
      collection: 'templatecontent',
      via: 'template',
      through: 'jointemplatetemplatecontent'
    },

And in JoinTemplateTemplateContent

module.exports = {

  tableName: 'join_template_templatecontent',
  // autoCreatedAt: false,
  // autoUpdatedAt: false,

  attributes: {

    //  ╔═╗╦═╗╦╔╦╗╦╔╦╗╦╦  ╦╔═╗╔═╗
    //  ╠═╝╠╦╝║║║║║ ║ ║╚╗╔╝║╣ ╚═╗
    //  ╩  ╩╚═╩╩ ╩╩ ╩ ╩ ╚╝ ╚═╝╚═╝


    //  ╔═╗╔╦╗╔╗ ╔═╗╔╦╗╔═╗
    //  ║╣ ║║║╠╩╗║╣  ║║╚═╗
    //  ╚═╝╩ ╩╚═╝╚═╝═╩╝╚═╝


    //  ╔═╗╔═╗╔═╗╔═╗╔═╗╦╔═╗╔╦╗╦╔═╗╔╗╔╔═╗
    //  ╠═╣╚═╗╚═╗║ ║║  ║╠═╣ ║ ║║ ║║║║╚═╗
    //  ╩ ╩╚═╝╚═╝╚═╝╚═╝╩╩ ╩ ╩ ╩╚═╝╝╚╝╚═╝

    template: {
      model: 'template'
    },

    templatecontent: {
      model: 'templatecontent'
    },

    createdAt: false,
    updatedAt: false,

    // createdAt: { type: 'number', autoCreatedAt: false},
    //
    // updatedAt: { type: 'number', autoCreatedAt: false},


  },

};

[sailsbot]

@sudo-apt-get-updates Thanks for posting! We'll take a look as soon as possible.

In the mean time, there are a few ways you can help speed things along:

• look for a workaround. (Even if it's just temporary, sharing your solution can save someone else a lot of time and effort.)
• tell us why this issue is important to you and your team. What are you trying to accomplish? (Submissions with a little bit of human context tend to be easier to understand and faster to resolve.)
• make sure you've provided clear instructions on how to reproduce the bug from a clean install.
• double-check that you've provided all of the requested version and dependency information. (Some of this info might seem irrelevant at first, like which database adapter you're using, but we ask that you include it anyway. Oftentimes an issue is caused by a confluence of unexpected factors, and it can save everybody a ton of time to know all the details up front.)
• read the code of conduct.
• if appropriate, ask your business to sponsor your issue. (Open source is our passion, and our core maintainers volunteer many of their nights and weekends working on Sails. But you only get so many nights and weekends in life, and stuff gets done a lot faster when you can work on it during normal daylight hours.)
• let us know if you are using a 3rd party plugin; whether that's a database adapter, a non-standard view engine, or any other dependency maintained by someone other than our core team. (Besides the name of the 3rd party package, it helps to include the exact version you're using. If you're unsure, check out this list of all the core packages we maintain.)

---

Please remember: never post in a public forum if you believe you've found a genuine security vulnerability. Instead, disclose it responsibly.

For help with questions about Sails, click here.

[sudo-apt-get-updates]

Another issue is that this:

loggedinPublicUser = await PublicUser.findOne({
                owner: req.session.userId
              }).populate('communityUserSetting');

Works in MariaDB. No issues at all. Then in PostgreSQL "communityUserSetting" returns a null. However this works:

loggedinPublicUser = await PublicUser.findOne({
                owner: req.session.userId
              });
              let setting = await UserSettingCommunity.findOne({
                id: loggedInUser.id
              });
              loggedinPublicUser.communityUserSetting = setting;

Sails.js just seems to not work with PostgreSQL when it comes to populating. PostgreSQL version is 14.

[DominusKelvin]

Hey @sudo-apt-get-updates that's interesting. I have been using Sails and PostgreSQL for Sailscasts with lots of populate calls and I haven't seen that issue before. We will discuss this further in the core team meeting.