Concurrent reads and writes

[carere]

Hello, first of all, thx for you wonderful lib, it's just amazing to use Drizzle ORM in my frontend 😅

I wonder the best way (most optimize way) to use your abstraction. On each SQLocal instanciation, you create a new worker with a connection to the SQLite database. Thus, I wonder if we can have concurrent connection ? And concurrent reads ?

I use a Shared Worker in order to "store" the connection, and use that worker in other web workers when I need to retrieve some data. Should I create new connection for every worker which needs to access the database ?

Here is what I plan to do:

// Hack to work with Shared Workers in Typescript
const _self: {
  onconnect: (this: SharedWorkerGlobalScope, ev: MessageEvent<any>) => any;
} = self as any;
// Hack end

import { drizzle } from "drizzle-orm/sqlite-proxy";
import { SQLocalDrizzle } from "sqlocal/drizzle";

const db = drizzle(new SQLocalDrizzle("ashiso.sqlite3").driver);

//TODO: Apply migrations if needed

_self.onconnect = function (e) {
  var port = e.ports[0];

  port.addEventListener("message", function (e) {
    //TODO: Depends on the message type, execute appropriate query against the database
  });

  port.start();
};

What are your thoughts about that ??

Best regards,

Carere

[carere]

Also, I tried to run the example code (without drizlle) from a shared worker, and it does not seems to work, but it works from the main thread.

[DallasHoff]

One of the goals of SQLocal is to abstract away the need for your app to deal with the web worker. SQLocal is designed to be used from the main thread, and it handles the workers for you. You won't really get any benefit from trying to use a shared worker. SQLite itself does not do concurrent writes anyway.

My recommendation is to just instantiate SQLocal from the main thread and enable WAL mode. Each instance will connect to the same database and be able to read from it concurrently, and with WAL mode enabled, writes will not block reads.

const { sql } = new SQLocalDrizzle("db.sqlite3");
await sql`PRAGMA journal_mode = WAL;`;

[carere]

@DallasHoff Thx for your answer,

For more context, I'm buildling a trading app, and I store historical stock price and market analysis in Sqlite.

I use an "analyzer" worker to analyze the market in order to avoid blocking the main thread with big processing.

I was planning on creating a "db-access" worker which instanciate SQLite through SQLocal, do migrations and handle requests from other context through channel, in order to provide some data from SQLite.

The main thread, and the "analyzer" worker needs the data from SQLite respectively to display it and analyze it. Other workers ("trader", "strategist", ...) would also needs the data.

Does it makes sense to you ?

Best regards,

Carere

[DallasHoff]

Yeah, that makes sense. What problem are you running into?

[carere]

I have no problem, I was just wondering about the best way to organize my front-end logic with workers. Thx for this little discussion, I see now how to implement what I need 🙂

[DallasHoff]

Sure thing!