GLM 4.5 Air

Better Auth SQLite Schema Error: Complete Fix Guide

Fix better-auth SQLite schema generation errors in Nuxt projects. Complete guide to resolving database introspection issues with generic OAuth plugin. Step-by-step solutions included.

#better-auth#cli-error-fix#database-schema#nuxt-auth#oauth-configuration#sqlite-database
Question

Better Auth Schema Generation Error with SQLite and Generic OAuth in Nuxt

I’m setting up my Nuxt project with better-auth to authenticate against my company’s OAuth2/OIDC provider using the genericOAuth plugin. When running the CLI command to generate the database schema, I encounter an error indicating that no tables are found.

Problem Description

When executing npx @better-auth/cli@latest generate --config ./server/utils/auth.ts, the database file is created but remains empty, and no schema is generated. The error message indicates a problem with database introspection.

Error Message

npx @better-auth/cli@latest generate --config ./server/utils/auth.ts
⠋ preparing schema...file:///Users/taasasi8/.npm/_npx/05eee8cc65087e98/node_modules/better-auth/dist/shared/better-auth.D-L3RQ6y.mjs:1143
  const tableMetadata = await db.introspection.getTables();
                                               ^

TypeError: Cannot read properties of undefined (reading 'getTables')
    at getMigrations (file:///Users/taasasi8/.npm/_npx/05eee8cc65087e98/node_modules/better-auth/dist/shared/better-auth.D-L3RQ6y.mjs:1143:48)
    at async generateMigrations (file:///Users/taasasi8/.npm/_npx/05eee8cc65087e98/node_modules/@better-auth/cli/dist/index.mjs:2660:33)
    at async Command.generateAction (file:///Users/taasasi8/.npm/_npx/05eee8cc65087e98/node_modules/@better-auth/cli/dist/index.mjs:2720:18)

Configuration

Here’s my server/utils/auth.ts configuration:

typescript
import { betterAuth } from "better-auth"
import { genericOAuth } from "better-auth/plugins"
import Database from "better-sqlite3"

export const auth = betterAuth({
  database: {
    provider: "sqlite",  // This tells Better Auth it's SQLite
    type: "sqlite",      // Add this line to be explicit
    db: new Database("server/database/auth/local.db")
  },
  plugins: [
    genericOAuth({
      config: [
        {
          providerId: "sso",
          clientId: process.env["NUXT_OAUTH_SSO_CLIENT_ID"] || '',
          clientSecret: process.env["NUXT_OAUTH_SSO_CLIENT_SECRET"] || '',
          discoveryUrl: process.env["NUXT_OAUTH_SSO_CONFIGURATION_URL"] || '',
        },
      ]
    })
  ]
})

Question

What could be causing the schema generation error with better-auth, SQLite, and generic OAuth in my Nuxt project? How can I properly set up the database schema for authentication with my company’s OAuth provider?

Technical Details

  • Framework: Nuxt
  • Authentication: better-auth with genericOAuth plugin
  • Database: SQLite
  • Error: TypeError when trying to introspect database tables
GLM 4.5 Air

Better Auth Schema Generation Error with SQLite and Generic OAuth in Nuxt

Brief Answer

The error occurs because better-auth’s CLI tool is having trouble introspecting your SQLite database, likely due to improper database configuration or version compatibility issues. To fix this, you’ll need to ensure your SQLite database is properly initialized, use the correct database adapter syntax, and verify that all dependencies are properly installed and compatible with each other.

Contents

Understanding the Error

The error message indicates that db.introspection is undefined when the CLI tries to call getTables(). This suggests that the database connection or configuration isn’t properly set up for introspection.

The error occurs in the getMigrations function, which is part of the schema generation process. When better-auth attempts to introspect the database to determine existing tables and schema, it fails because the database connection object doesn’t have the expected introspection methods.

This is a common issue when working with SQLite through better-auth, as SQLite requires specific handling compared to other database systems like PostgreSQL or MySQL.

Database Configuration Issues

Your configuration has several potential issues that could be causing the introspection failure:

typescript
export const auth = betterAuth({
  database: {
    provider: "sqlite",  // This tells Better Auth it's SQLite
    type: "sqlite",      // Add this line to be explicit
    db: new Database("server/database/auth/local.db")
  },
  // ...
})

The main issues with this configuration are:

  1. Database Adapter Syntax: The db property expects a specific database adapter, not a raw SQLite connection. Better-auth needs its own database adapter that implements the necessary introspection methods.

  2. Missing Database Adapter: You’re using the raw better-sqlite3 library directly, but better-auth requires its own database adapter that can properly introspect the schema.

  3. Database Path: The path server/database/auth/local.db might not exist or the parent directories might not be created, leading to connection issues.

  4. Version Compatibility: There might be version mismatches between better-auth, better-sqlite3, and the CLI tool.

Solutions for SQLite Schema Generation

Solution 1: Use the Better Auth SQLite Adapter

Better-auth provides its own database adapters for different database systems. For SQLite, you should use the better-sqlite3 adapter:

bash
npm install better-auth @better-auth/sqlite

Then update your configuration:

typescript
import { betterAuth } from "better-auth"
import { genericOAuth } from "better-auth/plugins"
import Database from "better-sqlite3"
import { sqliteAdapter } from "@better-auth/sqlite"

export const auth = betterAuth({
  database: sqliteAdapter(new Database("server/database/auth/local.db")),
  plugins: [
    genericOAuth({
      config: [
        {
          providerId: "sso",
          clientId: process.env["NUXT_OAUTH_SSO_CLIENT_ID"] || '',
          clientSecret: process.env["NUXT_OAUTH_SSO_CLIENT_SECRET"] || '',
          discoveryUrl: process.env["NUXT_OAUTH_SSO_CONFIGURATION_URL"] || '',
        },
      ]
    })
  ]
})

Solution 2: Ensure Database Directory Exists

Before running the CLI command, ensure that the database directory exists:

typescript
import { mkdirSync, existsSync } from "fs"
import { join } from "path"
import { betterAuth } from "better-auth"
import { genericOAuth } from "better-auth/plugins"
import Database from "better-sqlite3"
import { sqliteAdapter } from "@better-auth/sqlite"

// Ensure database directory exists
const dbPath = join(process.cwd(), "server/database/auth")
if (!existsSync(dbPath)) {
  mkdirSync(dbPath, { recursive: true })
}

export const auth = betterAuth({
  database: sqliteAdapter(new Database(join(dbPath, "local.db"))),
  plugins: [
    genericOAuth({
      config: [
        {
          providerId: "sso",
          clientId: process.env["NUXT_OAUTH_SSO_CLIENT_ID"] || '',
          clientSecret: process.env["NUXT_OAUTH_SSO_CLIENT_SECRET"] || '',
          discoveryUrl: process.env["NUXT_OAUTH_SSO_CONFIGURATION_URL"] || '',
        },
      ]
    })
  ]
})

Solution 3: Update Dependencies

Ensure you’re using compatible versions of all packages. Check the latest versions in the better-auth documentation and update accordingly:

bash
npm install better-auth@latest @better-auth/sqlite@latest better-sqlite3@latest

Solution 4: Manual Schema Generation

If the CLI tool continues to fail, you can manually create the database schema:

  1. First, create an empty database file with the correct path:
typescript
import { betterAuth } from "better-auth"
import { genericOAuth } from "better-auth/plugins"
import Database from "better-sqlite3"
import { sqliteAdapter } from "@better-auth/sqlite"

// Ensure database directory exists
const dbPath = join(process.cwd(), "server/database/auth")
if (!existsSync(dbPath)) {
  mkdirSync(dbPath, { recursive: true })
}

// Create database if it doesn't exist
const db = new Database(join(dbPath, "local.db"))
db.exec("CREATE TABLE IF NOT EXISTS _better_auth (key TEXT PRIMARY KEY, value TEXT)")
db.close()

export const auth = betterAuth({
  database: sqliteAdapter(new Database(join(dbPath, "local.db"))),
  plugins: [
    genericOAuth({
      config: [
        {
          providerId: "sso",
          clientId: process.env["NUXT_OAUTH_SSO_CLIENT_ID"] || '',
          clientSecret: process.env["NUXT_OAUTH_SSO_CLIENT_SECRET"] || '',
          discoveryUrl: process.env["NUXT_OAUTH_SSO_CONFIGURATION_URL"] || '',
        },
      ]
    })
  ]
})
  1. Then run the CLI command again:
bash
npx @better-auth/cli@latest generate --config ./server/utils/auth.ts

Alternative Approaches

Using Prisma with SQLite

If you continue to face issues with the SQLite adapter, consider using Prisma as an intermediary:

  1. Install Prisma:
bash
npm install prisma @prisma/client
npx prisma init
  1. Set up your schema file (schema.prisma):
prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

model Account {
  id                String   @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String?
  access_token      String?
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?
  session_state     String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}
  1. Generate Prisma client and run migrations:
bash
npx prisma migrate dev --name init
  1. Update your better-auth configuration to use Prisma:
typescript
import { betterAuth } from "better-auth"
import { genericOAuth } from "better-auth/plugins"
import { PrismaAdapter } from "@better-auth/adapters-prisma"
import { PrismaClient } from "@prisma/client"

const prisma = new PrismaClient()

export const auth = betterAuth({
  database: PrismaAdapter(prisma, {
    provider: "sqlite"
  }),
  plugins: [
    genericOAuth({
      config: [
        {
          providerId: "sso",
          clientId: process.env["NUXT_OAUTH_SSO_CLIENT_ID"] || '',
          clientSecret: process.env["NUXT_OAUTH_SSO_CLIENT_SECRET"] || '',
          discoveryUrl: process.env["NUXT_OAUTH_SSO_CONFIGURATION_URL"] || '',
        },
      ]
    })
  ]
})

Best Practices for Better Auth with SQLite

  1. Use the Correct Adapter: Always use the official database adapter for your database system. For SQLite, use @better-auth/sqlite.

  2. Handle File Paths Properly: Ensure your database file path is correct and the directory exists before creating the connection.

  3. Check Version Compatibility: Regularly check for updates and ensure all packages are compatible with each other.

  4. Environment Variables: Use environment variables for sensitive data like database paths and OAuth credentials.

  5. Database Migrations: Implement a proper migration strategy to handle schema changes as your application evolves.

  6. Error Handling: Add proper error handling for database operations to catch issues early.

  7. Testing: Test your authentication flow thoroughly in different environments.

Verification Steps

After implementing one of the solutions above, follow these steps to verify everything is working:

  1. Check that the database file exists and has been created:
bash
ls -la server/database/auth/
  1. Verify the database contains the expected tables:
bash
sqlite3 server/database/auth/local.db ".tables"
  1. Run the CLI command again:
bash
npx @better-auth/cli@latest generate --config ./server/utils/auth.ts

  1. If it still fails, check the console for more detailed error messages.

  2. Test the authentication flow in your application to ensure users can register, log in, and authenticate with your OAuth provider.

In conclusion, the schema generation error with better-auth, SQLite, and generic OAuth typically stems from incorrect database configuration or missing adapters. By using the proper SQLite adapter, ensuring your database directory exists, and maintaining compatible package versions, you should be able to resolve the issue and set up a working authentication system for your Nuxt application.

How to configure better-auth with Prisma adapter instead of SQLite?What are the best practices for setting up OAuth2/OIDC providers with better-auth?How to handle database migrations when switching from SQLite to PostgreSQL with better-auth?What are the common authentication errors in Nuxt projects and how to fix them?How to implement session management with better-auth in a Nuxt application?What are the security considerations when using better-auth with custom OAuth providers?
Ask GLM 4.5 Air