BA
Better Auth•3mo ago
andreasb

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; but instead SELECT TOP 10 * FROM Users;
2) Returning values from non-SELECT queries does not use RETURNING but instead OUTPUT. Examples: Postgres: 
INSERT INTO Users (Name, Email) 
VALUES ('John Doe', '[email protected]') 
RETURNING id, created_at
INSERT INTO Users (Name, Email) 
VALUES ('John Doe', '[email protected]') 
RETURNING id, created_at
MSSQL: 
INSERT INTO Users (Name, Email) 
OUTPUT INSERTED.id, INSERTED.created_at
VALUES ('John Doe', '[email protected]')
INSERT INTO Users (Name, Email) 
OUTPUT INSERTED.id, INSERTED.created_at
VALUES ('John Doe', '[email protected]')
In the Kysely adapter there's a 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() with .outputAll('inserted')? 3) Perhaps as a result of the issues raised in (2), I'm having issues with the 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 Cookie value: undefined.1TfYi...... Notice the 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 is 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 vs 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
7 Replies
andreasb
andreasbOP•3mo ago
Message became too long, just wanted to append a thank you for working on Better-Auth and improving the state of open source auth management! @bekacru Not wanting to be "that guy" tagging the maintainer, but wondering if either (1) there's possibility to financially support the development of mssql support, (2) if mssql actually should work and I'm doing something wrong, or (3) if mssql is not supported and the reference to it should be removed from the docs to avoid confusion (search "mssql" here: https://www.better-auth.com/docs/concepts/database)
bekacru
bekacru•3mo ago
Hey sorry for the late response. Will take a look hopefully today. We don't have direct test for MSSQL yet so it could defintely be from our side.
andreasb
andreasbOP•3mo ago
Do tell if there's anything I can help out with in this regard. Much appreciated!
andreasb
andreasbOP•3mo ago
Oooh this makes me happy to see! 🤩
No description
andreasb
andreasbOP•3mo ago
@bekacru Thanks for your work thus far on this issue! I've made an attempt at improving withReturning after I've encountered some issues with logging in. Adding an explicit check for mssql in withReturning seems to do the trick, something like: 
if (config?.type === "mssql") {
    res = await builder.outputAll('inserted').executeTakeFirst();
} else if (config?.type !== "mysql") {
    res = await builder.returningAll().executeTakeFirst();
} else {
....
if (config?.type === "mssql") {
    res = await builder.outputAll('inserted').executeTakeFirst();
} else if (config?.type !== "mysql") {
    res = await builder.returningAll().executeTakeFirst();
} else {
....
bekacru
bekacru•3mo ago
oh. will check it out.
andreasb
andreasbOP•3mo ago
Wrote a note here after a quick debug/investigation. I wrote the comment above afterwards, so I did test .outputAll('inserted') locally https://github.com/better-auth/better-auth/pull/1255#issuecomment-2607248675
GitHub
feat: support mssql directly by Bekacru · Pull Request #1255 · bett...
fix mssql related errors to fully support mssql databases directly through kysley

Did you find this page helpful?