Binding ORDER BY

[amiralitaheri]

Currently this syntax doesn't work and doesn't cause any error.

const selectAllImageQueryAsc = db.prepare(
  "SELECT * From image ORDER BY @sortKey ASC;",
);

const result = selectAllImageQueryAsc.all({ sortKey: sort?.key || "id" });

1. Is there any workaround beside using template literals for creating db.prepare input.
2. How bad it is (for performance or in general) to run db.prepare for each query?

[JoshuaWise]

SQLite only allows binding parameters for data values. A table/column name is not a data value, so it doesn't work the way you expect. This isn't an issue with better-sqlite3, it's just how SQLite is designed.

The number of tables/columns in your database is theoretically static (i.e., it doesn't change rapidly while an application is running; it only changes at intentional, predictable moments). Therefore, the optimal solution is to create one prepared statement per column that you might want to order by, and then choose the correct prepared statement dynamically (without running db.prepare() for each query).

Alternatively, if you want to just run db.prepare() for each query, there's no way to know whether the performance is acceptable unless you measure it on your own machine with your actual query and real data.