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

**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},

},

};

```

Start with the failing populate query in api/controllers/views/dashboard/design/template/view-template-widget.js:61 and reproduce it against PostgreSQL using the supplied Template, TemplateContent, and JoinTemplateTemplateContent definitions. Trace the Waterline join handling for boolean filters and quoted column names, comparing the PostgreSQL behavior with MariaDB or MongoDB. Done means the populate query works without the smallint/boolean error and the reported JOIN identifier issue is addressed.

Written by the indexing model from the issue text.