dallashoff/sqlocal

SQLocal makes it easy to run SQLite3 in the browser, backed by the origin private file system.

sqlocal's Issues

Feature request: ability to control instantiation of sqlite db

Wow, amazing work here--really high quality code and docs, and just what I'm looking for (sqlite-wasm + Kysely).

That said, I wish the API supported a way for me to pass in the sqlite DB instead of sqlocal creating it (i.e., the ability to override SQLocalProcessor.init). Would you be open to making a change along those lines, or considering a PR to that effect?

Some reasons this might be a nice feature:

• dev can control version of sqlite-wasm being used, including the possibility of packages other than the official @sqlite.org/sqlite-wasm distro (e.g., maybe one compiled with custom sqlite extensions, etc.)
• dev has better control over storage mechanism (e.g., could use new sqlite.oo1.JsStorageDb(), or their own logic for how to handle OPFS not being available, etc.)
  • this also allows dev to control flags passed in to OpfsDb constructor (e.g., use trace flag in dev mode); although this could also be exposed as a param similar to databasePath

Error during SvelteKit Server-side Rendering

I am using latest SvelteKit version (2.0.6), which uses bundler moduleResolution.
When i try to build or run vite dev on my project i am getting following error:

Cannot find module 'C:\sources\my-app\node_modules\sqlocal\dist\kysely\client' imported from C:\sources\my-app\node_modules\sqlocal\dist\kysely\index.js

I have created repository that reproduces issue when you run dev command. Unlike my actual project, build command seems to be working.
Any ideas how to solve it?

Error opening WAL db with overwriteDatabaseFile

Thanks for writing this library! it looks very clean.

When using overwriteDatabaseFile with a db that has WAL enabled I get the following error:
Error: SQLITE_CANTOPEN: sqlite3 result code 14: unable to open database file

Passing the byte array to the worker and using the sqlite3.oo1.OpfsDb.importDb('filename', byteArray);
function does allow it to be accessed.

Is this something that is open to being supported in the future?

Unable to parameterize table/column name in query

Have the following base query to drop table:

async drop () {
      return DB.sql`DROP TABLE IF EXISTS ${this.tableName}`;
};

which is creating following console error:

Error: sqlite3 result code 1: near "?": syntax error
    SQLite3Error http://127.0.0.1:5173/node_modules/@sqlite.org/sqlite-wasm/sqlite-wasm/jswasm/sqlite3-bundler-friendly.mjs?v=83ec1d7f:5714
    toss3 http://127.0.0.1:5173/node_modules/@sqlite.org/sqlite-wasm/sqlite-wasm/jswasm/sqlite3-bundler-friendly.mjs?v=83ec1d7f:10398
    checkSqlite3Rc http://127.0.0.1:5173/node_modules/@sqlite.org/sqlite-wasm/sqlite-wasm/jswasm/sqlite3-bundler-friendly.mjs?v=83ec1d7f:10417
    checkRc http://127.0.0.1:5173/node_modules/@sqlite.org/sqlite-wasm/sqlite-wasm/jswasm/sqlite3-bundler-friendly.mjs?v=83ec1d7f:10712
    exec http://127.0.0.1:5173/node_modules/@sqlite.org/sqlite-wasm/sqlite-wasm/jswasm/sqlite3-bundler-friendly.mjs?v=83ec1d7f:10842
    execQuery http://127.0.0.1:5173/node_modules/sqlocal/dist/worker.mjs?type=module&worker_file:47
    flushQueue http://127.0.0.1:5173/node_modules/sqlocal/dist/worker.mjs?type=module&worker_file:92
    init http://127.0.0.1:5173/node_modules/sqlocal/dist/worker.mjs?type=module&worker_file:124

checked the worker when db.exec is executed and confirmed the object is:

method: "all"
params: Array [ "Accounts" ]
queryKey: "snTm0m_khR34swnpiudaC"
sql: "DROP TABLE IF EXISTS ?"
type: "query"

Seems to be an issue with binding arguments to query. If I directly replace variable with text the query works fine.

Using nested literals to construct queries passed to SQLocal.sql

Not sure if appropriate question to post here but here goes.

Does anyone know if its possible to nest query template literals?
I cant seem to get it to work.
Example:

DB.sql<Transaction[]> `SELECT id, account_id, date, time, description, notes, amount, total, source, active, p_id, created_at, updated_at
        ${filter ? `WHERE description=${filter.description}` : ``}
        FROM Transactions`;

sqlite3 result code 10: disk I/O error

The browser throws the error below. Any ideas?

sqlite3-opfs-async-proxy.js?type=classic&worker_file:90 OPFS asyncer: xOpen DOMException: A requested file or directory could not be found at the time an operation was processed.
logImpl @ sqlite3-opfs-async-proxy.js?type=classic&worker_file:90
error @ sqlite3-opfs-async-proxy.js?type=classic&worker_file:94
xOpen @ sqlite3-opfs-async-proxy.js?type=classic&worker_file:645
await in xOpen (async)
f @ sqlite3-opfs-async-proxy.js?type=classic&worker_file:951
await in f (async)
globalThis.onmessage @ sqlite3-opfs-async-proxy.js?type=classic&worker_file:990
Show 5 more frames
Show less
sqlite3-bundler-friendly.mjs?v=6b26e52b:12388 OPFS syncer: xOpen() async error: NotFoundError: A requested file or directory could not be found at the time an operation was processed.
logImpl @ sqlite3-bundler-friendly.mjs?v=6b26e52b:12388
error @ sqlite3-bundler-friendly.mjs?v=6b26e52b:12392
opRun @ sqlite3-bundler-friendly.mjs?v=6b26e52b:12614
f @ sqlite3-bundler-friendly.mjs?v=6b26e52b:12958
$func507 @ sqlite3.wasm:0x24d5f
$func501 @ sqlite3.wasm:0x240d9
$sqlite3_open_v2 @ sqlite3.wasm:0x251ea
(anonymous) @ sqlite3-bundler-friendly.mjs?v=6b26e52b:7747
ctor @ sqlite3-bundler-friendly.mjs?v=6b26e52b:10589
OpfsDb @ sqlite3-bundler-friendly.mjs?v=6b26e52b:13174
value @ processor.js?v=6b26e52b:56
await in value (async)
value @ processor.js?v=6b26e52b:125
value @ processor.js?v=6b26e52b:91
self.onmessage @ worker.js?type=module&worker_file:4
Show 14 more frames
Show less
client.js?v=6b26e52b:56 Uncaught SQLite3Error: SQLITE_IOERR: sqlite3 result code 10: disk I/O error
at toss3 (sqlite3-bundler-friendly.mjs?v=6b26e52b:10492:17)
at checkSqlite3Rc (sqlite3-bundler-friendly.mjs?v=6b26e52b:10511:13)
at OpfsDb.ctor (sqlite3-bundler-friendly.mjs?v=6b26e52b:10591:13)
at new OpfsDb (sqlite3-bundler-friendly.mjs?v=6b26e52b:13174:45)
at SQLocalProcessor.value [as init] (processor.js?v=6b26e52b:56:35)
value @ client.js?v=6b26e52b:56
Show 1 more frame
Show less

IndexedDB as a persistence option?

Hi,
SQLocale uses sqlite-wasm which to the best of my knowledge only uses OPFS for persisting data.
There could be other options. For instance absurd-sql uses IndexedDB for storing data.
Since OPFS is not widely and fully supported yet. Would you consider adding the option to use IndexedDB?
How much work would it be to let sqlocale wrap absurd-sql as an option?'
Thank you again!

Feature: Transaction

Firstly, thanks sincerely for your contributions.

Q. Are you interested in extending transactions to support the existing SQLite standard?

I am looking for a drop-in replacement for WebSQL/SQLite.
This library creates it's own unique interfaces [I mitigate by wrapping SQLocal.exec()].

Attempting to intercept executeSql() to manage transactions is complicated as inferred here: sqlite/sqlite-wasm#42 (comment)

The standard WebSQL/SQLite transaction interface is elegant; simply obtain a transaction object and use that.

db.transaction(
	(tx) => {
		tx.executeSql('INSERT INTO myTable', null, (tx, results) => {
			tx.executeSql('SELECT FROM myTable', null, (tx, results) => {});
		});
	},
	(error) => {}
);

Cannot find module 'sqlocal/drizzle' or its corresponding type declarations.ts(2307)

maybe need to migrate https://github.com/egoist/tsup to better generate js mjs d.ts

Node support

There seems to be a origin-private file-system ponyfill, it'd be cool if it could be used to make this module isomorphic and work everywhere with good performance, with the same APIs, and without ever touching native node modules.

Replace Sqlite3 with Libsql

Libsql is a fork of SQlite3 and has more features like ALTER TABLE extension for modifying column types and constraints

I recommend replacing Sqlite3 with Liqsql https://github.com/tursodatabase/libsql

sqlocal easy connect/disconnect

I have a use case where I have to connect and disconnect on demand from databases.
Because of this I have created a wrapper to manage this, as a singleton.

I think it could be useful if the sqlocal client wouldn't actually connect to database on construction if fileName param was empty, and add the ability to connect on demand.

Any thoughts?

Database maximum size

Hello and thank you for this nice library.

I understand this is supposed to be run in the context of a web browser. With that in mind, how much data can the "DB" hold in practice. Meaning what is the maximum data size you effectively tested this library with while keeping responsive and practical.

Thank you again and best wishes!