[Question] How to I do SELECT WHERE...IN?

[s7130457]

I have array and want to use SELECT * FROM table WHERE uid IN (array of uid)
for example:
const uid = [1,2,3]
and I use sql

const sql = `SELECT * FROM user WHERE uid IN $uid`
const param = {
    uid: uid
}
const result = await sqlite.prepare(sql).run(param)

But I get error message : APIError: near "$taskUid": syntax error

When I want to use WHERE...IN, how can I do?
thanks

[JoshuaWise]

This is a duplicate of #125. But, in short, SQLite does now allow you to bind arrays to prepared statements. You have two options:

Construct the SQL dynamically

const uid = [1, 2, 3];
const sql = `SELECT * FROM user WHERE uid IN (${uid.map(() => '?').join(',')})`;
const result = sqlite.prepare(sql).all(uid);

Note that this example only works if the uid array has at least one item.

Perform multiple queries

const sql = `SELECT * FROM user WHERE uid = ?`;
const stmt = sqlite.prepare(sql);
const myTransaction = sqlite.transaction((uid) => {
  return uid.map(x => stmt.get(x));
});
const result = myTransaction([1, 2, 3]);

Side note, better-sqlite3 is a synchronous library, so you don't need await, as you have in your example.

[KyleAMathews]

BTW. I prepared a quick benchmark for the two above methods & transactions are quite a bit faster (probably as you don't need to call prepare each time).

select where...in x 12,405 ops/sec ±0.74% (87 runs sampled)
select transaction x 17,069 ops/sec ±1.42% (88 runs sampled)

const Benchmark = require(`benchmark`);
var suite = new Benchmark.Suite();
const fs = require(`fs`);
const path = require(`path`);

const db = require(`.`)(`./temp/benchmark.db`);
db.pragma("journal_mode = MEMORY");
db.pragma("synchronous = OFF");
db.pragma("locking_mode = EXCLUSIVE");
db.pragma("temp_store = MEMORY");
// db.pragma("page_size = 65535");
db.exec(`CREATE TABLE "test" (
"id"	INTEGER,
"path"	TEXT,
"blob"	BLOB,
PRIMARY KEY("id")
);`);
console.log(db);
const stmt = db.prepare("INSERT INTO test VALUES (:id, :path, :blob)");
const readstmt = db.prepare(`SELECT * FROM test WHERE rowid >= ? LIMIT 100`);
const selectRowId = db.prepare(`SELECT * FROM test WHERE rowid = ?`);

// Long string for inserting/selecting
const str = `{"componentChunkName":"component---src-templates-custom-page-home-template-js","path":"/","result":{"data":{"contentfulCustomPage":{"id":"fe9e5397-0de4-5830-b010-088f66427501","contentful_id":"3N0RqKCSHL0zRTdyYVoS00","spaceId":"vkdbses00qqt","name":"New Home page","slug":"new-home","contentGap":null,"seoDescription":null,"seoTitle":"Gatsby | Blazing Fast Front-End Using Any CMS Or Service","socialMediaImage":{"file":{"url":"//images.ctfassets.net/vkdbses00qqt/4griHByQ7M1BL0C3mdjts/a004c1cf1de006af61fa6775ac747f68/og-image_home.png"}},"template":"Home","content":[{"__typename":"ContentfulPageSection","id":"432e89db-0fb5-5e6f-abc7-11394ff87783","name":"Home > Header","contentful_id":"3LJTKnUFi6MPsgyhvhN7eo","spaceId":"vkdbses00qqt","content":[{"__typename":"ContentfulHeading","id":"10f21c09-f449-5923-9cbf-925951afa78e","name":"Home Page > Header > Heading","contentful_id":"2SaDbQjy49yraglOiTBUGA","spaceId":"vkdbses00qqt","text":{"text":"One Front-end to <span>Rule Them All</span>"}},{"__typename":"ContentfulMarkdown","id":"12920378-7aa2-58be-9f63-87d545bc2740","name":"Home Page > Header > Lede","contentful_id":"4kvzeFZsm1mndkl0TcKfLA","spaceId":"vkdbses00qqt","body":{"childMarkdownRemark":{"html":"<p>Create blazing fast websites and apps <strong>AND</strong> harness the power of 2000+ plugins</p>"}}},{"__typename":"ContentfulCta","id":"8cd2ab37-e0f5-5966-96b0-77e2715c648d","name":"Home Page > Header > Start building","contentful_id":"5OGRtzhh4Su7R9glIIfuMJ","spaceId":"vkdbses00qqt","anchorText":"Start building","href":"/docs/","target":null},{"__typename":"ContentfulCta","id":"66126279-49db-57da-9179-737e52ad7a8b","name":"Home Page > Header > Learn more","contentful_id":"6aJCQHGK8hobsVcn22BumV","spaceId":"vkdbses00qqt","anchorText":"Learn more","href":"#gatsby-is-fast","target":null},{"__typename":"ContentfulPicture","id":"a99d5f93-09dd-5543-a107-57dc6c514717","name":"Home Page > Header > Hero Image","contentful_id":"4IW3Z7coA0BwKXXxzMKiwy","spaceId":"vkdbses00qqt","image":{"file":{"url":"//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg"},"fluid":{"tracedSVG":"data:image/svg+xml,%3csvg%20xmlns='http://www.w3.org/2000/svg'%20width='400'%20height='347'%20viewBox='0%200%20400%20347'%20preserveAspectRatio='none'%3e%3cpath%20d='M230%2034v17l-1%2017h5l4-1c0-2%201-2%204-2%202%200%203%200%202%201l-1%201c1%201%2039%202%2046%201l8-1V37l1-2-34-1h-34M60%2085v17h7a455%20455%200%200027%201l16%201%2017-1%201%208v25h-17l-17%201-1%2013v13H78a528%20528%200%2000-30%200c-16-1-16-1-16%207l1%209v2l-1%203c0%203%205%205%207%204h6l2%201c0%201-3%202-7%202-7-1-8%200-8%205v5h16l17-1h1l3-1h2l14%202h9v-30l9-1%209-1%202-1%201%201%207%201h6v-34h11c6%200%2010%200%209-1v-1l-1-1v-1h1l1-1h6l6-1-6-1-6-1-3-1c-2%201-2%201-2-1v-2l-1-6c0-6%200-6-2-6a178%20178%200%200145%200c-3%200-8%206-8%208%200%201-2%205-5%207-4%204-4%205-3%207s1%202-3%202h-5v34h17l33%201h17v18l-2-1c-3-1-4-1-2%202l3%206c0%204%200%204-2%204l-4%201h-15v6h11l11%201h-22v20h-23l-22%201-1%204v39h-34v-4c0-6-5-16-8-15-3%200-3%202%200%202%202%201%202%201-1%201-4%200-7-5-4-7%202-2%203-3%201-3-3%200-7%2012-4%2013l3%201v1l-2%201%202%201%201%205c-1%205-1%205%202%206s4%202%203%203l4%208%202-4%201%2011%201%2015h34v-35h34v20h68v-7l-1-6v-10h-33a549%20549%200%2001-1-1h34v-3c1-3%201-3%203-2h10c1-2%201-2-2-3-3%200-4-2-4-6s0-4-3-4-4%201-4%202l-1%201-1-1h-4c-3%202-10%202-11-1%200-1%200-2%204-2%203%200%204%201%204%202h1l3-2c4%201%206%200%206-2-1-3-6-3-56-3-12%200-12%200-12-2s1-3%202-3c4-1%204-1%205%201%200%202%202%203%202%200l11-1c16%200%2014%201%2014-16v-17a294%20294%200%200153%200l25-1h24v33h33V137h-33v33h-17l-16%201v1c-2%202-1-35%200-37h3c2%202%206%202%206-1l-2-1-1-2-1-2-2%201c0%202-5%204-6%203l-6-1c-4%201-5%200-6-1h-3l-4%201c-2%200-3%200-4%202s-1%202-1-1l-1-4-1%204c0%203%200%203-3%203h-4v-33l-17-1h-16v15l-2-4c-6-13-22-14-30-2l-3%203-1-2v-1l2-5v-4h-68l-1-17V68H60v17m141-3c-2%201-2%204-1%206%202%202%205%201%206-1l1-2%201%202c2%204%207%202%207-3%200-3-3-5-6-2-2%202-2%202-3%200-2-2-3-2-5%200M62%20117v1l-1-1h-1c-1%202-1%203%201%203v1c-2%200%200%207%203%2010l2-1c3-2%205-1%206%204%201%202%202%202%205%202%205%200%2010-3%2010-5v-1l1-1v-1l1%201h1l3-6c2-4%201-6-1-5l-5%201-4%203-3%201-2%202-1%202-1%202-1%204-1-2c1-4-1-8-3-7l-1%201-1%201c-2%200-2-1-2-3h3c2%200%202%200%201-2s-2-3-4-2l-2-2-2-2-1%202m66%2070v17h13l-5%203c-15%209-9%2031%209%2031%2017%200%2023-22%208-31l-4-3h12v-34h-33v17m-21%2019c-4%204-5%209-5%2019%200%205%200%205%203%204%202%200%203%200%204%202%201%203%200%205-3%205-1%200-2%200-1%201%200%202%2011%201%2011%200s1-2%203-2c4%200%206-2%205-3h2v-1l-1-11c1-10%201-10-3-14-4-3-11-3-15%200m6%207c-2%201-5%207-5%209l-1%202c-1-1-1%200-1%201h1l2%202c0%203%204%204%206%203s2-1%201-3c-2-1-2-1%200-1%202-1%202-1%202%201l1%201v-1l1-2c1%200%202-1%201-3l-1-2-3%202-2%201-1-2c-3%201-2-2%201-3%203-2%203-2%202-4%200-2-2-2-4-1m116%2040v5c0%204-2%205-4%202s-9-4-9-1v5c2%202%205%203%205%201s8-2%2010%200c1%202%204%201%204-2l-2-3c-2-2-2-4-2-7h-2M99%20277c0%201%200%202%201%201l1%201c-1%201%200%201%201%201l1%202-1%202-8%203%201-3c2-2%202-3%200-3l-2%202-3%206-2%203-1%201c-3%202-7%2010-5%2011%201%202%2013%201%2015%200%203-3%204-4%205-3v-1c-1-2%205-5%208-5%202%200%202%200%201-1-2-1-2-1%201-1l5%202c0%201%205-3%205-5s-3-6-4-5l-2-1c-2-1-1-2%201-1l1-2-1-3c0-2-10-3-11-1-1%201-4%200-4-1l-1-1-2%202m126%203v4h6c4%200%205%200%205-2h1c0%203%202%202%202%200l1-3v5h7l8-1c0-3-2-3-3-2h-3l-1%201c0-2-1-2-3-2-3%200-3%200-2-1%202-2%201-3-9-3h-9v4m-29%2019v6h11c12%201%2013%200%2012-1a219%20219%200%200044%201c2-2-1-3-5-3l-4-1c0-2%201-2%205-2%205%200%205%200%205-3v-3h-68v6m-24%209c-17%208-12%2032%206%2032%2019%200%2024-25%208-32-6-2-9-2-14%200'%20fill='%23d3d3d3'%20fill-rule='evenodd'/%3e%3c/svg%3e","aspectRatio":1.1549079754601228,"src":"//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg?w=800&q=80","srcSet":"//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg?w=200&h=173&q=80 200w,\n//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg?w=400&h=346&q=80 400w,\n//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg?w=800&h=693&q=80 800w,\n//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg?w=1200&h=1039&q=80 1200w,\n//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg?w=1506&h=1304&q=80 1506w","srcWebp":"//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg?w=800&q=80&fm=webp","srcSetWebp":"//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg?w=200&h=173&q=80&fm=webp 200w,\n//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg?w=400&h=346&q=80&fm=webp 400w,\n//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg?w=800&h=693&q=80&fm=webp 800w,\n//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg?w=1200&h=1039&q=80&fm=webp 1200w,\n//images.ctfassets.net/vkdbses00qqt/Dz58dBKd9TTfnQaIcau2F/3e26d5aaf08048573254ceec2c8b883f/home-page_hero-collage.jpg?w=1506&h=1304&q=80&fm=webp 1506w","sizes":"(max-width: 800px) 100vw, 800px"}}},{"__typename":"ContentfulCustomContent","id":"f9cc67cb-c018-5515-a359-7a28779fdd03","name":"PageHeader > CTA Open Source","contentful_id":"5xhlPrFSUEvd7PYGPmZgdl","spaceId":"vkdbses00qqt","component":"PageHeaderCtaPrimary","content":[{"__typename":"ContentfulHeading","id":"bc9aec92-845a-5c63-b7ba-4b621e46599a","name":"OSSTitle","contentful_id":"5s9C0VOfDFO7HE7rLdn74k","spaceId":"vkdbses00qqt","text":{"text":"Build"}},{"__typename":"ContentfulHeading","id":"b299fd85-792e-5465-a275-0c554233208e","name":"OSSSubtitle","contentful_id":"5F4iNtnSez3EeyJ5j84Rou","spaceId":"vkdbses00qqt","text":{"text":"Gatsby Open Source"}},{"__typename":"ContentfulCta","id":"8087ca56-0861-5104-a290-0004ea65d28a","name":"OSSLink","contentful_id":"7tsNOjVmAg81gbHYyMgmzj","spaceId":"vkdbses00qqt","anchorText":"Start the tutorial","href":"/docs","target":null},{"__typename":"ContentfulMarkdown","id":"a96b5940-54b1-5655-9f26-41c5e17945d0","name":"OSSContent","contentful_id":"3KS7aFB2CMx3mu0y6iljP1","spaceId":"vkdbses00qqt","body":{"childMarkdownRemark":{"html":"<p>Free, open source framework for building fast, powerful websites.</p>"}}}]},{"__typename":"ContentfulCustomContent","id":"bf271543-9340-56af-ae96-5ad7e744d5ac","name":"PageHeader > CTA Cloud","contentful_id":"1eFX8wsxtXULT2CiVm75FS","spaceId":"vkdbses00qqt","component":"PageHeaderCtaSky","content":[{"__typename":"ContentfulHeading","id":"9a861557-5c16-5381-8e66-88a62a37b40f","name":"CloudTitle","contentful_id":"4ROWQKMzhHATMji0ITsrHS","spaceId":"vkdbses00qqt","text":{"text":"Deploy"}},{"__typename":"ContentfulHeading","id":"730ece49-222d-5e86-baa5-422a066465bc","name":"CloudSubtitle","contentful_id":"2mm3d2VpL6c041zoNHqr0X","spaceId":"vkdbses00qqt","text":{"text":"Gatsby Cloud"}},{"__typename":"ContentfulMarkdown","id":"4e92db72-bbf8-563b-b926-a08b4eb4eb78","name":"CloudContent","contentful_id":"5xaLtr7RORc0mmddjWnNwn","spaceId":"vkdbses00qqt","body":{"childMarkdownRemark":{"html":"<p>The best place to build and host your Gatsby website or application.</p>"}}},{"__typename":"ContentfulCta","id":"fa364c7d-d69d-503f-8001-2eef54d055c1","name":"CloudLink","contentful_id":"71huVEB9GVO0ymhaywituP","spaceId":"vkdbses00qqt","anchorText":"Sign up for free","href":"/dashboard/signup","target":null}]},{"__typename":"ContentfulCustomContent","id":"f2848bb2-1744-56f9-a7c3-33272e3e766b","name":"PageHeader > CTA Concierge","contentful_id":"5XQGG9p7YONB`;

suite
  .add(`insert db transaction`, () => {
    const seed = Math.random().toString();
    db.transaction(() => {
      require(`lodash`)
        .range(100)
        .map((i) => {
          start += 1;
          const filePath = path.join(seed, `temp3`, i.toString());
          stmt.run({ id: start, path: filePath, blob: str });
        });
    })();
  })
  .add(`select where...in`, () => {
    const uid = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
    const sql = `SELECT * FROM test WHERE rowid IN (${uid
      .map(() => "?")
      .join(",")})`;
    const readWhereInStmt = db.prepare(sql);
    readWhereInStmt.all(uid);
  })
  .add(`select transaction`, () => {
    const uid = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
    const myTransaction = db.transaction((uid) => {
      return uid.map((x) => selectRowId.get(x));
    });
    const result = myTransaction(uid);
  })
  .on("cycle", function (event) {
    console.log(String(event.target));
  })
  .run();

[m4heshd]

@KyleAMathews Thank you. That's very useful information.

[Prinzhorn]

In case you need to use a lot of parameters you will run into SQLite limits. Here's a JSON workaround I use that also allows re-using the same statement because it uses a single bind parameter.

const uid = [1, 2, 3, .........., 12345679]; // A lot
const sql = `SELECT * FROM user WHERE uid IN (SELECT value FROM json_each(?))`;
const stmt = sqlite.prepare(sql);

// Use statement with arbitrary many uid in your array
const result = stmt.all(JSON.stringify(uid));

---

For absolute maximum pedal to the medal performance you could also use a custom vtable and avoid the serialization step entirely. Basically register your array somewhere (Map), then pass some sort of identifier (e.g. id++) to the custom function and yield the values directly from the array (and then free the array from Map). Basically passing the array out-of-band and then injecting it back using the vtable. But SQLite JSON is fast and we're talking edge cases here.

Scratch that and stick with json_each. I just remembered that the better-sqlite3 vtables are generic and not individually optimized. That means you will run into fun edge cases with the query planner, see https://stackoverflow.com/questions/69688791/running-into-some-sqlite-limitation-using-in-operator

[uwemaurer]

this json_each(?) is really nice!

for my usecase the results should be in the order as in the input array, this can be done like this:

SELECT user.* FROM user, json_each(?) as x WHERE user.uid = x.value order by x.key

this key field is the index in the array and can be used for the ordering