Working with Clerk and per-user databases

Learn how to create per-user databases in a multi-tenant architecture using Turso's Multi-DB Schemas feature and Clerk for user authentication and management.

Jamie BartonJamie Barton
Cover image for Working with Clerk and per-user databases

Creating per-user databases in a multi-tenant architecture can significantly enhance both data security, compliance, and and can result in an improved experience for developers building complex multi-tenant apps.

In this post, we'll explore some ideas on how to implement this using Turso's Multi-DB Schemas feature, and Clerk for user authentication and management.

This post assumes you have a basic understanding of Turso's Multi-DB Schemas and Clerk. If you're new to these concepts, you can check out the following resources.

Here's a refresher on Turso's Multi-DB Schemas:

#Starting with a Parent Database

The first step involves setting up a parent database with Turso, which will act as a template for each individual user database.

You can easily create a database using the Turso CLI by specifying --type schema. This schema will serve as the blueprint from which all user-specific databases are derived.

turso db create parent-db --type schema

#Create the Parent Database Schema

Now let's connect to the parent database and create a table to store user data:

turso db shell parent-db

Now we will create a todos table that will store a user's to-do items:

CREATE TABLE todos (id INTEGER PRIMARY KEY AUTOINCREMENT, description TEXT NOT NULL );

You're probably wondering what happens when the parent database schema diverges from the child databases.

Turso's Multi-DB Schema architecture allows you to update the parent schema and apply the changes to all child databases. This ensures that all user databases remain in sync with the parent schema. All of this is handled automatically.

#Integrating Clerk for User Authentication

Once you have your schema ready, the next step is to integrate Clerk into your existing application. Clerk will handle user authentication and management.

npm install @clerk/nextjs

Depending on what frontend framework you're using, you'll want to integrate Clerk in the way you're already familiar with. Here's what it looks like with Next.js and React:

import { ClerkProvider } from '@clerk/nextjs';

export default function RootLayout({
  children,
}: Readonly<{
  children: React.ReactNode;
}>) {
  return (
    <ClerkProvider>
      <html lang="en">
        <body>{children}</body>
      </html>
    </ClerkProvider>
  );
}

#Creating a Clerk webhook

By setting up a Clerk webhook, you can trigger database creation whenever a new user is created.

Before we begin, make sure you have the following dependencies installed:

npm install svix md5 @tursodatabase/api

You'll want to replace the @clerk/nextjs dependency with the Clerk SDK for your specific framework if you're not using Next.js.

You will also need to create and configure the following environment variables, using the Turso CLI:

Go to the Clerk Dashboard and create a new webhook. You'll need to provide the URL of your Turso API endpoint. Since Clerk uses svix for managing webhooks, we can use their library to verify webhooks are valid and secure.

Create a route handler for your Clerk webhook, this URL must match the same value you gave Clerk when creating the webhook — app/webhooks/clerk/route.ts.

import { createClient } from '@tursodatabase/api';
import { Webhook } from 'svix';
import { headers } from 'next/headers';
import { WebhookEvent } from '@clerk/nextjs/server';
import md5 from 'md5';

const turso = createClient({
  token: process.env.TURSO_USER_API_TOKEN!,
  org: process.env.TURSO_ORG_NAME!,
});

export async function POST(req: Request) {
  const WEBHOOK_SECRET = process.env.CLERK_WEBHOOK_SECRET;

  const headerPayload = headers();
  const svix_id = headerPayload.get('svix-id');
  const svix_timestamp = headerPayload.get('svix-timestamp');
  const svix_signature = headerPayload.get('svix-signature');

  const wh = new Webhook(WEBHOOK_SECRET);
  const payload = await req.json();
  const body = JSON.stringify(payload);

  let evt;

  try {
    evt = wh.verify(body, {
      'svix-id': svix_id,
      'svix-timestamp': svix_timestamp,
      'svix-signature': svix_signature,
    }) as WebhookEvent;
  } catch (err) {
    return new Response('Webhook verification failed', { status: 400 });
  }

  if (evt.type === 'user.created') {
    const databaseName = md5(evt.data.id);

    try {
      await turso.databases.create(databaseName, {
        schema: process.env.TURSO_SCHEMA_DATABASE_NAME!,
      });
    } catch (err) {
      return new Response('Database creation failed', { status: 500 });
    }
  }
}

The last part of the webhook is where the magic happens:

if (evt.type === 'user.created') {
  const databaseName = md5(evt.data.id);

  try {
    await turso.databases.create(databaseName, {
      schema: process.env.TURSO_SCHEMA_DATABASE_NAME!,
    });
  } catch (err) {
    return new Response('Database creation failed', { status: 500 });
  }
}

We take the data.id (User ID) and hash it using md5 to create a unique database name. We then create a new database using the parent schema.

Make sure to keep an eye on the package @brianmmdev/clerk-webhooks-handler for future development. This will certainly reduce the compexity of handling Clerk webhooks, and signature verification.

#Handling user login

When a user logs in, you'll want to make sure their database has been created and is ready to connect. If Clerk is busy, and the webhook has not yet been delivered or completed, you could opt to redirect users to an onboarding screen to complete their profile while the webhook is processed, or you could invoke creating a database directly on demand.

You will want to create a utility function checkDatabaseExists that checks if the user's database exists:

import { createClient as createTursoClient } from '@tursodatabase/api';
import { auth } from '@clerk/nextjs/server';
import md5 from 'md5';

const turso = createTursoClient({
  token: process.env.TURSO_USER_API_TOKEN!,
  org: process.env.TURSO_ORG_NAME!,
});

export function getDatabaseName() {
  const userId = auth().userId;
  if (!userId) return null;
  return md5(userId);
}

export async function checkDatabaseExists() {
  const dbName = getDatabaseName();
  if (!dbName) return false;

  try {
    await turso.databases.get(dbName);
    return true;
  } catch {
    return false;
  }
}

Then somewhere in your applications layout or middleware, check the databse exists and redirect them to the desired route:

const databaseExists = await checkDatabaseExists();

if (!databaseExists) redirect('/welcome');

To keep things simple, we'll redirect users to the route handler /welcome that checks again if the database exists, and if not, creates it!

import { auth } from '@clerk/nextjs/server';
import { redirect } from 'next/navigation';
import { createClient } from '@tursodatabase/api';

import { checkDatabaseExists, getDatabaseName } from '../utils';

const turso = createClient({
  token: process.env.TURSO_USER_API_TOKEN!,
  org: process.env.TURSO_ORG_NAME!,
});

export async function GET() {
  auth().protect();

  const databaseExists = await checkDatabaseExists();

  if (databaseExists) {
    return redirect('/dashboard');
  }

  const dbName = getDatabaseName();

  if (!dbName) {
    return redirect('/sign-in');
  }

  try {
    await turso.databases.create(dbName, {
      schema: process.env.TURSO_SCHEMA_DATABASE_NAME!,
      group: 'default',
    });
  } catch (err) {
    console.error('Error processing webhook:', err);
    return new Response('Error occured', {
      status: 500,
    });
  }

  redirect('/dashboard');
}

#Handling per-user database access

When a user logs in, you'll want to ensure they're connected to their specific database.

We'll be using the @libsql/client package to connect to the database, so make sure to install it:

npm install @libsql/client

Each user's database connection is managed via a function that constructs the database URL, based on the user's ID, as md5:

import { createClient as createLibsqlClient } from '@libsql/client';

export async function getDatabaseClient() {
  const dbName = getDatabaseName();

  return createLibsqlClient({
    url: `libsql://${dbName}.turso.io`,
    authToken: process.env.TURSO_DATABASE_GROUP_AUTH_TOKEN || '',
  });
}

That's it! You can now execute SQL using the @libsql/client instance generated by getDatabaseClient anywhere in your application:

const client = await getDatabaseClient();

const result = await client.execute('SELECT * FROM todos');

export type TodoItem = {
  id: number;
  description: string;
};

const rows = result.rows as unknown as Array<TodoItem>;

#Enabling data export

Lastly, one additional benefit to using Turso's Multi-DB Schemas is that you can give users the ability to download the entire database, because, it's only their data that they're downloading.

You can create a route that allows users to download their data. Here I have the Next.js route /app/dump.sql/route.ts:

export async function GET() {
  auth().protect();

  const url = getDumpUrl();

  if (!url) return new Response('No data available');

  try {
    const response = await fetch(url, {
      headers: {
        Authorization: `Bearer ${process.env.TURSO_DATABASE_GROUP_AUTH_TOKEN}`,
      },
    });

    if (response.ok) return new Response(await response.text());

    return new Response('No data available');
  } catch (err) {
    return new Response('Data download failed', { status: 500 });
  }
}

Which means you can link users to this page to download the .sql file at anytime:

<a download href="/dump.sql">
  Download my data
</a>

Congratulations! You've now created a multi-tenant application using Clerk and Turso. You can get the code on GitHub, and try the demo.

Share article

Twitter / XHacker NewsEmailLinkedIn
scarf