Planetscale for user authentication

Use Stytch and PlanetScale to build out your entire user authentication flow and database.

Create your PlanetScale user database

Create your user database with PlanetScale’s pscale command line utility, then create your first database branch.

$ pscale auth login
$ pscale database create <database-name>
$ pscale branch create <database-name> <branch-name>

Create your users table

This query creates your users table where you’ll store a record for each user.

$ pscale shell <database-name> <branch-name> // Open a MySQL shell in your database.

CREATE TABLE users (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(255),
  email varchar(255) NOT NULL
);

Create a deploy request, deploy, and merge

$ pscale deploy-request create <database-name> <branch-name>
$ pscale deploy-request deploy <database-name> <deploy-request-number>

Create your backend

Establish a connection to PlanetScale, then create the functions to add, remove, and get users from the DB.

const URL = process.env.DATABASE_URL as string;
const sqlConn = mysql.createConnection(URL);
sqlConn.connect();

// getUsers retrieve all users.
async function getUsers(conn: PSDB, req: NextApiRequest, res: NextApiResponse) {
  try {
    var query = 'select * from users';

    const [getRows, _] = await conn.query(query, '');
    res.status(200).json(getRows);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'an error occurred' });
  }
  return;
}

// addUser create a new user.
async function addUser(conn: PSDB, req: NextApiRequest, res: NextApiResponse) {
  var user = JSON.parse(req.body);
  try {
    var query = 'INSERT INTO users (name, email) VALUES (?,?)';
    var params = [name, email];

    var insertID;
    const result = sqlConn.query(query, params, (err, result) => {
      if (err) {
        throw err;
      }

      insertID = (<OkPacket>result).insertId;
    });

    res.status(201).json({ id: insertID });
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'an error occurred' });
  }
  return;
}

// deleteUser remove a single user.
async function deleteUser(conn: PSDB, req: NextApiRequest, res: NextApiResponse) {
  try {
    var query = 'DELETE from users WHERE id=?';
    var params = [req.query['uid']];

    var status = 200;

    const result = await sqlConn
      .promise()
      .query(query, params)
      .then(([row]) => {
        if ((<OkPacket>row).affectedRows == 0) {
          status = 304;
        }
      });

    res.status(status).json({ message: 'success' });
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'an error occurred' });
  }
  return;
}

Instantiate the Stytch client

Instantiate the Stytch client via our stytch-node Backend SDK; you’ll use this SDK to make any calls to the Stytch API. After the user has been authenticated, you will redirect them to the logged-in page where they can take authenticated action.

import * as stytch from 'stytch';

let client: stytch.Client;
const loadStytch = () => {
  if (!client) {
    client = new stytch.Client({
      project_id: process.env.STYTCH_PROJECT_ID || '',
      secret: process.env.STYTCH_SECRET || '',
      env: process.env.STYTCH_PROJECT_ENV === 'live' ? stytch.envs.live : stytch.envs.test,
    });
  }

  return client;
};

Set up Stytch Email Magic Links

We would like to be able to invite and add new users to our dashboard, we’ll leverage our stytch-node Backend SDK to send Email Magic Links to users to do that.

async function inviteUser(req: NextApiRequest, res: NextApiResponse) {
  const client = loadStytch();

  var email = req.body.email;

  // Params are of type stytch.LoginOrCreateRequest.
  const params = {
    email: email,
    login_magic_link_url: `${BASE_URL}/api/authenticate_magic_link`,
    signup_magic_link_url: `${BASE_URL}/api/authenticate_magic_link`,
  };

  try {
    await client.magicLinks.email.loginOrCreate(params);
    res.status(200).json({"message":"magic link sent"});
  } catch (error) {
    res.status(400).json({ error });
  }
  return;
}

Authenticate Email Magic Links

Next you’ll want a way to authenticate the magic links that are being sent by the Stytch SDK.

import type { NextApiRequest, NextApiResponse } from 'next';
import loadStytch from '../../lib/loadStytch';
import { serialize } from 'cookie';

async function authenticate(req: NextApiRequest, res: NextApiResponse) {
  const client = loadStytch();
  const { token } = req.query;

  try {
    // Authenticate request and create 7 day session.
    const resp = await client.magicLinks.authenticate(token as string, { session_duration_minutes: 10080 });

    // Send user to profile with cookies in response.
    res.setHeader(
      'Set-Cookie',
      serialize(process.env.COOKIE_NAME as string, resp.session_token as string, { path: '/' }),
    );
    res.redirect('/profile');
    return;
  } catch (error) {
    res.status(400).json({ error });
    return;
  }
}

Set up session management

Now that we’ve got authentication in place, what happens when a user leaves and then comes back? We want that returning user to come back to an authenticated experience, so we will use Stytch’s Session Management API to make that happen.

export async function validSessionToken(token: string): Promise<boolean> {
  // Authenticate the session.
  try {
    const sessionAuthResp = await client.sessions.authenticate({
      session_token: token
    });

    if (sessionAuthResp.status_code != 200) {
      console.error('Failed to validate session');
      return false;
    }
    return true;
  } catch (error) {
    console.error(error);
    return false;
  }
}

// Add this session helper to each DB handler so we can prevent unauthenticated API calls.
var isValidSession = await validSessionToken(token);
if (!isValidSession) {
  res.status(401).json({ error: 'user unauthenticated' });
  return;
}

Handle clearing sessions

Now we’ll want to add logic to clear the client state once a session has expired. Doing so will allow us to gracefully re-prompt the user to log in when their session expires; when we initiated the session, we chose a session_duration_minutes of 10080 minutes, 7 days, so that users won’t be prompted to re-login if they return within that 7 day period.

export async function handler(req: NextApiRequest, res: NextApiResponse<Data>) {
  if (req.method === 'POST') {
    try {
      //  You can add logic to destroy other sessions or cookies as well.
      res
        .status(200)
        .setHeader('Set-Cookie', [
          serialize(process.env.COOKIE_NAME as string, '', { path: '/', maxAge: -1 }),
          serialize(STYTCH_SESSION_NAME, '', { path: '/', maxAge: -1 }),
        ]).json({"message":"logged out"});
      } catch (error) {
      res.status(400).json({ error });
    }
    return;
  }
}

You're done

You just finished all the critical backend components in our example. Your app can now login with Stytch Email Magic Links, manage sessions via Sessions Management, and maintain your user database via PlanetScale. You can find an example of this implementation in our Stytch + PlanetScale example app.We can’t wait to see what you build with Stytch! Get in touch with us and tell us what you think in our forum, support@stytch.com, or in our community Slack.