I wanted to make a working proof of concept that you can login with an oAuth provider directly in the database.
To be clear:
Surreal Database should have this built in, and I am hoping they will in the future just like Gel, Firebase and Supabase!
Thanks to Zorimyll for pseudocode in Discord.
TL;DR
I make a fully working SurrealDB SvelteKit login with GitHub button that automatically merges with existing users and their email! See Repo.
Pre-Config
We must first get rid of the username concept from this first post, and use email. This means refactoring all username references to email. This includes using valibot v.email() and type="email" in the inputs. For sake of time, I'm not going to do that here, but you can view the source code below to see the changes.
- If you want to add the username capability, it should be optional in this scenario.
- Forgot Password is a feature this needs, but we would need to setup an email provider for that (maybe in a later post).
- I also didn't add the
emailConfirmedfield, which may be useful.
Schema
-- =====================================
-- USERS TABLE
-- =====================================
DEFINE TABLE users SCHEMAFULL
PERMISSIONS
FOR select WHERE $auth.id = id OR $auth.role = 'admin'
FOR create, update, delete WHERE $auth.role = 'admin' OR $auth.scope = 'auth';
DEFINE FIELD email
ON TABLE users
TYPE string
ASSERT $value != '' AND string::is::email($value)
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE FIELD password
ON TABLE users
TYPE string
ASSERT $value != ''
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE FIELD name
ON TABLE users
TYPE option<string>
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE FIELD avatar_url
ON TABLE users
TYPE option<string>
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE FIELD OVERWRITE created_at
ON TABLE users
TYPE datetime
DEFAULT time::now()
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE FIELD OVERWRITE updated_at
ON TABLE users
TYPE datetime
VALUE time::now()
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE INDEX users_email_unique
ON TABLE users
COLUMNS email
UNIQUE;
DEFINE ACCESS OVERWRITE user
ON DATABASE
TYPE RECORD
SIGNUP (
CREATE users CONTENT {
email: string::lowercase($email),
password: crypto::argon2::generate($password)
} RETURN AFTER
)
SIGNIN (
SELECT * FROM users
WHERE email = string::lowercase($email)
AND crypto::argon2::compare(password, $password)
LIMIT 1
)
DURATION
FOR TOKEN 1h,
FOR SESSION NONE;
-- =====================================
-- AUTH_ACCOUNTS TABLE
-- (per provider login -> user)
-- =====================================
DEFINE TABLE auth_accounts SCHEMAFULL
PERMISSIONS
FOR select WHERE $auth.role = 'admin' OR $auth.scope = 'auth'
FOR create, update, delete WHERE $auth.role = 'admin' OR $auth.scope = 'auth';
DEFINE FIELD user
ON TABLE auth_accounts
TYPE record<users>
ASSERT $value != NONE
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE FIELD provider
ON TABLE auth_accounts
TYPE string
ASSERT $value != ''
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE FIELD provider_id
ON TABLE auth_accounts
TYPE string
ASSERT $value != ''
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE FIELD created_at
ON TABLE auth_accounts
TYPE datetime
DEFAULT time::now()
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE INDEX auth_accounts_provider_unique
ON TABLE auth_accounts
COLUMNS provider, provider_id
UNIQUE;
DEFINE INDEX auth_accounts_user_idx
ON TABLE auth_accounts
COLUMNS user;
-- =====================================
-- AUTH_PROVIDERS TABLE
-- (stores client/secret for GitHub etc.)
-- =====================================
DEFINE TABLE auth_providers SCHEMAFULL
PERMISSIONS
FOR select WHERE $auth.role = 'admin' OR $auth.scope = 'auth'
FOR create, update, delete WHERE $auth.role = 'admin';
DEFINE FIELD provider
ON TABLE auth_providers
TYPE string
ASSERT $value != ''
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE FIELD client_id
ON TABLE auth_providers
TYPE string
ASSERT $value != ''
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE FIELD client_secret
ON TABLE auth_providers
TYPE string
ASSERT $value != ''
PERMISSIONS
FOR select, create, update, delete FULL;
DEFINE INDEX auth_providers_provider_unique
ON TABLE auth_providers
COLUMNS provider
UNIQUE;
DEFINE FUNCTION OVERWRITE fn::github_login(
$code: string
) {
------------------------------------------------------------
-- 0) Load GitHub OAuth config from auth_providers
------------------------------------------------------------
LET $provider = (
SELECT client_id, client_secret
FROM auth_providers
WHERE provider = "github"
LIMIT 1
)[0];
IF $provider = NONE {
THROW {
stage: "missing_github_config",
message: "No auth_providers row found for provider 'github'"
};
};
IF $provider.client_id = NONE OR $provider.client_secret = NONE {
THROW {
stage: "invalid_github_config",
message: "auth_providers.github is missing client_id or client_secret",
provider: $provider
};
};
LET $github_client_id = $provider.client_id;
LET $github_secret_id = $provider.client_secret;
------------------------------------------------------------
-- 1) Exchange code for access token
------------------------------------------------------------
LET $token_res = http::post(
"https://github.com/login/oauth/access_token",
{
"client_id": $github_client_id,
"client_secret": $github_secret_id,
"code": $code
},
{
"Accept": "application/vnd.github+json"
}
);
IF $token_res = NONE {
THROW {
stage: "token_request_failed",
message: "GitHub /access_token returned NONE",
token_res: $token_res
};
};
LET $access_token = $token_res.access_token;
IF $access_token = NONE {
THROW {
stage: "missing_access_token",
message: "GitHub did not return access_token",
token_res: $token_res
};
};
------------------------------------------------------------
-- 2) Fetch GitHub user profile
------------------------------------------------------------
LET $headers = {
"Accept": "application/vnd.github+json",
"Authorization": string::concat("Bearer ", $access_token)
};
LET $u = http::get("https://api.github.com/user", $headers);
IF $u = NONE {
THROW {
stage: "user_fetch_failed",
message: "GitHub /user returned NONE",
token_res: $token_res
};
};
IF $u.id = NONE {
THROW {
stage: "missing_github_id",
message: "GitHub user has no id",
user: $u
};
};
------------------------------------------------------------
-- 3) Require email
------------------------------------------------------------
IF $u.email = NONE {
THROW {
stage: "missing_email",
message: "GitHub account has no public email associated",
user: $u
};
};
LET $email = string::lowercase($u.email);
LET $github_id = type::string($u.id);
------------------------------------------------------------
-- 4) If GitHub account already linked → load & return user
------------------------------------------------------------
LET $existing_account = (
SELECT *
FROM auth_accounts
WHERE provider = "github"
AND provider_id = $github_id
LIMIT 1
)[0];
IF $existing_account != NONE {
IF $existing_account.user = NONE {
THROW {
stage: "linked_account_missing_user_ref",
message: "auth_account exists but has no user field",
account: $existing_account
};
};
LET $user_from_account = (
SELECT *
FROM $existing_account.user
LIMIT 1
)[0];
IF $user_from_account = NONE {
THROW {
stage: "linked_account_missing_user",
message: "auth_account exists but user record not found",
account: $existing_account
};
};
RETURN $user_from_account;
};
------------------------------------------------------------
-- 5) No auth_account yet → try to find user by email
------------------------------------------------------------
LET $existing_user = (
SELECT *
FROM users
WHERE email = $email
LIMIT 1
)[0];
IF $existing_user != NONE {
LET $linked_auth_account = (
CREATE auth_accounts CONTENT {
user: $existing_user.id,
provider: "github",
provider_id: $github_id
} RETURN AFTER
)[0];
IF $linked_auth_account = NONE OR $linked_auth_account.id = NONE {
THROW {
stage: "auth_account_link_existing_failed",
message: "Failed to create auth_accounts row for existing user",
user: $existing_user
};
};
RETURN $existing_user;
};
------------------------------------------------------------
-- 6) Still no user → create new user, link, return
------------------------------------------------------------
LET $new_user = (
CREATE users CONTENT {
email: $email,
name: $u.name,
avatar_url: $u.avatar_url,
password: rand::string(32)
} RETURN AFTER
)[0];
IF $new_user = NONE {
THROW {
stage: "user_creation_failed",
message: "Failed to create user record from GitHub data",
github_user: $u
};
};
IF $new_user.id = NONE {
THROW {
stage: "user_creation_missing_id",
message: "Created users row has no id",
user: $new_user
};
};
LET $new_auth_account = (
CREATE auth_accounts CONTENT {
user: $new_user.id,
provider: "github",
provider_id: $github_id
} RETURN AFTER
)[0];
IF $new_auth_account = NONE OR $new_auth_account.id = NONE {
THROW {
stage: "auth_account_creation_failed",
message: "Failed to create auth_accounts row for new user",
user: $new_user
};
};
RETURN $new_user;
};
DEFINE ACCESS OVERWRITE github ON DATABASE TYPE RECORD
SIGNUP (
fn::github_login($code)
)
SIGNIN (
fn::github_login($code)
)
DURATION
FOR TOKEN 1h,
FOR SESSION NONE;
;
We have three main tables: users, auth_accounts, and auth_providers.
-
usersis self-explanator -
auth_accountsallows us to have multiple providers -
auth_providerssaves our client_id and client_secret. Surreal Cloud DOES NOT allow you to have environment variables, plus this could be helpful if you have an admin panel where you could update this.
GitHub Secret and Client ID
We must first create a GitHub oAuth App under GitHub Developer Settings. Save the client_secret and client_id. You can't get the secret later. You will also need two different apps for production and development.
For this purpose, make sure the callback URL is set to http://localhost:5173/auth/callback or whatever is relevant to your framework.
Insert Data
There are no environment variables in Surreal, but we can save our data in a safe table we just created. This allows it to be available safely to any client.
CREATE auth_providers SET
provider = "github",
client_id = "<YOUR_GITHUB_CLIENT_ID>",
client_secret = "<YOUR_GITHUB_CLIENT_SECRET>";
GitHub Login
The key to the entire schema is the gitHub_login method.
- It takes a code and gets an access token
- It takes the access token and gets an email (must have email scope)
- It gets the github ID and email and saves it to the database
- I tried to throw every possible error for debugging purposes
- It should be safe client side so that you can login from anywhere
Allow GitHub in the Network
Make sure to add allowed network calls in your Surreal Instance setup
- api.github.com
- github.com
SvelteKit Changes
We added a callbackURL and some oauth credential management to our surrealServer setup.
export function surrealServer({
cookies: { cookieName, setCookie, getCookie },
credentials: { url, namespace, database },
oauth: {
github: {
client_id: github_client_id
}
},
callbackURL
}: {
cookies: {
cookieName?: string;
setCookie: SetCoookieFn;
getCookie: GetCookieFn;
};
oauth: {
github: {
client_id: string;
}
};
credentials: {
url: string;
namespace: string;
database: string;
};
callbackURL: string
}) {
Which can be called like this in our hooks.
export const handle: Handle = async ({ event, resolve }) => {
event.locals.surreal = surrealServer({
cookies: {
setCookie: (name, value, options) => event.cookies.set(name, value, options),
getCookie: (name) => event.cookies.get(name)
},
credentials: {
url: config.url,
namespace: config.namespace,
database: config.database
},
oauth: {
github: {
client_id: PUBLIC_GITHUB_CLIENT_ID
}
},
callbackURL: event.url.origin + '/auth/callback'
});
return resolve(event);
};
We only need the client_id to generate the login with GitHub URL.
Connection
We create a sign in function.
export async function surrealGHLogin({
db,
namespace,
database,
code
}: {
db: Surreal;
namespace: string;
database: string;
code: string;
}) {
try {
const signinData = await db.signin({
namespace,
database,
variables: {
code
},
access: 'github'
});
return {
data: signinData,
error: null
};
} catch (e) {
if (e instanceof SurrealDbError) {
console.log(e.message);
return {
data: null,
error: e
};
}
if (e instanceof Error) {
return {
data: null,
error: e
};
}
return {
data: null,
error: new Error('Unknown error during login')
};
}
}
And we call it inside our Surreal Server instance just like our other login methods.
async function loginWithCallback(url: URL) {
logout();
const code = url.searchParams.get('code');
const state = url.searchParams.get('state');
if (!code) {
return {
data: null,
error: new Error('Missing authorization code')
};
}
let next = '/';
try {
const parsed = state && JSON.parse(state);
next = parsed?.next ?? '/';
} catch { /* empty */ }
const { data: db, error: dbError } = await connect();
if (dbError) {
return {
data: null,
error: dbError
};
}
const { data: token, error: loginError } = await surrealGHLogin({
db,
namespace,
database,
code
});
if (loginError) {
return {
data: null,
error: loginError
};
}
setCookie(tokenName, token, TOKEN_COOKIE_OPTIONS);
return {
data: next,
error: null
};
}
And call it in the /auth/callback route.
import type { PageServerLoad } from './$types';
import { redirect, error } from '@sveltejs/kit';
export const load = (async ({ url, locals: { surreal } }) => {
const {
data: next,
error: loginError
} = await surreal.loginWithCallback(url);
if (loginError) {
error(400, loginError.message);
}
redirect(303, next);
}) satisfies PageServerLoad;
We get the code and next path from the URL. Then we login and save the session.
Create the Redirect
Working backwards, we have to create the redirect in order to login.
export function createGitHubOAuthLoginUrl(
redirect_uri: string,
path: string,
client_id: string,
customParameters?: Record<string, string>,
addScopes?: string[]
) {
// Build scope string with default scopes and additional ones
const baseScopes = ['read:user', 'user:email'];
const scopes = addScopes ? [...baseScopes, ...addScopes] : baseScopes;
const params: Record<string, string> = {
client_id,
redirect_uri,
scope: scopes.join(' '),
state: JSON.stringify({
next: path,
provider: 'github'
})
};
// Add custom parameters if provided (e.g., 'login', 'allow_signup')
if (customParameters) {
Object.assign(params, customParameters);
}
return new URL(
'https://github.com/login/oauth/authorize?' + new URLSearchParams(params).toString()
).toString();
}
It is customizable, must require an email, and saves our next path to the state.
It can then be accessed in our server:
function getGitHubURL(next: string) {
return createGitHubOAuthLoginUrl(
callbackURL,
next,
github_client_id
);
}
And called from the form action:
github: async ({ request, locals: { surreal } }) => {
const formData = await request.formData();
const next = formData.get('next') || '/';
if (typeof next !== 'string') {
error(500, 'Invalid form data');
}
const githubURL = surreal.getGitHubURL(next);
redirect(303, githubURL);
}
And that's it! A working login with GitHub.
We could equally add any provider in the same way, and it will automatically link the providers by email!
Until next time...
Repo: GitHub
J
Top comments (0)