Drizzle & PowerSync: SELECT Queries Blocking?

Hey everyone, I've got a bit of a head-scratcher and could really use your expertise. I'm knee-deep in a React Native app with PowerSync and Drizzle, and I've hit a performance snag. My app is experiencing some serious slowdowns, like 5-10 second freezes, and after some digging, I think I've found the culprit: Drizzle queries appear to be using writeLock() even for SELECT statements. This is causing a bottleneck, and I'm hoping you can shed some light on whether this is expected behavior and, if not, how to fix it. Let's dive in and see if we can get to the bottom of this!

The Problem: Performance Bottlenecks

Performance is key, especially in mobile apps. A sluggish app leads to a poor user experience, which can be a real killer. The issue I'm seeing is that database queries are blocking each other, leading to those frustrating freezes. When a user taps a button or interacts with the app, they expect an immediate response. When there's a delay, the app feels unresponsive and ultimately it becomes unusable.

The Investigation Begins

To figure out what's going on, I started tracing the execution paths of my queries. I wanted to understand exactly how each query was being handled by PowerSync and Drizzle. I was using different query types to see what each process was doing and to identify the issue. This involved a lot of logging, and some careful source code inspection.

The Culprit: writeLock() for Everything!

What I found was pretty revealing, and maybe not what I was expecting. It turns out that all my Drizzle queries, regardless of whether they were SELECT, INSERT, UPDATE, or DELETE are routing through executeRaw() and then, crucially, through writeLock(). This is where the red flag went up. Raw PowerSync queries, on the other hand, use readLock(), which seems more appropriate for SELECT operations.

The Queries

Here's a breakdown of what I observed:

• Drizzle Queries (all using writeLock):
  • db.query.findMany() → executeRaw() → writeLock
  • db.query.findFirst() → executeRaw() → writeLock
  • db.select().from() → executeRaw() → writeLock
  • db.select({ cols }).from() → executeRaw() → writeLock
• Raw PowerSync Queries (using readLock):
  • powerSyncDb.getAll() → getAll() → readLock
  • powerSyncDb.get() → get() → readLock

As you can see, the raw PowerSync queries behave the way I'd expect. However, the Drizzle queries were all using the writeLock. This could explain the performance issues, as a writeLock will block other operations, including reads.

Deep Dive into the Code: The Execution Path

To understand why this is happening, I had to trace the code path. Let's walk through it together and see what's going on, and if it makes sense.

The PowerSyncSQLitePreparedQuery Class

The journey starts in the PowerSyncSQLitePreparedQuery class. This is where the query gets prepared before execution. Here's what's happening:

  PowerSyncSQLitePreparedQuery:
  async values(placeholderValues) {
      const params = fillPlaceholders(this.query.params, placeholderValues ?? {});
      this.logger.logQuery(this.query.sql, params);
      return await this.db.executeRaw(this.query.sql, params);
  }

The values() method is the core. It takes placeholder values, logs the query (which is super helpful for debugging), and then calls this.db.executeRaw().

OPSqliteAdapter and executeRaw()

The executeRaw() method is where the writeLock() comes into play. In the OPSqliteAdapter, it looks like this:

  executeRaw(query, params) {
      return this.writeLock(ctx => ctx.executeRaw(query, params));
  }

This is the critical part. Regardless of the query type, executeRaw() wraps the query execution in a writeLock(). This means that even SELECT queries are being treated as if they could potentially modify the database.

The Million-Dollar Questions: Is This Expected? What Can I Do?

Now, here are the big questions that are bothering me, and maybe you can help answer them:

1. Is writeLock() the Standard for executeRaw()?

Is it normal for executeRaw() to use writeLock() even for SELECT queries? I'm not sure if this is the intended behavior. If it is the standard approach, then I might need to rethink my entire architecture and find a way to minimize the impact of the write locks. This could involve batching queries, optimizing the data fetching, and more.

2. Is There a Different Route for SELECT Queries?

Is there a different code path or method that I should be using for SELECT queries with Drizzle? Ideally, there would be a way to tell PowerSync that a query is a read-only operation so it could use a readLock() instead. Could I be missing something in the Drizzle configuration that would help me with this?

3. Should .values() Act Differently for SELECT Queries?

Should the .values() method in PowerSyncSQLitePreparedQuery be behaving differently for SELECT queries? Should it be calling a different underlying method, or should the executeRaw call be conditional on the query type? It could be possible to determine whether a query is a SELECT statement and use a different approach accordingly.

My Environment

Here are the details of my setup to help you understand the context. This helps identify the scope of the problem.

• @powersync/drizzle-driver: ^0.6.0
• @powersync/op-sqlite: ^0.7.11
• @powersync/react-native: ^1.25.1
• @powersync/common: ^1.40.0
• drizzle-orm: ^0.39.3
• Platform: React Native (Expo)
• SQLite: WAL mode enabled

Closing Thoughts: Seeking Guidance

I'm hoping to get some clarity on the expected behavior and any potential solutions. My goal is to optimize my React Native app. I'm keen on leveraging PowerSync and Drizzle together, as it's been a great experience otherwise! Any insights you could provide would be a massive help. If you'd like, I can provide test code that traces the exact execution paths, or any other information that might be helpful. Thanks in advance for your time and expertise!