Easy Bug: Empty Array in PG orm/query-builder

Handling empty arrays within applications that utilize PostgreSQL can be a source of potential bugs. Here outlines two common scenarios encountered when working with Kysely, a TypeScript query builder for SQL databases, and provides solutions to mitigate these issues.

Scenario 1: Querying with an Empty Array

When performing a query that includes an empty array as a parameter, unexpected behavior may occur. Consider the following example:

const search = async (ids: string[]) => 
  await db.selectFrom("posts")
          .where("id", "in", ids)
          .selectAll()
          .execute();  

In this instance, the search function is designed to retrieve posts based on their IDs. However, if the ids parameter is an empty array, one might expect the query to return no results. Contrary to this expectation, the function returns all posts in the database. This behavior can lead to unintended data exposure and should be carefully managed.

Recommended Solution: Implement logic to check for an empty ids array before executing the query. If the array is empty, the function should return an empty result set or handle the scenario as per the application's requirements.

Scenario 2: Inserting with an Empty Array

Another issue arises when attempting to insert records that include an empty array. Consider the following code snippet:

const createShow = async (val: { name: string, episodeIds: string[]}) => 
 await db.insertInto("shows")
         .values(val)
         .returningAll()
         .executeTakeFirstOrThrow();

Here, the createShow function is intended to insert a new show into the database, where episodeIds is an array of episode identifiers. When episodeIds is an empty array, the function throws an error, which can disrupt the flow of the application.

Recommended Solution: To prevent this error, it is advised to replace the empty array with sqlARRAY[]::text[]`` in the insert statement. This adjustment allows for the explicit handling of empty arrays and ensures that the insertion operation completes successfully without exceptions.