Database

Better auth requires a database connection to store data. It comes with a query builder called Kysley to manage and query your database. The database will be used to store data such as users, sessions, and more. Plugins can also define their own database tables to store data.

You can pass a database connection to better auth by passing a database object auth options. The database object takes provider key that specifies the database provider to use. By default, better auth support postgres, mysql and sqlite and a url key that specifies the database connection to use. If you're using sqlite, you can pass a file path in the url key.

You can also pass a kysley dialect directly to use other databases supported by kysley.

Example: Sqlite

import { betterAuth } from "better-auth"
export const auth = await betterAuth({
    database: {
        provider: "sqlite",
        url: "path/to/database.sqlite"
    }
})

Example: Postgres

import { betterAuth } from "better-auth"
export const auth = await betterAuth({
    database: {
        provider: "postgres",
        url: "postgres://user:password@localhost:5432/database"
    }
})

Exmaple: Mysql

import { betterAuth } from "better-auth"
export const auth = await betterAuth({
    database: {
        provider: "mysql",
        url: "mysql://user:password@localhost:3306/database"
    }
})

Exmaple: Custom Dialect using libsql

import { betterAuth } from "better-auth"
import { LibsqlDialect } from "@libsql/kysely-libsql";
 
export const auth = await betterAuth({
   	database: new LibsqlDialect({
		url: process.env.TURSO_DATABASE_URL || "",
		authToken: process.env.TURSO_AUTH_TOKEN || "",
	}),
})

Running Migrations

Better auth comes with a CLI tool to manage database migrations. Use the migrate command to create or update tables as needed.

The cli checks your database and prompts you to add missing tables or update existing ones with new columns.

npx better-auth migrate

If you prefer adding tables manually, you can do that as well. The core schema required by better auth is described below and you can find additional schema required by plugins in the plugin documentation.

Core Schema

Better auth requires the following tables to be present in the database:

user:

• id: (string) - The unique identifier of the user.
• email: (string) - The email address of the user.
• name: (string) - The name of the user.
• image: (string) - The image of the user. (optional)

session:

• id: (string) - The unique identifier of the session. Also used as the session token.
• userId: (foregin-key: user.id) - The id of the user.
• expiresAt: (Date) - The time when the session expires.
• ipAddress: (string) - The IP address of the device. (optional)
• userAgent: (string) - The user agent information of the device. (optional)

account:

• id: (string) - The unique identifier of the account.
• userId: (foregin-key: user.id) - The id of the user.
• accountId: (string) - The id of the account. (optional)
• providerId: (string) - The id of the provider. (optional)
• accessToken: (string) - The access token of the account. Returned by the provider. (optional)
• refreshToken: (string) - The refresh token of the account. Returned by the provider. (optional)
• expiresAt: (Date) - The time when the access token expires. (optional)
• password: (string) - The password of the account. Mainly used for email and password authentication. (optional)

Plugins Schema

Plugins can define their own tables in the database to store additional data. They can also add columns to the core tables to store additional data. For example, the two factor authentication plugin adds the following columns to the user table:

• twoFactorEnabled: Whether two factor authentication is enabled for the user.
• twoFactorSecret: The secret key used to generate TOTP codes.
• twoFactorBackupCodes: Encrypted backup codes for account recovery.

To add additional tables and columns to the database, you can run the migrate command provided by better auth. The migrate command will run the migrations defined by the plugins to add the required tables and columns to the database.

npx better-auth migrate

Extending the Schema

Better auth currently does not provide a direct method to extend the core schema. However, you can add extra columns to the database and manage them independently. For instance, to store additional user data, you can either create a new table or add columns to the user table.

We suggest creating a new table for additional data to keep the core tables clean and maintainable. You can link this new table to the user table using a foreign key.

Database Hooks

Database hooks allow you to define custom logic that can be executed during the lifecycle of core database operations in BetterAuth. You can create hooks for the following models: user, session, and account.

There are two types of hooks you can define:

1. Before Hook

• Purpose: This hook is called before the respective entity (user, session, or account) is created or updated.
• Behavior:
  • If the hook returns false, the operation will be aborted.
  • If it returns an object, that object will replace the original data.

2. After Hook

• Purpose: This hook is called after the respective entity is created or updated.
• Behavior: You can perform additional actions or modifications after the entity has been successfully created or updated.

Example Usage: add a before hook to the user model to split the name field into firstName and lastName fields.

const hooks: BetterAuthOptions['databaseHooks'] = {
  user: {
    create: {
      before: async (user) => {
          return {
              ...user,
              firstName: user.name.split(" ")[0],
              lastName: user.name.split(" ")[1]
          }
      },
    },
    update: {
      after: async (user) => {
          return {
              ...user,
              firstName: user.name.split(" ")[0],
              lastName: user.name.split(" ")[1]
          }
      },
    },
  },
};