Username & Password

🚧 Feature has not been thoroughly tested. Proceed at your own risk. 🚧

Models

Users

import { pgTable, text } from "drizzle-orm/pg-core";

import { archivedAt, createdAt, randomId, updatedAt } from "#/helpers/cols";

// const username = text("username"); // Abstracting like this results in an issue where drizzle-kit applies notNull to both schemas

export const users = pgTable(
  "users",
  {
    id: randomId.defaultRandom(),
    username: text("username").notNull().unique(),
    passwordHash: text("password_hash").notNull(),
    createdAt: createdAt.defaultNow(),
    updatedAt,
  },
  /** @todo add checks for usename and passwordHash as needed. Examples below: */
  // (table) => [
  //   check(
  //     "username_check",
  //     sql`${table.username} ~ '^(?!.*__)[a-z0-9][a-z0-9_]{2,14}[a-z0-9]$'`,
  //   ),
  //   check(
  //     "password_hash_check",
  //     sql`char_length(${table.passwordHash}) <= 255`,
  //   ),
  // ],
);

export const usersArchive = pgTable("users_archive", {
  id: randomId,
  username: text("username"), // nullable so PII can be erased while maintaining a record
  createdAt,
  updatedAt,
  archivedAt,
});

When to use a check constraint?

Check constraints for username and passwordHash are commented out by default.

That allows you to use the valibot schemas in #/models/users/validations.ts as your single source of truth for username and passwordHash validations.

If you do not expect your requirements to change or if multiple applications will use this table, it may be beneficial write checks that match your valibot schemas.

Why not use pgcrypto crypt()?

pgcrypto provides a built-in crypt() function that hashes passwords. There are four main reasons wunshot opts not to use it:

Relying on a database function for hashing means that the password would need to remain in plaintext while in transit. (All transit between your app and the database should be encrypted, but that’s a different topic.)
The recommended argon2id algorithm is not available in pgcrypto.
pgcrypto integration with Drizzle ORM is not straight-forward. It would involve a lot of magic sql
Hashing in the server/application layer continues to just work if a different RDBMS is used.

import { createInsertSchema, createSelectSchema } from "drizzle-valibot";
import * as v from "valibot";

import { Argon2 } from "#/helpers/validators";

import { users } from "./schemas";

// === PRIMITIVES ===

const { id } = createSelectSchema(users).entries;

const { username, passwordHash: passwordHashPrimitive } =
  createInsertSchema(users).entries;

const passwordHash = v.pipe(passwordHashPrimitive, Argon2);

// === UI ===

export const UserFormInput = v.object({
  username: v.pipe(
    username,
    v.minLength(4),
    v.maxLength(16), // Somewhat arbitrary. For comparison: Twitter/X uses 15, Instagram uses 30, and TikTok uses 24
    v.regex(
      /^[a-z0-9_]+$/,
      "can only contain lowercase letters, numbers, or underscores",
    ),
    v.regex(/^[a-z0-9].*$/, "must start with a letter or number"),
    v.regex(/^.+[a-z0-9]$/, "must end with a letter or number"),
    v.regex(/^((?!__).)*$/, "cannot contain consecutive underscores"),
    v.regex(/^((?!admin).)*$/i, "cannot contain the word 'admin'"),
  ),
  password: v.pipe(
    v.string(),
    /**
     * Based on NIST guidelines https://pages.nist.gov/800-63-3/sp800-63b.html#5111-memorized-secret-authenticators
     * May increase to 15 following new draft https://nvlpubs.nist.gov/nistpubs/SpecialPublications/NIST.SP.800-63B-4.2pd.pdf
     */
    v.minLength(8),
    /**
     * The `normalize` function is used to ensure consistent unicode support.
     * Read more about unicode normalization [here](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/normalize#description)
     */
    v.normalize("NFKC"),
    /**
     * 72 bytes is the max allowed by bcrypt. This project is set up to use argon2 by default,
     * but some projects may need to use bcrypt to cut down on third-party packages
     *
     * It is also large enough to allow a 64 character passphrase while
     * short enough to prevent [DoS attacks associated with long passwords](https://www.acunetix.com/vulnerabilities/web/long-password-denial-of-service/)
     */
    v.maxBytes(72),
  ),
});

// === INSERT ===

export const InsertUser = v.object({
  username,
  passwordHash,
});

// === SELECT ===

export const SelectUser = v.object({ id });

export const ByUsername = v.object({ username });

// === UPDATE ===

export const UpdateUserUsername = v.object({
  id,
  username,
});

// ...

// === SELECT ===

// ...

const selectUserByUsernameStmt = ({
  qx,
  label = "",
}: {
  qx: QueryExecutor;
  label?: string;
}) =>
  qx
    .select()
    .from(users)
    .where(eq(users.id, sql.placeholder("username")))
    .limit(1)
    .prepare(`select_user_by_username_${label}`);

const selectUserByUsernameDefault = selectUserByUsernameStmt({ qx: db });

export async function selectUserByUsername(
  input: InferInput<typeof ByUsername>,
  viaTx?: { tx: Tx; label: string },
) {
  const _selectUser = viaTx
    ? selectUserStmt({ qx: viaTx.tx, label: viaTx.label })
    : selectUserByUsernameDefault;

  const [data] = await _selectUser.execute(parse(ByUsername, input));

  return data;
}

// === UPDATE ===

const updateUserUsernameStmt = ({
  qx,
  label = "",
}: {
  qx: QueryExecutor;
  label?: string;
}) =>
  qx
    .update(users)
    .set({
      username: sql`${sql.placeholder("username")}`,
      updatedAt: sql`NOW()`,
    })
    .where(eq(users.id, sql.placeholder("id")))
    .returning({ username: users.username })
    .prepare(`update_user_username_${label}`);

const updateUserUsernameDefault = updateUserUsernameStmt({ qx: db });

export async function updateUserUsername(
  input: InferInput<typeof UpdateUserUsername>,
  viaTx?: { tx: Tx; label: string },
) {
  const _updateUserUsername = viaTx
    ? updateUserUsernameStmt({ qx: viaTx.tx, label: viaTx.label })
    : updateUserUsernameDefault;

  const [data] = await _updateUserUsername.execute(
    parse(UpdateUserUsername, input),
  );

  return data;
}

Ops

// ...

const failureCauses = {
  // ...
  INVALID_PASSWORD: "INVALID_PASSWORD",
} as const;

// ...

  if (!validationSuccess)
    throw new ActivityError({
      activity: {
        failureCause: flatten<typeof UserFormInput>(validationIssues).nested
          ?.username
          ? failureCauses.INVALID_CREDENTIAL
          : failureCauses.INVALID_PASSWORD,
        failedCredential:
          input && typeof input === "object" && Object.hasOwn(input, "username")
            ? (input as { username: unknown }).username
            : null,
      },
      message: failureOutputMessages.GENERIC,
    });

  /** Destructure necessary fields from the validation output based on the auth strategy */
  const { username, password } = validationOutput;

  /** Hash the password before storing it in the database */
  const passwordHash = await hashTarget(password);

// ...

    /** Attempt to add a user */
    const user = await insertUser({ username, passwordHash }).catch((error) => {
      /**
       * If a user already exists with the given credential, return specified error
       * https://www.postgresql.org/docs/current/errcodes-appendix.html#ERRCODES-TABLE
       */
      if (error.code === "23505") {
        throw new ActivityError({
          activity: {
            failureCause: failureCauses.USER_ALREADY_EXISTS,
            failedCredential: username,
          },
          message: failureOutputMessages.USER_ALREADY_EXISTS,
        });
      }

  // ...

  /** Return the user data and tokens */
  return {
    success: true,
    data: {
      //...
      username,
    },
  } as const;

  // ...

// ...

const failureCauses = {
  // ...
  INVALID_PASSWORD: "INVALID_PASSWORD",
  // ...
  WRONG_PASSWORD: "WRONG_PASSWORD",
} as const;

// ...

if (!validationSuccess)
  throw new ActivityError({
    activity: {
      failureCause: flatten<typeof UserFormInput>(validationIssues).nested
        ?.username
        ? failureCauses.INVALID_CREDENTIAL
        : failureCauses.INVALID_PASSWORD,
      failedCredential:
        input && typeof input === "object" && Object.hasOwn(input, "username")
          ? (input as { username: unknown }).username
          : null,
    },
    message: failureOutputMessages.GENERIC,
  });

/** Destructure necessary fields from the validation output based on the auth strategy */
const { username, password } = validationOutput;

/** Attempt to find the user for the given credentials based on the auth strategy */
const user = await selectUserByUsername({ username });

/** If no user is found, throw an error */
if (!user) {
  throw new ActivityError({
    activity: {
      failureCause: failureCauses.USER_NOT_FOUND,
      failedCredential: username,
    },
    message: failureOutputMessages.GENERIC,
  });
}

const { id: userId, passwordHash } = user;

const isPasswordVerified = await verifyTarget(passwordHash, password);

/** If the password is not correct, throw an error */
if (!isPasswordVerified) {
  throw new ActivityError({
    activity: {
      failureCause: failureCauses.WRONG_PASSWORD,
      failedCredential: username,
    },
    message: failureOutputMessages.GENERIC,
  });
}

// ...

/** Return the user data and tokens */
return {
  success: true,
  data: {
    // ...
    username,
  },
} as const;

// ...

Username & Password

Models

Users

Ops

Sign Up

Sign In