Drizzle ORM SQLite and Nuxt - Integrating Nuxt Auth, Part 2
Tags: nuxt, drizzle, orm, vue
Published: 2023-08-19 03:00:16

###The Series

1. [Drizzle ORM, SQLite and Nuxt JS - Getting Started](https://dev.to/aaronksaunders/drizzle-orm-sqlite-and-nuxt-js-getting-started-374m)
2. [Drizzle ORM SQLite and Nuxt - Integrating Nuxt Auth, Part 1](https://dev.to/aaronksaunders/drizzle-orm-sqlite-and-nuxt-integrating-nuxt-auth-part-1-534e)
3. Drizzle ORM SQLite and Nuxt - Integrating Nuxt Auth, Part 2

---

##Overview
In this series, we will use the package [@sidebase/nuxt-auth](https://sidebase.io/nuxt-auth/getting-started) -  to implement email + password authentication in the application. We will create login and register API routes that utilize [Drizzle ORM](https://orm.drizzle.team/) connected to an SQLite Database. Then we will create the user  interface to support registering and logging in user.


This blog post is a walkthrough of the code added to the application as we work though the video, meaning this is a companion post to support the video

###VIDEO
{% youtube g7oBbpP9eqY %}

###Modifying User Table To Support Authentication Using Drizzle

Update the schema file to include a new column in database for the `username` and `password`

```jsx
import { InferModel, sql } from "drizzle-orm";
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: integer("id").primaryKey(),
  firstName: text("first_name"),
  lastName: text("last_name"),
  age: integer("age"),
  username : text("username"),
  password : text("password"),
  createdAt: text("created_at").default(sql`CURRENT_TIMESTAMP`),
});

export type User = InferModel<typeof users>;
```

Now we need to migrate the data first by creating the migration file.

```jsx
npm exec drizzle-kit generate:sqlite
```

and then using the cli to push the changes to the database.

```jsx
npm exec drizzle-kit push:sqlite
```

You can then launch drizzle studio to see changes in the database

```jsx
npm exec drizzle-kit studio
```

See the changes in the database 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r0bvwf14bhbk1nfld05c.png)

###Create API Route For User Registration

For this section, we will need to install a new package and the required types for encrypting and comparing password
```
npm install bcryptjs
npm install --save-dev @types/bcryptjs 
```

We will leverage the user.post route to create the new `register.post` API route to register a new user in the system. We will take the same properties in the `body` except we will hash the password and save the hashed password and associate it with the `username`, `first_name`, `last_name` and `age` in the database.

This function call is used to save the user to the database using the drizzle api

```jsx
const result = db.insert(users).values(newUser).run();
```

Full source code for `register.post.ts`

```jsx
import { users, InsertUser } from "@/db/schema";
import { db } from "@/server/sqlite-service";
import * as bcrypt from "bcrypt";

export default defineEventHandler(async (event) => {
  try {
    const body = await readBody(event);

    // hash password
    const hashedPassword = bcrypt.hashSync(body.password, 10);
    const newUser: InsertUser = {
      ...body,
      password : hashedPassword
    }
    const result = db.insert(users).values(newUser).run();
    return { newUser : result}
  } catch (e: any) {
    throw createError({
      statusCode: 400,
      statusMessage: e.message,
    });
  }
});
```

###Create API Route For User Login

Make sure you have turned on `globalAppMiddleware` in you nuxt configuration. This will protect all of the pages in the app from users unless they are authenticated. We will use page metadata to provide access to the register user page. 

```jsx
// https://nuxt.com/docs/api/configuration/nuxt-config
export default defineNuxtConfig({
  devtools: { enabled: true },
  modules: ["@sidebase/nuxt-auth"],
  auth: {
    globalAppMiddleware: true,
  },
});
```

Create the file `login.post.ts` where we will process a login request using the `username` and `password` from the body parameter

Then we retrieve the user based on the `username`, and return error if we cannot find the user. We get the user using the Drizzle API for selecting objects from the database.

```jsx
const usersResp = db.select().from(users)
		  .where(eq(users.username, username))
		  .get();
```

Next we compare the password we hashed with password retrieved from database

```jsx
if (!bcrypt.compareSync(password, 
  usersResp.password as string)) {
  throw new Error("Invalid Credentials ");
}
```

Full source code for api route `login.post.ts`

```jsx
import { users, InsertUser } from "@/db/schema";
import { db } from "@/server/sqlite-service";
import * as bcrypt from "bcrypt";
import { eq } from "drizzle-orm";

export default defineEventHandler(async (event) => {
  try {
    const { username, password } = await readBody(event);

    const usersResp = db
      .select()
      .from(users)
      .where(eq(users.username, username))
      .get();

    if (!usersResp) throw new Error("User Not Found");

    if (!bcrypt.compareSync(password, usersResp.password as string)) {
      throw new Error("Invalid Credentials ");
    }

    const authUser = usersResp;
    authUser["password"] = null;

    return authUser;
  } catch (e: any) {
    throw createError({
      statusCode: 400,
      statusMessage: e.message,
    });
  }
});
```

###Integrate Login API Route Into Nuxt Auth

Let now remove the template provided code for handling authentication from Nuxt-Auth and use our new API route we just created. We will use the UI provided by Nuxt-Auth in this example so there is no need to create a separate route and page for logging in a user

We have replaced the `async` function in the `NuxtAuthHandler`  `Credentials.Provider` using the following code. This will call our api and return a user if there is a match, otherwise return null.

```jsx
async authorize(credentials: any) {
  let url = "http://localhost:3000/api/login";
  let options = {
    method: "POST",
    headers: {
      Accept: "*/*",
	"Content-Type": "application/json",
    },
    body: JSON.stringify({
      username: credentials.username,
      password: credentials.password,
    }),
  };
	
  const resp = await fetch(url, options);
  if (!resp.ok) return null;	
	
  const user = await resp.json();
  	
  return user;
},
```

###Getting Custom Information Into Session

We want to return some user specific information is our session after login. To do that you need to add some custom callback to assign those properties to the session after first adding then to the auth token.

So first set the token from the user object, we want the `id` and the `username` in the session

```jsx
callbacks: {
  jwt: async ({ token, user }) => {
    const isSignIn = user ? true : false;
    if (isSignIn) {
      token.id = user ? user.id || "" : "";
      token.username = user ? (user as any).username || "" : "";
    }
    return Promise.resolve(token);
  },
  session: async ({ session, token }) => {
  },
},
```

Then in the session callback, we get the id and the username from the token and add it to the session. Here I am just assigning ever

```jsx
session.user = { ...session.user, id :token.id, username : token.username };
```

Full source code for the callbacks are listed below

```jsx
callbacks: {
  jwt: async ({ token, user }) => {
    const isSignIn = user ? true : false;
    if (isSignIn) {
      token.id = user ? user.id || "" : "";
      token.username = user ? (user as any).username || "" : "";
    }
    return Promise.resolve(token);
  },
  session: async ({ session, token }) => {
    session.user = { ...session.user, 
											id :token.id, 
											username : token.username 
									  };
    return Promise.resolve(session);
  },
},
```

Full Source Code for the `NuxtAuthHandler`

```jsx
import CredentialsProvider from "next-auth/providers/credentials";
import { NuxtAuthHandler } from "#auth";

export default NuxtAuthHandler({
  // secret needed to run nuxt-auth in production mode (used to encrypt data)
  secret: process.env.NUXT_SECRET,
  pages: {
    // Change the default behavior to use `/login` as the path for the sign-in page
    signIn: '/login'
  },
  callbacks: {
    jwt: async ({ token, user }) => {
      const isSignIn = user ? true : false;
      if (isSignIn) {
        token.id = user ? user.id || "" : "";
        token.username = user ? (user as any).username || "" : "";
      }
      return Promise.resolve(token);
    },
    session: async ({ session, token }) => {
      session.user = {
        ...session.user,
        ...token,
      };
      return Promise.resolve(session);
    },
  },
  providers: [
    // @ts-ignore Import is exported on .default during SSR, so we need to call it this way. May be fixed via Vite at some point
    CredentialsProvider.default({
      // The name to display on the sign in form (e.g. 'Sign in with...')
      name: "Credentials",
      credentials: {
        username: {
          label: "Username",
          type: "text",
          placeholder: "(hint: jsmith)",
        },
        password: {
          label: "Password",
          type: "password",
          placeholder: "(hint: hunter2)",
        },
      },
      async authorize(credentials: any) {
        let url = "http://localhost:3000/api/login";
        let options = {
          method: "POST",
          headers: {
            Accept: "*/*",
            "Content-Type": "application/json",
          },
          body: JSON.stringify({
            username: credentials.username,
            password: credentials.password,
          }),
        };

        const resp = await fetch(url, options);
        if (!resp.ok) return null;

        const user = await resp.json();
        console.log(user);

        return user;
      },
    }),
  ],
});
```

###Integrate Register API Route and Register User Page

Here is the code for the template for the register user page.

```jsx
<template>
  <button @click="router.back()">BACK</button>
  <form @submit.prevent="register" class="form-container">
    <div class="form-group">
      <label for="username">Username:</label>
      <input v-model="username" type="text" id="username" />
    </div>
    <div class="form-group">
      <label for="password">Password:</label>
      <input v-model="password" type="password" id="password" />
    </div>
    <div class="form-group">
      <label for="firstName">First Name:</label>
      <input v-model="firstName" type="text" id="firstName" />
    </div>
    <div class="form-group">
      <label for="lastName">Last Name:</label>
      <input v-model="lastName" type="text" id="lastName" />
    </div>
    <div class="form-group">
      <label for="age">Age:</label>
      <input v-model="age" type="number" id="age" />
    </div>
    <div class="form-group">
      <button type="submit">Register</button>
    </div>
  </form>
</template>
```

We are capturing the `username`, `password`, `first_name`, `last_name` and `age`. We will submit the form to the function `register` when the user clicks the login button

At the top of the `script` tag we need to add some page metadata so Nuxt-Auth know how to handle the page.

```jsx
definePageMeta({
  auth: {
    unauthenticatedOnly: true,
    navigateAuthenticatedTo: "/",
  },
});
```

This states that the page can only be access by unauthenticated users and if any other is routed to this page, navigate them to the default index page.

Next define the `ref(s)` for the form entries

```jsx
const username = ref("");
const age = ref(0);
const password = ref("");
const firstName = ref("");
const lastName = ref("");
```

The `useRouter` hook for routing and the `useAuth` hook form `NuxtAuth` for accessing the `signIn` method that will be used to signIn the user after the account is created successfully

```jsx
import { ref } from "vue";
const router = useRouter();
const { signIn } = useAuth();
```

Inside the `register` function we set the `options` `url` and `body` for calling the API route created for registering a user using `fetch`

```jsx
let url = "http://localhost:3000/api/register";
let options = {
  method: "POST",
  headers: {
    Accept: "*/*",
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    username: username.value,
    password: password.value,
    firstName: firstName.value,
    lastName: lastName.value,
    age: age.value,
  }),
};

const resp = await fetch(url, options);
if (!resp.ok) throw new Error(resp.statusText);
```

if there is no error then we login the user using the `signIn` function from the `useAuth` composable.

If there is an error, we throw an exception, otherwise we redirect to the appropriate route specified in This URL can be set in the `redirect` property of the `auth` object in your `nuxt.config.js` file or it will default in index route.

```jsx
const signResp = await signIn("credentials", {
  username: username.value,
  password: password.value,
  redirect: false,
  callbackUrl: "/",
});
if ((signResp as any).error) throw (signResp as any).error;

return navigateTo((signResp as any).url, { external: true })
```

Full Source Code for Register User Page

```jsx
<template>
  <button @click="router.back()">BACK</button>
  <form @submit.prevent="register" class="form-container">
    <div class="form-group">
      <label for="username">Username:</label>
      <input v-model="username" type="text" id="username" />
    </div>
    <div class="form-group">
      <label for="password">Password:</label>
      <input v-model="password" type="password" id="password" />
    </div>
    <div class="form-group">
      <label for="firstName">First Name:</label>
      <input v-model="firstName" type="text" id="firstName" />
    </div>
    <div class="form-group">
      <label for="lastName">Last Name:</label>
      <input v-model="lastName" type="text" id="lastName" />
    </div>
    <div class="form-group">
      <label for="age">Age:</label>
      <input v-model="age" type="number" id="age" />
    </div>
    <div class="form-group">
      <button type="submit">Register</button>
    </div>
  </form>
</template>

<script setup lang="ts">
definePageMeta({
  auth: {
    unauthenticatedOnly: true,
    navigateAuthenticatedTo: "/",
  },
});
import { ref } from "vue";
const router = useRouter();
const { signIn } = useAuth();

const username = ref("");
const age = ref(0);
const password = ref("");
const firstName = ref("");
const lastName = ref("");

const register = async () => {
  try {
    // do register
    let url = "http://localhost:3000/api/register";
    let options = {
      method: "POST",
      headers: {
        Accept: "*/*",
        "Content-Type": "application/json",
      },
      body: JSON.stringify({
        username: username.value,
        password: password.value,
        firstName: firstName.value,
        lastName: lastName.value,
        age: age.value,
      }),
    };

    const resp = await fetch(url, options);
    if (!resp.ok) throw new Error(resp.statusText);

    const user = await resp.json();
    console.log(user);

    const signResp = await signIn("credentials", {
      username: username.value,
      password: password.value,
      redirect: false,
      callbackUrl: "/",
    });
    if ((signResp as any).error) throw (signResp as any).error;

    return navigateTo((signResp as any).url, { external: true })

  } catch (e) {
    alert((e as any).message);
  } finally {
    // Reset form fields
    username.value = "";
    age.value = 0;
    password.value = "";
    firstName.value = "";
    lastName.value = "";
  }
};
</script>
<style scoped>
.form-container {
  display: grid;
  grid-template-columns: max-content auto;
  gap: 8px;
  align-items: center;
  width: 400px;
  margin: 32px;
}

.form-group {
  display: contents; /* Allow the label and input to be displayed inline */
}

.form-group label {
  text-align: right;
  padding-right: 8px;
}

.form-group input {
  width: 100%; /* Occupy full width of the column */
}
</style>
```

###Links
- Sidebase Nuxt-Auth -https://sidebase.io/nuxt-auth/getting-started
- Drizzle Quick Start - https://orm.drizzle.team/docs/quick-start
- Drizzle Kit - https://orm.drizzle.team/kit-docs/overview
- Drizzle ORM Sqlite - https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/README.md
- Better Sqlite 3 - https://github.com/WiseLibs/better-sqlite3

###Social Media 
- Twitter - https://twitter.com/aaronksaunders
- Facebook - https://www.facebook.com/ClearlyInnovative
- Instagram - https://www.instagram.com/aaronksaunders
- Dev.to - https://dev.to/aaronksaunders
- YouTube - https://www.youtube.com/@Aaronsaundersci