Knex Authentication: JWT, Sessions, and OAuth Integration

Understanding Authentication with Knex

Knex.js is a powerful, promise-based SQL query builder for Node.js that supports multiple database engines like PostgreSQL, MySQL, and SQLite. While it’s not an authentication library itself, it plays a central role in persisting user credentials, session data, and OAuth profiles. This tutorial walks you through building complete authentication flows — JWT, server-side sessions, and third-party OAuth — all backed by Knex migrations, queries, and best practices.

Why Authentication Patterns Matter

Choosing the right authentication strategy affects security, scalability, and user experience. JWT offers stateless, token-based auth ideal for APIs and microservices. Session-based auth keeps state on the server, simplifying revocation and CSRF protection. OAuth integration lets users sign in with Google, GitHub, or other providers, reducing password fatigue. Knex ties them all together by managing the underlying data safely and consistently across any supported database.

Project Setup and Database Schema

Start by creating a new Node.js project and installing the core dependencies:

npm init -y
npm install knex pg bcrypt jsonwebtoken express-session connect-session-knex passport passport-google-oauth20 dotenv

Create a knexfile.js to configure your database connection. We'll use PostgreSQL here, but the same concepts apply to MySQL or SQLite.

// knexfile.js
require('dotenv').config();

module.exports = {
  development: {
    client: 'pg',
    connection: {
      host: process.env.DB_HOST || 'localhost',
      database: process.env.DB_NAME || 'auth_demo',
      user: process.env.DB_USER || 'postgres',
      password: process.env.DB_PASSWORD || 'postgres',
    },
    migrations: { directory: './migrations' },
    seeds: { directory: './seeds' },
  },
};

Users Table Migration

Every auth system needs a users table. Include fields for email, hashed password, and OAuth provider details.

// migrations/001_create_users.js
exports.up = function(knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('email').unique().notNullable();
    table.string('password_hash'); // nullable for OAuth-only users
    table.string('oauth_provider'); // 'google', 'github', etc.
    table.string('oauth_id');       // provider's user ID
    table.timestamps(true, true);
  });
};

exports.down = function(knex) {
  return knex.schema.dropTableIfExists('users');
};

Sessions Table (for Session-Based Auth)

When using server-side sessions, we need a dedicated table to store session data. The connect-session-knex library uses this table automatically.

// migrations/002_create_sessions.js
exports.up = function(knex) {
  return knex.schema.createTable('sessions', (table) => {
    table.string('sid').primary();
    table.json('sess').notNullable();
    table.timestamp('expired').notNullable();
    table.index('expired', 'sessions_expired_index');
  });
};

exports.down = function(knex) {
  return knex.schema.dropTableIfExists('sessions');
};

Run the migrations to build the schema:

npx knex migrate:latest --env development

JWT Authentication with Knex

JSON Web Tokens enable stateless authentication. The server issues a signed token after login, and the client sends it in the Authorization header for protected routes. Knex handles user lookups and persistence.

Registration Endpoint

Hash passwords with bcrypt before storing them. Never save plain-text passwords.

const bcrypt = require('bcrypt');
const knex = require('./db'); // configured knex instance

async function register(req, res) {
  const { email, password } = req.body;
  if (!email || !password) {
    return res.status(400).json({ error: 'Email and password are required' });
  }

  try {
    const existingUser = await knex('users').where({ email }).first();
    if (existingUser) {
      return res.status(409).json({ error: 'Email already registered' });
    }

    const saltRounds = 12;
    const passwordHash = await bcrypt.hash(password, saltRounds);
    const [newUser] = await knex('users')
      .insert({ email, password_hash: passwordHash })
      .returning('*');

    res.status(201).json({ id: newUser.id, email: newUser.email });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Internal server error' });
  }
}

Login and Token Generation

Verify credentials, then generate a signed JWT with a payload containing the user ID and email. Set an expiration time and use a strong secret stored in environment variables.

const jwt = require('jsonwebtoken');

async function login(req, res) {
  const { email, password } = req.body;
  if (!email || !password) {
    return res.status(400).json({ error: 'Email and password are required' });
  }

  try {
    const user = await knex('users').where({ email }).first();
    if (!user) {
      return res.status(401).json({ error: 'Invalid credentials' });
    }

    const passwordMatches = await bcrypt.compare(password, user.password_hash);
    if (!passwordMatches) {
      return res.status(401).json({ error: 'Invalid credentials' });
    }

    const tokenPayload = { userId: user.id, email: user.email };
    const secret = process.env.JWT_SECRET;
    const options = { expiresIn: '2h' };

    const token = jwt.sign(tokenPayload, secret, options);
    res.json({ token });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Internal server error' });
  }
}

Middleware for Protected Routes

Verify the token on each request and attach user data to the request object for downstream handlers.

function authenticateJWT(req, res, next) {
  const authHeader = req.headers.authorization;
  if (!authHeader || !authHeader.startsWith('Bearer ')) {
    return res.status(401).json({ error: 'Missing or malformed token' });
  }

  const token = authHeader.split(' ')[1];
  try {
    const decoded = jwt.verify(token, process.env.JWT_SECRET);
    req.user = decoded; // { userId, email, iat, exp }
    next();
  } catch (err) {
    return res.status(403).json({ error: 'Invalid or expired token' });
  }
}

// Example protected route
app.get('/api/me', authenticateJWT, async (req, res) => {
  const user = await knex('users').where({ id: req.user.userId }).first();
  res.json({ id: user.id, email: user.email });
});

Session-Based Authentication with Knex

Sessions store the user’s authenticated state on the server, identified by a cookie. This model works well for traditional server-rendered apps and simplifies logout (just destroy the session). We’ll use express-session backed by Knex.

Setting Up express-session with Knex Store

Create a session store using connect-session-knex. This automatically reads and writes session data to the sessions table we created earlier.

const session = require('express-session');
const KnexSessionStore = require('connect-session-knex')(session);
const knex = require('./db');

const store = new KnexSessionStore({
  knex,
  tablename: 'sessions',
  sidfieldname: 'sid',
  createtable: false, // we already have the table
});

app.use(session({
  secret: process.env.SESSION_SECRET,
  resave: false,
  saveUninitialized: false,
  store,
  cookie: {
    httpOnly: true,
    secure: process.env.NODE_ENV === 'production',
    maxAge: 1000 * 60 * 60 * 2, // 2 hours
  },
}));

Login Route (Session)

Validate credentials, then set the user ID in the session. The session store persists this automatically via Knex.

async function loginSession(req, res) {
  const { email, password } = req.body;
  if (!email || !password) {
    return res.status(400).json({ error: 'Email and password are required' });
  }

  try {
    const user = await knex('users').where({ email }).first();
    if (!user || !(await bcrypt.compare(password, user.password_hash))) {
      return res.status(401).json({ error: 'Invalid credentials' });
    }

    req.session.userId = user.id;
    req.session.email = user.email;
    res.json({ message: 'Logged in successfully' });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Internal server error' });
  }
}

Session Authentication Middleware

function authenticateSession(req, res, next) {
  if (req.session && req.session.userId) {
    req.user = { userId: req.session.userId, email: req.session.email };
    return next();
  }
  res.status(401).json({ error: 'Please log in' });
}

// Protected route
app.get('/session/me', authenticateSession, async (req, res) => {
  const user = await knex('users').where({ id: req.user.userId }).first();
  res.json({ id: user.id, email: user.email });
});

Logout and Session Destruction

Logout simply destroys the session, which also removes the corresponding row from the sessions table.

app.post('/logout', (req, res) => {
  req.session.destroy((err) => {
    if (err) return res.status(500).json({ error: 'Logout failed' });
    res.clearCookie('connect.sid');
    res.json({ message: 'Logged out' });
  });
});

OAuth Integration with Knex

OAuth2 allows users to authenticate via Google, GitHub, etc. We'll use Passport.js, store user profiles with Knex, and then optionally issue a JWT or create a session for subsequent requests.

Setting up Passport with Google OAuth2.0

Install the required packages and configure the Google strategy. You need a Google Cloud project with OAuth credentials (client ID and secret).

const passport = require('passport');
const GoogleStrategy = require('passport-google-oauth20').Strategy;

passport.use(new GoogleStrategy({
  clientID: process.env.GOOGLE_CLIENT_ID,
  clientSecret: process.env.GOOGLE_CLIENT_SECRET,
  callbackURL: '/auth/google/callback',
},
async (accessToken, refreshToken, profile, done) => {
  try {
    // Find existing user by OAuth provider and ID
    let user = await knex('users')
      .where({ oauth_provider: 'google', oauth_id: profile.id })
      .first();

    if (!user) {
      // Create new user from Google profile
      const [newUser] = await knex('users')
        .insert({
          email: profile.emails[0].value,
          oauth_provider: 'google',
          oauth_id: profile.id,
          password_hash: null, // no password
        })
        .returning('*');
      user = newUser;
    }
    done(null, user);
  } catch (err) {
    done(err);
  }
}));

Serialize and Deserialize User with Knex

When using sessions, Passport needs to serialize the user ID into the session and deserialize it back to a full user object. We can use Knex for deserialization.

passport.serializeUser((user, done) => {
  done(null, user.id);
});

passport.deserializeUser(async (id, done) => {
  try {
    const user = await knex('users').where({ id }).first();
    done(null, user);
  } catch (err) {
    done(err);
  }
});

OAuth Routes

Define routes that start the OAuth flow and handle the callback. After successful authentication, you can issue a JWT or establish a session.

app.get('/auth/google',
  passport.authenticate('google', { scope: ['profile', 'email'] })
);

app.get('/auth/google/callback',
  passport.authenticate('google', { failureRedirect: '/login' }),
  (req, res) => {
    // User is now attached to req.user
    // Option A: Create a JWT and send it
    const tokenPayload = { userId: req.user.id, email: req.user.email };
    const token = jwt.sign(tokenPayload, process.env.JWT_SECRET, { expiresIn: '2h' });
    res.redirect(`/dashboard?token=${token}`);

    // Option B: Start a session
    // req.session.userId = req.user.id;
    // res.redirect('/dashboard');
  }
);

Combining OAuth with JWT or Sessions

After the OAuth callback, you have a verified user. From there, you can attach a JWT to the response, store it in a cookie, or use the existing session. The Knex-backed user record remains the source of truth regardless of the token mechanism you choose downstream.

Best Practices for Secure Authentication

• Hash passwords securely: Use bcrypt with a salt rounds value of at least 10 (12 recommended). Never store plain-text passwords.
• Protect secrets: Store JWT secrets, session secrets, and OAuth credentials in environment variables (.env). Never commit them to version control.
• Enforce HTTPS: In production, always serve over HTTPS to prevent token and cookie interception.
• Set reasonable token lifetimes: JWT should expire (e.g., 15 minutes to 2 hours). Implement refresh tokens if longer sessions are needed.
• Use secure cookie flags: For session cookies, set httpOnly (prevents JavaScript access), secure (HTTPS only), and sameSite to mitigate CSRF.
• Validate OAuth redirect URIs: Only allow whitelisted callback URLs to prevent open redirect attacks.
• Implement CSRF protection for sessions: Use libraries like csurf or double-submit cookie patterns when using session-based auth in browsers.
• Handle database errors gracefully: Use try/catch blocks and avoid leaking stack traces to the client.
• Keep business logic separate from routes: Extract authentication functions into services or modules for easier testing and maintenance.
• Run migrations in order: Ensure the sessions table exists before starting the session store, and always version your schema with Knex migrations.

Conclusion

Knex provides a unified, database-agnostic way to manage authentication data, whether you’re storing hashed passwords, OAuth profiles, or session records. By combining it with JWT for stateless APIs, server-side sessions for traditional apps, and Passport strategies for social login, you can build flexible, secure authentication systems that scale with your application. The patterns shown here give you a solid foundation — remember to always prioritize security, keep secrets out of your codebase, and choose the authentication model that best fits your architecture.