[Bug]: LibSQL adapter update

[jasperkelder]

Package

lucia

Description

Currently the LibSQL adapter provided by @lucia-auth/adapter-sqlite causes builds on Vercel to crash when using recent @libsql/client versions. This happens when using ^0.4.0 or ^0.5.0, though when developing locally this didn't seem to be an issue.

I think there's also a bit of unclarity whether @lucia-auth/adapter-sqlite is suitable to use when using Drizzle with Turso's LibSQL. Lucia's Drizzle adapter currently doesn't support LibSQL, and although I've had succes in making @lucia-auth/adapter-sqlite work with Drizzle/Turso I've experienced some sporadic errors with it.

I'm not sure yet if they're related to the adapter or something else, but the errors in question are FetchErrors where a request to the database fails due to a socket hang up (ECONNRESET). Either way I think it'd be good to know for Lucia users whether LibSQL would need a specific Drizzle adapter or not.

[pilcrowOnPaper]

This should probably be a bug report?

[pilcrowOnPaper]

Have you tried using the SQLite Drizzle adapter provided by @lucia-auth/adapter-drizzle?

[jasperkelder]

Only rudimentary, I just now gave it a more thorough go. When making the switch I'm importing the DrizzleSQLiteAdapter from it, and I'm changing the old LibSQL setup (https://lucia-auth.com/database/sqlite):

const adapter = new LibSQLAdapter(db, { user: "user", session: "session"});

Into the DrizzleSQLite setup (https://lucia-auth.com/database/drizzle):

const adapter = new DrizzleSQLiteAdapter(db, sessionTable, userTable);

Something to be noted is that in this code I actually need to pass the libSQLClient to the adapter in the LibSQL setup instead of the db. For the DrizzleSQLite setup I'm switching to the actual db to make it work. For reference this is how both are configured:

export const libSQLClient = createClient({
    url: env.DATABASE_URL,
    authToken: env.DATABASE_AUTH_TOKEN
});

export const db = drizzle(libSQLClient, { schema });

After doing so I'm encountering a sessionTable type mismatch for the expiresAt column. The dataType is expected to be "number" but is interpreted as "date". I'm able to resolve this for now by removing the { mode: 'timestamp' } configuration from my Drizzle sessions table:

export const sessions = sqliteTable('sessions', {
    id: text('id').primaryKey(),
    userId: text('user_id')
        .references(() => users.id, { onDelete: 'cascade' })
        .notNull(),
    expiresAt: integer('expires_at', {
        mode: 'timestamp'
    }).notNull()
});

After doing so the DrizzleSQLiteAdapter does seem to work with Turso and I'm able to deploy the app with the latest @libsql/client version. As of yet I haven't encountered an error as I did earlier, but I'll need to do more testing to be sure as they didn't always pop-up earlier either.

Judging by this quick implementation the sessionTable dataType mismatch might be a bug in the DrizzleSQLiteAdapter, though I think this might be an issue regardless of whether LibSQL or SQLite is used. I'm guessing the adapter assumes regular SQLite functionality, whereas Drizzle offers a "date" type layered on top of it. We can work around this, but it could be confusing for people who aren't aware of it.

I'll report back when I know more. Thanks for looking into this and also in general for your open source contributions. I'm definitely looking forward for some Copenhagen reading.

[pilcrowOnPaper]

Gonna close this for now, but feel free to open another issue if you find any 👍