@platformatic/sql-mapper - npm Package Compare versions

Comparing version 0.33.1 to 0.34.0

lib/entity.js

@@ -203,2 +203,3 @@ 'use strict'
     like: 'LIKE',
+    ilike: 'ILIKE',
     any: 'ANY',
@@ -245,3 +246,3 @@ all: 'ALL'
           criteria.push(sql`${sql.ident(field)} IS NOT NULL`)
-        } else if (operator === 'LIKE') {
+        } else if (operator === 'LIKE' || operator === 'ILIKE') {
           let leftHand = sql.ident(field)
@@ -253,3 +254,4 @@ // NOTE: cast fields AS CHAR(64) and TRIM the whitespaces
           }
-          criteria.push(sql`${leftHand} LIKE ${value[key]}`)
+          const like = operator === 'LIKE' ? sql`LIKE` : queries.hasILIKE ? sql`ILIKE` : sql`LIKE`
+          criteria.push(sql`${leftHand} ${like} ${value[key]}`)
         } else if (operator === 'ANY' || operator === 'ALL') {
@@ -256,0 +258,0 @@ throw new Error('Unsupported operator for non Array field')

lib/queries/mysql-shared.js

@@ -118,1 +118,3 @@ 'use strict'
 }
+
+module.exports.hasILIKE = false

lib/queries/pg.js

@@ -130,1 +130,3 @@ 'use strict'
 module.exports.listEnumValues = listEnumValues
+
+module.exports.hasILIKE = true

lib/queries/sqlite.js

@@ -248,1 +248,3 @@ 'use strict'
 module.exports.updateMany = updateMany
+
+module.exports.hasILIKE = false

mapper.d.ts

@@ -103,2 +103,6 @@ import { FastifyPluginAsync, FastifyInstance, FastifyReply, FastifyRequest } from 'fastify'
     like?: string
+    /**
+     * Like ignore-case value.
+     */
+    ilike?: string
   }
@@ -105,0 +109,0 @@ }

package.json

 {
   "name": "@platformatic/sql-mapper",
-  "version": "0.33.1",
+  "version": "0.34.0",
   "description": "A data mapper utility for SQL databases",
@@ -32,3 +32,3 @@ "main": "mapper.js",
     "inflected": "^2.1.0",
-    "@platformatic/types": "0.33.1"
+    "@platformatic/types": "0.34.0"
   },
@@ -35,0 +35,0 @@ "tsd": {

test/where.test.js

@@ -743,1 +743,143 @@ 'use strict'
 })
+
+test('ILIKE', async ({ pass, teardown, same, equal }) => {
+  const mapper = await connect({
+    ...connInfo,
+    log: fakeLogger,
+    async onDatabaseLoad (db, sql) {
+      teardown(() => db.dispose())
+      pass('onDatabaseLoad called')
+
+      await clear(db, sql)
+
+      if (isSQLite) {
+        await db.query(sql`CREATE TABLE posts (
+          id INTEGER PRIMARY KEY,
+          title VARCHAR(42),
+          long_text TEXT,
+          counter INTEGER
+        );`)
+      } else {
+        await db.query(sql`CREATE TABLE posts (
+          id SERIAL PRIMARY KEY,
+          title VARCHAR(42),
+          long_text TEXT,
+          counter INTEGER
+        );`)
+      }
+    }
+  })
+
+  const entity = mapper.entities.post
+
+  const posts = [
+    {
+      title: 'Dog',
+      longText: 'The Dog barks',
+      counter: 1
+    },
+    {
+      title: 'Cat',
+      longText: 'The Cat meows',
+      counter: 2
+    },
+    {
+      title: 'Potato',
+      longText: 'The Potato is vegetable',
+      counter: 3
+    },
+    {
+      title: 'Atmosphere',
+      longText: 'The atmosphere is not a sphere',
+      counter: 4
+    },
+    {
+      title: 'planet',
+      longText: 'The planet have atmosphere',
+      counter: 14
+    }
+  ]
+
+  await entity.insert({
+    inputs: posts
+  })
+
+  same(await entity.find({ where: { title: { ilike: '%at' } } }), [{
+    id: '2',
+    title: 'Cat',
+    longText: 'The Cat meows',
+    counter: 2
+  }], 'where: { title: { like: \'%at\' } }')
+
+  same(await entity.find({ where: { title: { ilike: '%at%' } } }), [{
+    id: '2',
+    title: 'Cat',
+    longText: 'The Cat meows',
+    counter: 2
+  },
+  {
+    id: '3',
+    title: 'Potato',
+    longText: 'The Potato is vegetable',
+    counter: 3
+  },
+  {
+    id: '4',
+    title: 'Atmosphere',
+    longText: 'The atmosphere is not a sphere',
+    counter: 4
+  }], 'where: { title: { ilike: \'%at%\' } }')
+
+  same(await entity.find({ where: { title: { ilike: 'at%' } } }), [{
+    id: '4',
+    title: 'Atmosphere',
+    longText: 'The atmosphere is not a sphere',
+    counter: 4
+  }], 'where: { title: { ilike: \'at%\' } }')
+
+  same(await entity.find({ where: { longText: { ilike: '%is%' } } }), [{
+    id: '3',
+    title: 'Potato',
+    longText: 'The Potato is vegetable',
+    counter: 3
+  },
+  {
+    id: '4',
+    title: 'Atmosphere',
+    longText: 'The atmosphere is not a sphere',
+    counter: 4
+  }], 'where: { longText: { ilike: \'%is%\' } }')
+
+  same(await entity.find({ where: { longText: { ilike: null } } }), [], 'where: { longText: { ilike: null } }')
+
+  if (!isSQLite) {
+    same(await entity.find({ where: { counter: { ilike: 4 } } }), [{
+      id: '4',
+      title: 'Atmosphere',
+      longText: 'The atmosphere is not a sphere',
+      counter: 4
+    }], 'where: { counter: { ilike: 4 } }')
+  }
+
+  same(await entity.find({ where: { counter: { ilike: '%4' } } }), [{
+    id: '4',
+    title: 'Atmosphere',
+    longText: 'The atmosphere is not a sphere',
+    counter: 4
+  },
+  {
+    id: '5',
+    title: 'planet',
+    longText: 'The planet have atmosphere',
+    counter: 14
+  }], 'where: { counter: { ilike: \'%4\' } }')
+
+  same(await entity.find({ where: { counter: { ilike: '4%' } } }), [{
+    id: '4',
+    title: 'Atmosphere',
+    longText: 'The atmosphere is not a sphere',
+    counter: 4
+  }], 'where: { counter: { ilike: \'4%\' } }')
+
+  same(await entity.find({ where: { counter: { ilike: null } } }), [], 'where: { counter: { ilike: null } }')
+})

No alert changes

Improved metrics

Total package byte prevSize

172826

2.2%

Lines of code

5479

2.55%

Dependency changes

• + Added

  @platformatic/types@0.34.0
  (transitive)

• - Removed

  @platformatic/types@0.33.1
  (transitive)

• Updated

  @platformatic/types@0.34.0