[Bug] Boolean values not casted properly when used in .find() condition

[robmarti]

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[x] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native

TypeORM version:

[x] latest
[ ] @next
[x] 0.2.0 (or put your version here)

Dear @pleerock and community,

before describing my actual problem, I want to thank you for this awesome project 👍

When developing a mobile application with Ionic (Cordova, respectively), I stumbled upon the following issue. Consider the following example:

I have a Product model, that looks as follows:

@Entity()
export class Product {

    // various columns here

    @Column()
    liked: boolean;
}

and a respective ProductRepository in order to handle access to this model. This works so far, and i can create / update / delete models fine.
However, if i want to show only the Products that are liked, I request data like this:

// repository is the ProductRepository!
products = await repository.find({
	liked : true
});

However, when executing this query, i get an empty result. Problem is, that the database used is a SQLite database (remember that i use Ionic!). Connection is set up properly (remember that I can save / update / delete models respectively). The Boolean attribute is internally represented as an integer in the SQLite database. When I use 1 instead of true, however, it works.

// repository is the ProductRepository!
products = await repository.find({
	liked : 1
});

How can i properly "configure" the ORM to use boolean values instead of tinyint for the find() calls?

Thank you very much for your help, really appreciate it!
Cheers

[pleerock]

strange this must work. Can you contribute and create a PR with a failing test that reproduces this issue?

[robmarti]

I tried but I was not able to set up the TestingConnections.
I added the following to the ormconfig.json file

{
    "type": "cordova",
    "database": "test",
    "location": "default",
    "logging": false
  }

and tried to create a connection inside the before hook

before(async () => connections = await createTestingConnections({
        enabledDrivers: ["cordova"],
        entities: [__dirname + "/entity/*{.js,.ts}"],
        schemaCreate: true
    }));

However when running my test I get a DriverPackageNotInstalledError that suggests to install cordova-sqlite-storage, which is already installed.

Do you have any suggestions on how to properly create a database connection using the CordovaDriver in my testcase ?

[robmarti]

I just tried to slightly alter the provided typeorm ionic-example by adding

@Column({
    type: Boolean,
    default: false,
 })
liked: boolean;

to the post.ts file and ran into the same issue.
The query log for inserting a new post looks fine :

query:  INSERT INTO "post"("title", "text", "liked", "authorId") VALUES (?, ?, ?, ?) 
-- PARAMETERS: ["Control flow ","TypeScript 2.0 ",1,1]

When calling postRepository.find( { liked: true } ) the liked parameter remains true instead of getting mapped to 1

query:  SELECT "Post"."id" AS "Post_id", "Post"."title" AS "Post_title", "Post"."text" AS "Post_text", 
"Post"."liked" AS "Post_liked", "Post"."authorId" AS "Post_authorId" FROM "post" "Post" 
WHERE "Post"."liked" = ? -- PARAMETERS: [true]

Same thing for postRepository.find( { where: { pinned: true } } ).

[pleerock]

true being mapped into 1 is a correct behaviour, in sqlite/cordova there is NO boolean type, we have to use tinyint instead.

[johannesschobel]

hey @pleerock ,

it is true, that sqlite / cordova does not support boolean types, but rather fallback to tinyint..
However, as @robmarti states, inserting an object, where you set pinned : true works fine (i.e., it is mapped correctly to 1; or 0 if you use false). If you want to query the same entry via repository.find( { pinned : true } ); however, it returns nothing, as there is no true in the database, but rather 1.

So the question would be, how to use boolean type in the model definition, but correctly map it to tinyint in the sqlite / cordova layer. So one can safely use the models (where respective fields are typed as boolean)..

Hope this helps!?
All the best and cheers!

[pleerock]

If you want to query the same entry via repository.find( { pinned : true } ); however, it returns nothing, as there is no true in the database, but rather 1.

@johannesschobel yes, thank you, I got it from the first @robmarti post, but based on hist last post I wanted to confirm that he understands that we map boolean to number anyway since sqlite does not support boolean.

[pleerock]

@robmarti you need to use sqlite driver instead of cordova or provide a git repo at least.

[johannesschobel]

@pleerock yes, we know that (@robmarti and me work together)..
Can we just use sqlite instead of cordova to fix this issue?

[pleerock]

I added test and it works for sqlite. @daniel-lang can you please help here, is it possible that cordova work differently?

[johannesschobel]

@pleerock wow, thank you very much for your awesome support..

@robmarti and me will try later today to switch from cordova to sqlite and give proper feedback on this one!

[pleerock]

@johannesschobel not sure if sqlite gonna work in mobiles, better to ask @daniel-lang

[robmarti]

I created a new repository based on the typeorm ionic-example and made some adaptations (as described above). You can fork the repository here. It would be awesome if @daniel-lang or @pleerock could give me feedback on this.
Thank you for your support @pleerock !

[daniel-lang]

From my understanding, this seems to work because sqlite is able to convert true into 1 and back internally. This is my guess because

• While debugging I couldn't find the moment it gets changed to a 1
• Logger reports the query as

SELECT "Product"."id" AS "Product_id", "Product"."liked" AS "Product_liked"
FROM "product" "Product" WHERE "Product"."liked" = ? -- PARAMETERS: [true]

So my conclusion for this issue would be, that cordova doesn't convert boolean values to interger values automatically. Not sure, where the appropiate place for this convertion would be.

[pleerock]

What if we add here:

            } else if (typeof value === "boolean") {
                return value === true ? 1 : 0;
            }

Can someone with cordova add locally this change and test changes?

[johannesschobel]

hey @daniel-lang ,

thank you for giving us feedback on this issue here.. However, the test-repository from @robmarti indicates a wrong behaviour of the find method in this regards.

@pleerock : good catch - we can try to apply changes locally and then give feedback on this issue..

[johannesschobel]

@daniel-lang :
I think, the true is not changed at all.. Problem here is, that SQLite internally represents the boolean type as an integer value (with 1 digit - as MySQL did some time ago.. they used tinyint for this, i think)..

So your typeorm package sends the ... WHERE liked = true to the database.. However, there is no entity with liked = true, because internally it is liked = 1

So we need to "cast" the boolean value to its numeric representation.. We will try as @pleerock suggested and give feedback on this

[robmarti]

hey @pleerock and @daniel-lang ,
thanks for your feedback. I tried to modify the escapeQueryWithParameters method as @pleerock suggested. However this did not solve the problem.
After some debugging, it seems that the actual values are not passed via the parameters but rather via the nativeParameters argument. I will provide a PR in order to properly address this issue. Thanks for pointing me into the right direction 😆

[daniel-lang]

fixed by #2110