> ## Documentation Index
> Fetch the complete documentation index at: https://stytch.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Planetscale for user authentication

> Integrate Stytch Consumer Authentication with PlanetScale for user storage.

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

<img src="https://mintcdn.com/stytch-34ca0595/jCmOQoXV28mXNOhP/images/consumer/from-old-docs/stytch-planetscale-light.svg?fit=max&auto=format&n=jCmOQoXV28mXNOhP&q=85&s=8398ae386a7a7e2223b213e126e8d3e0" alt="Stytch and Planetscale" width="1621" height="144" data-path="images/consumer/from-old-docs/stytch-planetscale-light.svg" />

<Steps>
  <Step title="Create your PlanetScale user database">
    Create your user database with PlanetScale's `pscale` command line utility, then create your first database branch.

    ```bash theme={null}
    $ pscale auth login
    $ pscale database create <database-name>
    $ pscale branch create <database-name> <branch-name>
    ```
  </Step>

  <Step title="Create your users table">
    This query creates your users table where you'll store a record for each user.

    ```bash theme={null}
    $ 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
    );
    ```
  </Step>

  <Step title="Create a deploy request, deploy, and merge">
    ```bash theme={null}
    $ pscale deploy-request create <database-name> <branch-name>
    $ pscale deploy-request deploy <database-name> <deploy-request-number>
    ```
  </Step>

  <Step title="Create your backend">
    Establish a connection to PlanetScale, then create the functions to add, remove, and get users from the DB.

    ```js theme={null}
    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;
    }
    ```
  </Step>

  <Step title="Instantiate the Stytch client">
    Instantiate the Stytch client via our [stytch-node Backend SDK](https://github.com/stytchauth/stytch-node); 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.

    ```js theme={null}
    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;
    };
    ```
  </Step>

  <Step title="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.

    ```js theme={null}
    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;
    }
    ```
  </Step>

  <Step title="Authenticate Email Magic Links">
    Next you'll want a way to authenticate the magic links that are being sent by the Stytch SDK.

    ```js theme={null}
    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;
      }
    }
    ```
  </Step>

  <Step title="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](/consumer-auth/manage-sessions/overview) API to make that happen.

    ```js theme={null}
    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;
    }
    ```
  </Step>

  <Step title="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.

    ```js theme={null}
    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;
      }
    }
    ```
  </Step>

  <Step title="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](https://github.com/stytchauth/stytch-planetscale-integration).

    We can't wait to see what you build with Stytch! Get in touch with us and tell us what you think at [support@stytch.com](mailto:support@stytch.com) or in our [community Slack](https://join.slack.com/t/stytch/shared%5Finvite/zt-3aqo03e10-afWXyLzRIAlzGWJyF%5F~zHw).
  </Step>
</Steps>