Support for MSSQL (SQL Server) via Kysely

I'm attempting to use Better Auth in my SvelteKit project, connected via Kysely to MSSQL (Azure SQL Server) .
While some parts seem to work, I believe other parts of the Better-Auth internals rely on non-MSSQL compliant code.

Few things I've noticed so far:

1) Limiting the result set of a SQL query does not use for instance

SELECT * FROM Users LIMIT 10;

SELECT * FROM Users LIMIT 10;

SELECT * FROM Users LIMIT 10;

SELECT * FROM Users LIMIT 10; but instead

SELECT TOP 10 * FROM Users;

SELECT TOP 10 * FROM Users;

SELECT TOP 10 * FROM Users;

SELECT TOP 10 * FROM Users;

2) Returning values from non-

SELECT

SELECT

SELECT

SELECT queries does not use

RETURNING

RETURNING

RETURNING

RETURNING but instead

OUTPUT

OUTPUT

OUTPUT

OUTPUT. Examples:
Postgres:

INSERT INTO Users (Name, Email) 
VALUES ('John Doe', 'john@example.com') 
RETURNING id, created_at

INSERT INTO Users (Name, Email) 
VALUES ('John Doe', 'john@example.com') 
RETURNING id, created_at

INSERT INTO Users (Name, Email) 
VALUES ('John Doe', 'john@example.com') 
RETURNING id, created_at

INSERT INTO Users (Name, Email) 
VALUES ('John Doe', 'john@example.com') 
RETURNING id, created_at

MSSQL:

INSERT INTO Users (Name, Email) 
OUTPUT INSERTED.id, INSERTED.created_at
VALUES ('John Doe', 'john@example.com')

INSERT INTO Users (Name, Email) 
OUTPUT INSERTED.id, INSERTED.created_at
VALUES ('John Doe', 'john@example.com')

INSERT INTO Users (Name, Email) 
OUTPUT INSERTED.id, INSERTED.created_at
VALUES ('John Doe', 'john@example.com')

INSERT INTO Users (Name, Email) 
OUTPUT INSERTED.id, INSERTED.created_at
VALUES ('John Doe', 'john@example.com')

In the Kysely adapter there's a

withReturning

withReturning

withReturning

withReturning function which specifies query building for specifically mysql and non-mysql queries [1]. Could this be a good place to replace the use of

.returningAll()

.returningAll()

.returningAll()

.returningAll() with

.outputAll('inserted')

.outputAll('inserted')

.outputAll('inserted')

.outputAll('inserted')?

3) Perhaps as a result of the issues raised in (2), I'm having issues with the

signInEmail

signInEmail

signInEmail

signInEmail both on the auth API and on the client-side auth client. It creates the Session entry in the database and sets the cookie, but the value is incomplete.
Cookie name:

better-auth.session_token

better-auth.session_token

better-auth.session_token

better-auth.session_token
Cookie value:

undefined.1TfYi......

undefined.1TfYi......

undefined.1TfYi......

undefined.1TfYi......
Notice the

undefined

undefined

undefined

undefined rather than containing the token(?). Am I mistaken if I believe that the cause can be seen in [2], where the

session.session.token

session.session.token

session.session.token

session.session.token is

undefined

undefined

undefined

undefined perhaps due to the lack of a returning value from the database INSERT statement, due to the difference between Postgres and MSSQL in handling

RETURNING

RETURNING

RETURNING

RETURNING vs

OUTPUT

OUTPUT

OUTPUT

OUTPUT?

__
[1] https://github.com/better-auth/better-auth/blob/d2ce9da9b20ebb37444efea95c0d9bcf1d825119/packages/better-auth/src/adapters/kysely-adapter/kysely-adapter.ts#L200
[2] https://github.com/better-auth/better-auth/blob/f356bac8ea2d69836754725e08ef459a4d1573a1/packages/better-auth/src/cookies/index.ts#L150

Support for MSSQL (SQL Server) via Kysely

Similar Threads

Similar Threads

Similar Threads