Question: How to use array parameters, OR alternative?

[kevinfiol]

There was a similar question two weeks ago #126 , although the author closed the issue.

Currently, doing something like this does not work, which makes sense because SQLite doesn't work this way:

const names = ['kevin', 'john', 'mary'];

const select = db.prepare(`
  select *
  from profile
  where name in (:names)
`);

const rows = select.all({ names });

As a workaround, I'm currently doing this:

const names = ['kevin', 'john', 'mary'];
const paramStr = Array(names.length).fill("?").join(",")

const select = db.prepare(`
  select *
  from profile
  where name in (${paramStr})
`);

const rows = select.all(...names);

Since I'm constructing the paramStr, there isn't room for SQL injection, but still, manipulating the SQL string like this gives room for error in more complex queries. Is there a better alternative?

[DjDeveloperr]

I see, this is not the best approach but it’s the only approach SQLite3 gives us really. There is no API to pass an array via bind parameters in the default SQLite build. Only some primitive types are supported and arrays are not one of them.

Even when searching on StackOverflow, solutions like this are the accepted ones. Other solutions include storing these values into a table but that would only make sense if there are a lot of values. Or you could look into using JSON which should work with pretty decent performance.

SQLite3 also has a carray extension which allows passing array pointers directly, it’s not available by default though. If implemented, seems like the most performant way.

[kevinfiol]

Thanks for the detailed answer. I will probably create a wrapper/helper function to handle this more cleanly.

Closing this issue for now.