docs/content/blogs/0-supabase-auth-to-planetscale-migration.mdx
import { HomeIcon } from "lucide-react"; import { Step, Steps } from "fumadocs-ui/components/steps";
Recently, PlanetScale announced support for PostgreSQL. This is exciting news for developers and a big step forward for the database industry.
We’ve noticed that some users are migrating from Supabase to PlanetScale PostgreSQL, but facing challenges because they also rely on Supabase Auth. This guide will help you migrate your authentication from Supabase Auth to Better Auth on PlanetScale PostgreSQL.
```jsx
postgresql://<username>:<password>@<host>/postgres?sslmode=verify-full
```
```txt title=".env"
DATABASE_URL =
postgresql://<username>:<password>@<host>/postgres?sslmode=verify-full
```
```package-install
npm install better-auth
```
Install the pg package and its types:
npm install pg
npm install --save-dev @types/pg
```bash
npx auth generate
```
```bash
npx auth migrate
```
Your auth config should be like this:
<Tabs items={["auth.ts", "auth-client.ts"]}> <Tab value="auth.ts"> ```ts import { Pool } from "pg"; import { betterAuth } from "better-auth";
export const auth = betterAuth({
baseURL: "http://localhost:3000",
database: new Pool({
connectionString: process.env.DATABASE_URL,
}),
emailAndPassword: {
enabled: true,
},
});
```
export const { signIn, signUp, useSession } = createAuthClient();
```
Now comes the fun part. You are now all setup to move your auth from Supabase Auth to Better Auth and all you have to do is go through the instances you've used Supabase Auth client and replace it with Better Auth client. We are going to see a few examples here.
<Tabs items={["sign up", "sign in", "session"]}> <Tab value="sign up"> ```ts // Supabase Auth await supabase.auth.signUp({ email, password, });
// Better Auth
await authClient.signUp.email({
email,
password,
name: "John",
});
```
// Better Auth
await authClient.signIn.email({
email,
password,
});
```
// Better Auth
const { data, error } = await authClient.useSession();
```
Essentially you should be able to copy the following code into migration.ts and run it.
import { Pool } from "pg";
import { auth } from "./lib/auth";
import { User as SupabaseUser } from "@supabase/supabase-js";
type User = SupabaseUser & {
is_super_admin: boolean;
raw_user_meta_data: {
avatar_url: string;
};
encrypted_password: string;
email_confirmed_at: string;
created_at: string;
updated_at: string;
is_anonymous: boolean;
identities: {
provider: string;
identity_data: {
sub: string;
email: string;
};
created_at: string;
updated_at: string;
};
};
const migrateFromSupabase = async () => {
const ctx = await auth.$context;
const db = ctx.options.database as Pool;
const users = await db
.query(
`
SELECT
u.*,
COALESCE(
json_agg(
i.* ORDER BY i.id
) FILTER (WHERE i.id IS NOT NULL),
'[]'::json
) as identities
FROM auth.users u
LEFT JOIN auth.identities i ON u.id = i.user_id
GROUP BY u.id
`
)
.then((res) => res.rows as User[]);
for (const user of users) {
if (!user.email) {
continue;
}
await ctx.adapter
.create({
model: "user",
data: {
id: user.id,
email: user.email,
name: user.email,
role: user.is_super_admin ? "admin" : user.role,
emailVerified: !!user.email_confirmed_at,
image: user.raw_user_meta_data.avatar_url,
createdAt: new Date(user.created_at),
updatedAt: new Date(user.updated_at),
isAnonymous: user.is_anonymous,
},
})
.catch(() => {});
for (const identity of user.identities) {
const existingAccounts = await ctx.internalAdapter.findAccounts(user.id);
if (identity.provider === "email") {
const hasCredential = existingAccounts.find(
(account: { providerId: string }) =>
account.providerId === "credential"
);
if (!hasCredential) {
await ctx.adapter
.create({
model: "account",
data: {
userId: user.id,
providerId: "credential",
accountId: user.id,
password: user.encrypted_password,
createdAt: new Date(user.created_at),
updatedAt: new Date(user.updated_at),
},
})
.catch(() => {});
}
}
const supportedProviders = Object.keys(ctx.options.socialProviders || {});
if (supportedProviders.includes(identity.provider)) {
const hasAccount = existingAccounts.find(
(account: { providerId: string }) =>
account.providerId === identity.provider
);
if (!hasAccount) {
await ctx.adapter.create({
model: "account",
data: {
userId: user.id,
providerId: identity.provider,
accountId: identity.identity_data?.sub,
createdAt: new Date(identity.created_at ?? user.created_at),
updatedAt: new Date(identity.updated_at ?? user.updated_at),
},
});
}
}
}
}
};
migrateFromSupabase();
Run the migration script
bun migration.ts # or use node, ts-node, etc.
If you have additional user-related data in Supabase, you can use the Supabase to PlanetScale migration tool.
You now own your auth, you should start removing all the Supabase Auth related code.
You've successfully migrated from Supabase Auth to Better Auth on PlanetScale.