Removing adapters for query builders (Kysely and Drizzle)

[pilcrowonpaper]

Hi everyone! We had a pretty successful 1.0 launch (so many stars, upvotes, and likes!) and the community (specifically Discord) has never been so active!

The issue

We've had requests to support more databases and ORMs, specifically Drizzle, D1 from CloudFlare, and PlanetScale. I've started development for new adapters and @rogadev has been kind enough to create a PR #454 for Drizzle!

But one of them is not like the other, and that's Drizzle. Alongside Kysely, those are SQL builders. These rely on drivers to do the actual connection and querying. better-sqlite3 for SQLite, pg for PostgreSQL, mysql2 for MySQL, etc. It doesn't really make sense to create adapters for query builders since you still have to change the behavior depending on the driver you're using. Some issues include:

1. Transactions - D1 for example doesn't support transactions, but they do support batches, which is sufficient. Unfortunately, SQLite (the language D1 uses), and as such Drizzle, doesn't support batches, only transactions.

2. Errors - Lucia expects adapters to handle some errors but the error types depend on the driver, not the query builder.

3. Column types - Again it's SQLite, but SQLite doesn't support boolean types, only numbers

And since different query builders can support the same drivers, it's just increasing the work load for no particular reason.

Going forward

First, Prisma and Mongoose adapters aren't going away.

We'll deprecate @lucia-auth/adapter-kysely and the planned @lucia-auth/adapter-drizzle, and instead create adapters for each query language:

• @lucia-auth/adapter-postgres
• @lucia-auth/adapter-mysql
• @lucia-auth/adapter-sqlite
• @lucia-auth/adapter-mongo (maybe?)

These adapters will provide adapters for different drivers. Taking @lucia-auth/adapter-sqlite as an example, it would provide adapters for:

• better-sqlite3
• Cloudflare D1 driver

I'm grouping these into SQL languages since they can share some query statements between them and creating an adapter for each individual driver is going to increase the adapter count substantially. Kysely or other query builders might be used under the hood, but I'd rather not deal with dependencies (especially when you have multiple drivers).

PS: I'm super sorry to @rogadev who has spent so much time working on the Drizzle adapter - this idea came up while reviewing their PR

PR: #529

[m4rvr]

An adapter for each language sounds nice. Currently, I'm using a custom postgres adapter with postgres.js (https://github.com/porsager/postgres), but would love to have something official.

[saturnonearth]

This sounds like a better plan. If possible, example schema's in the documentation for Drizzle (since it allows you to build the Schema) would be great. Although maybe it's too much effort since the example would need to include examples with postgresql, and mysql.

[pilcrowonpaper]

That’s the plan (for now - might change)!

[rgodha24]

Honestly this sounds like a much better plan, especially because query builders (or at least drizzle) have weird caveats with generated SQL not being correct.

[saturnonearth]

Honestly this sounds like a much better plan, especially because query builders (or at least drizzle) have weird caveats with generated SQL not being correct.

What do you mean in "generated SQL not being correct"?

[rgodha24]

there were some weird issues with mysql and foreign keys not being forced to be unique

[saturnonearth]

Interesting. Were you testing on PlanetScale? They do not support foreign keys.
https://planetscale.com/docs/learn/operating-without-foreign-key-constraints

If so, could be that? They are definitely still heavily development though so they got a lot of bugs.

[rgodha24]

I was running mysql through docker, but I noticed that the migrations generated by drizzle orm were different than what the kysely lucia docs said