From Discord: @effect/sql with Cloudflare D1

[effect-bot]

Summary

The conversation revolves around an attempt to use @effect/sql with Cloudflare's D1 (SQLite) database. Here are the key points and takeaways:

1. Initial Inquiry: The user, phischer, inquired about the compatibility of @effect/sql with Cloudflare D1, wondering if it's possible to use them together given the current clients.

2. Assumption: Another user, datner_, suggested that if @effect/sql works with better-sqlite3, it should theoretically work with Cloudflare D1, as there might not be a significant difference in how they operate at a basic level.

3. Implementation Attempt: Phischer managed to get @effect/sql working with Cloudflare D1 by modifying the client from sql-sqlite-node to work with D1Database. However, this solution required multiple @ts-ignore annotations to compile, indicating potential type mismatches or unsupported operations. Due to these uncertainties and the makeshift nature of the solution, phischer expressed a lack of trust in the stability and reliability of this approach and decided to use an alternative (drizzle) for the time being.

4. Code Example: Phischer shared a code snippet demonstrating how they adapted @effect/sql for use with Cloudflare D1. The code involves creating a SQL client that interacts with the D1 database, executing a SQL query, and handling the results.

5. Challenges: The main challenges highlighted include:

   • The need to adapt the client to work with a database object (D1Database) instead of a filename, which is what the original @effect/sql-sqlite-node client expects.
   • Handling the asynchronous nature of D1's query results, which differ from the synchronous "try" pattern used in the existing client.
   • The necessity to bypass TypeScript's type checking with @ts-ignore to make the code compile, indicating potential type safety issues.

6. Conclusion: While it is technically possible to use @effect/sql with Cloudflare D1 by adapting existing clients, the process involves significant modifications and workarounds that may compromise type safety and reliability. As such, users might consider looking for more straightforward or officially supported solutions for their database needs on Cloudflare Workers.

Discord thread

https://discord.com/channels/795981131316985866/1236321129941958726