FazBrowse GitHub Viewer | Trending |
URL:
| Home
Tools: [Download Repo ZIP]   [Original HTTPS Page]

sqlite: add tagged template · nodejs/node@9a3e58a · GitHub

/ node Public

Commit 9a3e58a

Browse files
0hmX
authored and
targos
committed
sqlite: add tagged template
This pr introduces the support for tagged templates And an LRU to cache the templates. We introduced a new object called SqlTagStore that holds the ref to Lru. This acts as the main object that allows us to use tagged templates. PR-URL: #58748 Reviewed-By: Matteo Collina <matteo.collina@gmail.com> Reviewed-By: Edy Silva <edigleyssonsilva@gmail.com>
1 parent 7aec53b commit 9a3e58a

7 files changed

Lines changed: 1065 additions & 116 deletions

File tree

‎doc/api/sqlite.md‎

Lines changed: 161 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -351,6 +351,53 @@ added: v22.5.0
351351
Compiles a SQL statement into a [prepared statement][]. This method is a wrapper
352352
around [`sqlite3_prepare_v2()`][].
353353

354+
### `database.createSQLTagStore([maxSize])`
355+
356+
<!-- YAML
357+
added: REPLACEME
358+
-->
359+
360+
* `maxSize` {integer} The maximum number of prepared statements to cache.
361+
**Default:** `1000`.
362+
* Returns: {SQLTagStore} A new SQL tag store for caching prepared statements.
363+
364+
Creates a new `SQLTagStore`, which is an LRU (Least Recently Used) cache for
365+
storing prepared statements. This allows for the efficient reuse of prepared
366+
statements by tagging them with a unique identifier.
367+
368+
When a tagged SQL literal is executed, the `SQLTagStore` checks if a prepared
369+
statement for that specific SQL string already exists in the cache. If it does,
370+
the cached statement is used. If not, a new prepared statement is created,
371+
executed, and then stored in the cache for future use. This mechanism helps to
372+
avoid the overhead of repeatedly parsing and preparing the same SQL statements.
373+
374+
```mjs
375+
import { DatabaseSync } from 'node:sqlite';
376+
377+
const db = new DatabaseSync(':memory:');
378+
const sql = db.createSQLTagStore();
379+
380+
db.exec('CREATE TABLE users (id INT, name TEXT)');
381+
382+
// Using the 'run' method to insert data.
383+
// The tagged literal is used to identify the prepared statement.
384+
sql.run`INSERT INTO users VALUES (1, 'Alice')`;
385+
sql.run`INSERT INTO users VALUES (2, 'Bob')`;
386+
387+
// Using the 'get' method to retrieve a single row.
388+
const id = 1;
389+
const user = sql.get`SELECT * FROM users WHERE id = ${id}`;
390+
console.log(user); // { id: 1, name: 'Alice' }
391+
392+
// Using the 'all' method to retrieve all rows.
393+
const allUsers = sql.all`SELECT * FROM users ORDER BY id`;
394+
console.log(allUsers);
395+
// [
396+
// { id: 1, name: 'Alice' },
397+
// { id: 2, name: 'Bob' }
398+
// ]
399+
```
400+
354401
### `database.createSession([options])`
355402

356403
<!-- YAML
@@ -494,6 +541,120 @@ times with different bound values. Parameters also offer protection against
494541
[SQL injection][] attacks. For these reasons, prepared statements are preferred
495542
over hand-crafted SQL strings when handling user input.
496543

544+
## Class: `SQLTagStore`
545+
546+
<!-- YAML
547+
added: REPLACEME
548+
-->
549+
550+
This class represents a single LRU (Least Recently Used) cache for storing
551+
prepared statements.
552+
553+
Instances of this class are created via the database.createSQLTagStore() method,
554+
not by using a constructor. The store caches prepared statements based on the
555+
provided SQL query string. When the same query is seen again, the store
556+
retrieves the cached statement and safely applies the new values through
557+
parameter binding, thereby preventing attacks like SQL injection.
558+
559+
The cache has a maxSize that defaults to 1000 statements, but a custom size can
560+
be provided (e.g., database.createSQLTagStore(100)). All APIs exposed by this
561+
class execute synchronously.
562+
563+
### `sqlTagStore.all(sqlTemplate[, ...values])`
564+
565+
<!-- YAML
566+
added: REPLACEME
567+
-->
568+
569+
* `sqlTemplate` {Template Literal} A template literal containing the SQL query.
570+
* `...values` {any} Values to be interpolated into the template literal.
571+
* Returns: {Array} An array of objects representing the rows returned by the query.
572+
573+
Executes the given SQL query and returns all resulting rows as an array of objects.
574+
575+
### `sqlTagStore.get(sqlTemplate[, ...values])`
576+
577+
<!-- YAML
578+
added: REPLACEME
579+
-->
580+
581+
* `sqlTemplate` {Template Literal} A template literal containing the SQL query.
582+
* `...values` {any} Values to be interpolated into the template literal.
583+
* Returns: {Object | undefined} An object representing the first row returned by
584+
the query, or `undefined` if no rows are returned.
585+
586+
Executes the given SQL query and returns the first resulting row as an object.
587+
588+
### `sqlTagStore.iterate(sqlTemplate[, ...values])`
589+
590+
<!-- YAML
591+
added: REPLACEME
592+
-->
593+
594+
* `sqlTemplate` {Template Literal} A template literal containing the SQL query.
595+
* `...values` {any} Values to be interpolated into the template literal.
596+
* Returns: {Iterator} An iterator that yields objects representing the rows returned by the query.
597+
598+
Executes the given SQL query and returns an iterator over the resulting rows.
599+
600+
### `sqlTagStore.run(sqlTemplate[, ...values])`
601+
602+
<!-- YAML
603+
added: REPLACEME
604+
-->
605+
606+
* `sqlTemplate` {Template Literal} A template literal containing the SQL query.
607+
* `...values` {any} Values to be interpolated into the template literal.
608+
* Returns: {Object} An object containing information about the execution, including `changes` and `lastInsertRowid`.
609+
610+
Executes the given SQL query, which is expected to not return any rows (e.g., INSERT, UPDATE, DELETE).
611+
612+
### `sqlTagStore.size()`
613+
614+
<!-- YAML
615+
added: REPLACEME
616+
-->
617+
618+
* Returns: {integer} The number of prepared statements currently in the cache.
619+
620+
A read-only property that returns the number of prepared statements currently in the cache.
621+
622+
### `sqlTagStore.capacity`
623+
624+
<!-- YAML
625+
added: REPLACEME
626+
-->
627+
628+
* Returns: {integer} The maximum number of prepared statements the cache can hold.
629+
630+
A read-only property that returns the maximum number of prepared statements the cache can hold.
631+
632+
### `sqlTagStore.db`
633+
634+
<!-- YAML
635+
added: REPLACEME
636+
-->
637+
638+
* {DatabaseSync} The `DatabaseSync` instance that created this `SQLTagStore`.
639+
640+
A read-only property that returns the `DatabaseSync` object associated with this `SQLTagStore`.
641+
642+
### `sqlTagStore.reset()`
643+
644+
<!-- YAML
645+
added: REPLACEME
646+
-->
647+
648+
Resets the LRU cache, clearing all stored prepared statements.
649+
650+
### `sqlTagStore.clear()`
651+
652+
<!-- YAML
653+
added: REPLACEME
654+
-->
655+
656+
An alias for `sqlTagStore.reset()`.
657+
497658
### `statement.all([namedParameters][, ...anonymousParameters])`
498659

499660
<!-- YAML

‎src/lru_cache-inl.h‎

Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,69 @@
1+
#ifndef SRC_LRU_CACHE_INL_H_
2+
#define SRC_LRU_CACHE_INL_H_
3+
4+
#include <list>
5+
#include <unordered_map>
6+
#include <utility>
7+
8+
template <typename key_t, typename value_t>
9+
class LRUCache {
10+
public:
11+
using key_value_pair_t = typename std::pair<key_t, value_t>;
12+
using iterator = typename std::list<key_value_pair_t>::iterator;
13+
using const_iterator = typename std::list<key_value_pair_t>::const_iterator;
14+
15+
const_iterator begin() const { return lru_list_.begin(); }
16+
const_iterator end() const { return lru_list_.end(); }
17+
18+
explicit LRUCache(size_t capacity) : capacity_(capacity) {}
19+
20+
void Put(const key_t& key, const value_t& value) {
21+
auto it = lookup_map_.find(key);
22+
if (it != lookup_map_.end()) {
23+
lru_list_.erase(it->second);
24+
lookup_map_.erase(it);
25+
}
26+
27+
lru_list_.push_front(std::make_pair(key, value));
28+
lookup_map_[key] = lru_list_.begin();
29+
30+
if (lookup_map_.size() > capacity_) {
31+
auto last = lru_list_.end();
32+
last--;
33+
lookup_map_.erase(last->first);
34+
lru_list_.pop_back();
35+
}
36+
}
37+
38+
value_t& Get(const key_t& key) {
39+
auto it = lookup_map_.find(key);
40+
lru_list_.splice(lru_list_.begin(), lru_list_, it->second);
41+
return it->second->second;
42+
}
43+
44+
void Erase(const key_t& key) {
45+
auto it = lookup_map_.find(key);
46+
if (it != lookup_map_.end()) {
47+
lru_list_.erase(it->second);
48+
lookup_map_.erase(it);
49+
}
50+
}
51+
52+
bool Exists(const key_t& key) const { return lookup_map_.count(key) > 0; }
53+
54+
size_t Size() const { return lookup_map_.size(); }
55+
56+
size_t Capacity() const { return capacity_; }
57+
58+
void Clear() {
59+
lru_list_.clear();
60+
lookup_map_.clear();
61+
}
62+
63+
private:
64+
std::list<key_value_pair_t> lru_list_;
65+
std::unordered_map<key_t, iterator> lookup_map_;
66+
size_t capacity_;
67+
};
68+
69+
#endif // SRC_LRU_CACHE_INL_H_

0 commit comments

Comments
 (0)
Back | FazBrowse Home | New Git URL