Node.js sqlite3 driver

[wojta]

Description

I was trying to find a way to have direct filesystem-based SQLite with Node.js, not only in-memory. Not sure if someone else already did something like this.

If not I think about writing my own driver for sqldelight on top of sqlite3 npm package (https://github.com/TryGhost/node-sqlite3). Is there some tutorial or documentation regarding that or can I take only inspiration from existing drivers?

[dellisd]

I don't think there's any detailed documentation about it, but the main requirement is implementing the SqlDriver interface, along with implementing SqlCursor and Transacter.Transaction to let SQLDelight read the result of queries and handle transactions.

You can probably reference the sqljs-driver implementation to see how some of these are implemented as I imagine they'll be somewhat similar.

I'll also note that since the node-sqlite3 API is asynchronous, you'll want to use AsyncValue whenever you have to return a QueryResult in your driver implementation, and then construct a coroutine to handle the asynchronous API call. Something similar is done in the JsWorkerSqlDriver implementation, which also uses an asynchronous API.

[wojta]

Thanks for the swift response. That'll do! 😊

[wojta]

I'm working on that implementation. It is mostly usable. It's not yet on the public Maven repo.

Only obstacles are that SqlCursor is not ready for being asynchronous so I can't read rows as moving the cursor, but I can only read it all into memory. The second issue was with method Query.executeAsList() that also doesn't support asynchronous mode so I had to write my own extension method Query.executeSuspendingAsList() that suspends.

https://github.com/wojta/sqldelight-node-sqlite3-driver

[dellisd]

I ran into a similar issue when building the R2DBC driver which has a similar asynchronous cursor mechanism.

I have an idea that could address this and I'm testing out the changes now. It would basically allow you to await the next() call on the SqlCursor method. This is what I came up with for the R2DBC cursor (I haven't tested this at all yet, and it's a mess):

class R2dbcCursor(private val result: Result) : SqlCursor {
  lateinit var currentRow: Row
    private set

  private var job: Job? = null
  private val rowChannel = Channel<Row>(capacity = 1)

  override fun next(): QueryResult.AsyncValue<Boolean> = QueryResult.AsyncValue {
    coroutineScope {
      if (job == null) {
        job = async {
          result.map { row, _ -> row }.asFlow().collect {
            rowChannel.send(it)
          }
        }
      }
    }

    val result = rowChannel.tryReceive()
    if (result.isSuccess) {
      currentRow = result.getOrThrow()
      return@AsyncValue true
    } else {
      return@AsyncValue false
    }
  }
}

[wojta]

Do you have a different SqlCursor? Mine returns Boolean and in master branch as well. I suppose that I can do the same trick as I did with that extension method and write my own extension nextSuspending() for SqlCursor but that would violate core OOP principles :slightly_smiling_face: . But only as a workaround for moment...

[hfhbd]

Yeah, I also think we should revisit the SqlCursor. Even the name is wrong, it's not a cursor with lazy fetch support but a wrapper over a result set. Not having a real cursor is annoying with async drivers.

[dellisd]

Do you have a different SqlCursor? Mine returns Boolean and in master branch as well.

Yeah, I'm testing the change locally. Making the next() method return a QueryResult would allow it to suspend if needed.

[wojta]

Maybe just have an interface with suspending functions (methods). Or is it there any specific reason to have there AsyncValue intermediate step? Is it there because of some multiplatform reasons, like TypeScript / Swift compatibility?

[dellisd]

@wojta FYI #4102 was merged in which will allow you to suspend on the next() call on the Cursor.

[wojta]

@dellisd thank you, I'll try it.

[hfhbd]

Can we close this issue too? We officially mention this/your driver in our docs starting with 2.0.0. If you have any other questions/problems, feel free to ask or create an issue.

And thank you for providing the nodeJS driver! 🎉