NextAuth authentication with Nile

This example shows how to use NextAuth with Nile's tenant management and isolation. NextAuth is a popular and easy to use authentication library for Next.js applications. Using NextAuth with Nile as the database gives you access to passwordless authentication, session-based identity, and most important - tenant isolation. Properly configured, Nile will automatically validate that a user has access to the tenant when executing queries on behalf of a user.

Getting Started

1. Create a new database

Sign up for an invite to Nile if you don't have one already and choose "Yes, let's get started". Follow the prompts to create a new workspace and a database.

2. Create todo table

After you created a database, you will land in Nile's query editor. Since our application requires a table for storing all the "todos" this is a good time to create one:

    create table todos (id uuid DEFAULT (gen_random_uuid()), tenant_id uuid, title varchar(256), complete boolean);

If all went well, you'll see the new table in the panel on the left hand side of the query editor. You can also see Nile's built-in tenant table next to it.

3. Create tables for NextAuth data model

-- These are the extra tables NextAuth requires in your database to support

CREATE TABLE IF NOT EXISTS "accounts" (
    "userId" uuid NOT NULL,
    "type" text NOT NULL,
    "provider" text NOT NULL,
    "providerAccountId" text NOT NULL,
    "refresh_token" text,
    "access_token" text,
    "expires_at" integer,
    "token_type" text,
    "scope" text,
    "id_token" text,
    "session_state" text,
    CONSTRAINT account_provider_providerAccountId_pk PRIMARY KEY("provider","providerAccountId"),
    CONSTRAINT account_userId_users_id_fk FOREIGN KEY ("userId") REFERENCES users.users("id") ON DELETE cascade ON UPDATE no action
);

CREATE TABLE IF NOT EXISTS "sessions" (
    "sessionToken" text PRIMARY KEY NOT NULL,
    "userId" uuid NOT NULL,
    "expires" timestamp NOT NULL,
    CONSTRAINT "session_userId_users_id_fk" FOREIGN KEY ("userId") REFERENCES users.users("id") ON DELETE cascade ON UPDATE no action
);

CREATE TABLE IF NOT EXISTS "verification_token" (
    "identifier" text NOT NULL,
    "token" text NOT NULL,
    "expires" timestamp NOT NULL,
    CONSTRAINT verificationToken_identifier_token_pk PRIMARY KEY("identifier","token")
);

-- users table is built-in to Nile, but we need to extend it for NextAuth

alter table users.users add column "email_verified" timestamp;

Those should also show up on the left panel.

4. Getting credentials

In the left-hand menu, click on "Settings" and then select "Credentials". Generate credentials and keep them somewhere safe. These give you access to the database.

5. Setting the environment

If you haven't cloned this project yet, now will be an excellent time to do so. Since it uses NextJS, we can use create-next-app for this:

npx create-next-app -e https://github.com/niledatabase/niledatabase/tree/main/examples/user_management/NextAuth todo-nextauth
cd nile-todo

Rename .env.local.example to .env.local, and update it with your workspace and database name. (Your workspace and database name are displayed in the header of the Nile dashboard.) Also fill in the username and password with the credentials you picked up in the previous step.

Our example includes passwordless email and Github OAuth authentication. To use either method, you'll want to fill in the appropriate section of the environment file. You can refer to NextAuth getting started guides with email or oauth for more details.

The resulting env fileshould look something like this:

# Random string for encryption
NEXTAUTH_SECRET="random string. You should change this to something very random."

# For passwordless authentication, we need SMTP credentials
SMTP_USER=postmaster@youremaildomain.com
SMTP_PASSWORD=supersecret
SMTP_HOST=smtp.youremailhost.com
SMTP_PORT=587
EMAIL_FROM=someone@youremaildomain.com

# For Github OAuth, we need the client ID and secret we got when we registered with Github:
GITHUB_ID=Iv1.aa6f0f2bfa21b677
GITHUB_SECRET=b5b35282aa04bee94f31ccfaa44ed8c5e00fd2a9b2

# Private (backend) env vars for connecting to Nile database
NILE_DB_HOST = "db.thenile.dev"
NILE_USER =
NILE_PASSWORD =
NILE_DATABASE =

# Client (public) env vars

# the URL of this example + where the api routes are located
# Use this to instantiate Nile context for client-side components
NEXT_PUBLIC_BASE_PATH=http://localhost:3000/api
NEXT_PUBLIC_WORKSPACE=
NEXT_PUBLIC_DATABASE=

Install dependencies with npm install.

6. Running the app

npm run dev

Open http://localhost:3000 with your browser to see the result.

If all went well, your browser should show you the first page in the app, asking you to login or sign up.

After you sign up as a user of this example app, you'll be able to see this user by going back to Nile Console and running select * from users.users in the query editor.

Login with the new user, and you can create a new tenant and add tasks for the tenant. You can see the changes in your Nile database by running

select name, title, complete from
tenants join todos on tenants.id=todos.tenant_id

How it works

Setting up NextAuth

NextAuth is a very flexible authentication library that supports a wide range of authentication methods and providers. It is very easy to configure and use.

We set it up in app/api/auth/[...nextauth]/route.js:

export const authOptions: NextAuthOptions = {
    adapter: NileAdapter(pool),
    callbacks: {...}
    providers: [
      GithubProvider({
        clientId: process.env.GITHUB_ID,
        clientSecret: process.env.GITHUB_SECRET,
      }),
      Email({...}),
    ],
  }

const handler = NextAuth(authOptions)

export { handler as GET, handler as POST }

The NileAdapter is a custom adapter that implements the NextAuth adapter interface. It uses the Nile database to store user information and sessions.

This route handles all calls to /api/auth/* and provides the following endpoints:

• /api/auth/signin - handles sign in requests
• /api/auth/signout - handles sign out requests
• /api/auth/session - returns the current session
• /api/auth/providers - returns a list of configured providers
• /api/auth/callback/* - handles callbacks from authentication providers
• /api/auth/csrf - returns a CSRF token

Which we then use in our application.

Using NextAuth for Login / Logout

NextAuth SDK provides signIn() method that we call and it handles the login flow for us. We use it in app/pages.tsx.

import { signIn } from "next-auth/react";

<Button onClick={() => signIn("github", { callbackUrl: "/tenants" })}>
  Sign in with Github
</Button>;

As you can see, this is very easy to use. We just need to provide the provider name and the URL where we want the user to land after authenticating. NextAuth will handle the rest.

Similarly, to provide a logout link in app/tenants/page.tsx we link to the signout endpoint provided by NextAuth:

<MUILink href="/api/auth/signout" component={NextLink}>
  (Logout)
</MUILink>

Using NextAuth for identity and access

NextAuth provides a useSession() hook that we can use to get the current session. In order to use it with Nile's tenant isolation, we refer to it when retrieving a connection to Nile's tenant databases in /lib/NileServer.ts

This guarantees that all queries executed on behalf of the user will be executed in the context of the tenant the user is currently logged in to. Nile will also respond with errors if the user is not authorized to access the tenant.

export async function configureNile(tenantId: string | null | undefined) {
  console.log("configureNile", tenantId);
  const session = await getServerSession(authOptions);
  console.log(session);
  return nile.getInstance({
    tenantId: tenantId,
    //@ts-ignore
    userId: session?.user?.id,
  });
}

Adding new authentication providers

NextAuth supports a wide range of authentication providers. You can see the full list here. If you want to modify this example to use a different provider, you can do so by first modifying the NextAuth configuration in app/api/auth/[...nextauth]/route.js. Import the provider you want to choose and add a section to the authOptions object.

Then, you'll need to modify the UI to use the new provider. For example, if you want to use Google OAuth, you'll need to add a button to the UI that calls signIn("google").

Thats it. NextAuth will handle the rest and you will have a new authentication method for your multi-tenant application.