Library

Objection.js

Overview

Objection.js is an SQL-friendly ORM for Node.js designed to stay out of your way and make it as easy as possible to use the full power of SQL and the underlying database engine. Built on the Knex.js query builder, it provides both the benefits of an SQL query builder and a powerful set of tools for working with relations. More accurately described as a "relational query builder" rather than a traditional ORM, it uses SQL as a query language while making common operations easy and enjoyable. It's a practical solution that perfectly balances direct SQL control with the benefits of object mapping.

Details

Objection.js 2025 edition maintains its solid position as a mature library that achieves the ideal balance between SQL transparency and ORM convenience in the Node.js ecosystem. Built on the robust Knex.js query builder foundation, it fully supports PostgreSQL, MySQL, and SQLite, enabling development that leverages each database's characteristics. Features include ES6 class-based model definitions, JSON schema validation, and flexible relation management through graph operations. Advanced SQL features like complex JOINs, subqueries, and window functions can be naturally written in JavaScript/TypeScript, with a design philosophy that directly leverages SQL knowledge. Rich relation definitions and query optimization capabilities make it suitable for enterprise-level application development.

Key Features

• SQL-first design: SQL transparency and direct control through Knex.js foundation
• Rich relation features: Abundant relations including hasMany, belongsTo, manyToMany
• JSON schema validation: Automatic validation functionality during model definition
• Graph operations: Efficient manipulation of complex nested structure data
• Complete TypeScript support: Type safety and excellent developer experience
• Lightweight and high performance: High performance with minimal overhead

Pros and Cons

Pros

• Highly transparent architecture that directly leverages SQL knowledge
• Stability and database compatibility through robust Knex.js foundation
• Rich relation definitions and intuitive graph operation APIs
• Type safety and excellent developer experience with TypeScript support
• High performance and low memory usage through lightweight design
• Detailed documentation and rich community support

Cons

• Requires SQL knowledge, high learning cost for beginners
• Limited automation features like ActiveRecord patterns
• Migration functionality depends on Knex.js, requiring separate learning
• Manual query optimization needed for complex relations
• Limited type inference compared to modern Prisma or Drizzle
• No support for schema-first approaches

Reference Pages

• Objection.js Official Site
• Objection.js GitHub Repository
• Knex.js Documentation

Code Examples

Installation and Project Setup

# Install Objection.js, Knex.js, and database driver
npm install objection knex

# For PostgreSQL
npm install pg
npm install --save-dev @types/pg

# For MySQL
npm install mysql2

# For SQLite
npm install sqlite3

# TypeScript type definitions
npm install --save-dev @types/node

// knexfile.js - Database configuration
module.exports = {
  development: {
    client: 'postgresql',
    connection: {
      host: 'localhost',
      database: 'myapp_development',
      user: 'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations',
      directory: './migrations'
    },
    seeds: {
      directory: './seeds'
    }
  },
  
  production: {
    client: 'postgresql',
    connection: process.env.DATABASE_URL,
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }
};

// app.js - Basic setup
const Knex = require('knex');
const { Model } = require('objection');

const knexConfig = require('./knexfile');
const knex = Knex(knexConfig.development);

// Pass Knex instance to Objection
Model.knex(knex);

Basic Model Definition

// models/Person.js
const { Model } = require('objection');

class Person extends Model {
  static get tableName() {
    return 'persons';
  }

  static get idColumn() {
    return 'id';
  }

  // JSON schema validation
  static get jsonSchema() {
    return {
      type: 'object',
      required: ['firstName', 'lastName'],
      
      properties: {
        id: { type: 'integer' },
        firstName: { type: 'string', minLength: 1, maxLength: 255 },
        lastName: { type: 'string', minLength: 1, maxLength: 255 },
        email: { type: 'string', format: 'email' },
        age: { type: 'integer', minimum: 0, maximum: 150 },
        address: {
          type: 'object',
          properties: {
            street: { type: 'string' },
            city: { type: 'string' },
            zipCode: { type: 'string' }
          }
        }
      }
    };
  }

  // Relation definitions
  static get relationMappings() {
    return {
      pets: {
        relation: Model.HasManyRelation,
        modelClass: require('./Pet'),
        join: {
          from: 'persons.id',
          to: 'pets.ownerId'
        }
      },

      movies: {
        relation: Model.ManyToManyRelation,
        modelClass: require('./Movie'),
        join: {
          from: 'persons.id',
          through: {
            from: 'persons_movies.personId',
            to: 'persons_movies.movieId'
          },
          to: 'movies.id'
        }
      },

      mother: {
        relation: Model.BelongsToOneRelation,
        modelClass: Person,
        join: {
          from: 'persons.motherId',
          to: 'persons.id'
        }
      },

      children: {
        relation: Model.HasManyRelation,
        modelClass: Person,
        join: {
          from: 'persons.id',
          to: 'persons.motherId'
        }
      }
    };
  }

  // Instance methods
  fullName() {
    return `${this.firstName} ${this.lastName}`;
  }

  // Virtual attributes
  static get virtualAttributes() {
    return ['fullName'];
  }
}

module.exports = Person;

// TypeScript model definition
import { Model, ModelObject, RelationMappings } from 'objection';

export class Person extends Model {
  id!: number;
  firstName!: string;
  lastName!: string;
  email?: string;
  age?: number;
  address?: {
    street: string;
    city: string;
    zipCode: string;
  };

  // Relation type definitions
  pets?: Pet[];
  movies?: Movie[];
  mother?: Person;
  children?: Person[];

  static tableName = 'persons';

  static jsonSchema = {
    type: 'object',
    required: ['firstName', 'lastName'],
    properties: {
      id: { type: 'integer' },
      firstName: { type: 'string', minLength: 1, maxLength: 255 },
      lastName: { type: 'string', minLength: 1, maxLength: 255 },
      email: { type: 'string', format: 'email' },
      age: { type: 'integer', minimum: 0, maximum: 150 }
    }
  };

  static relationMappings: RelationMappings = {
    pets: {
      relation: Model.HasManyRelation,
      modelClass: () => Pet,
      join: {
        from: 'persons.id',
        to: 'pets.ownerId'
      }
    }
  };

  fullName(): string {
    return `${this.firstName} ${this.lastName}`;
  }
}

export type PersonShape = ModelObject<Person>;

CRUD Operations and Queries

const Person = require('./models/Person');

// CREATE - Creating new records
async function createPerson() {
  const person = await Person.query().insert({
    firstName: 'John',
    lastName: 'Doe',
    email: '[email protected]',
    age: 30,
    address: {
      street: '123 Main St',
      city: 'New York',
      zipCode: '10001'
    }
  });
  
  console.log('Created user:', person);
  return person;
}

// READ - Search and filtering
async function findPersons() {
  // Get all records
  const allPersons = await Person.query();
  
  // Conditional search
  const adults = await Person.query()
    .where('age', '>=', 18)
    .orderBy('lastName')
    .orderBy('firstName');
  
  // Complex conditions
  const filteredPersons = await Person.query()
    .where('age', '>', 25)
    .where('email', 'like', '%@example.com')
    .orWhere(builder => {
      builder
        .where('firstName', 'like', 'John%')
        .andWhere('age', '<', 40);
    });
  
  // JSON column search
  const nyPersons = await Person.query()
    .where('address:city', 'New York');
  
  // Pagination
  const page = await Person.query()
    .page(0, 10); // Page 0, 10 records per page
    
  console.log('Total count:', page.total);
  console.log('Results:', page.results);
  
  return { allPersons, adults, filteredPersons };
}

// UPDATE - Updates
async function updatePerson(personId) {
  // Single record update
  const updatedPerson = await Person.query()
    .findById(personId)
    .patch({
      age: 31,
      'address.city': 'Los Angeles'
    });
  
  // Multiple record update
  const updatedCount = await Person.query()
    .where('age', '<', 18)
    .patch({ status: 'minor' });
  
  // Upsert (create if not exists, update if exists)
  const upsertedPerson = await Person.query()
    .upsertGraph({
      id: personId,
      firstName: 'Jane',
      lastName: 'Doe',
      email: '[email protected]'
    });
  
  return { updatedPerson, updatedCount };
}

// DELETE - Deletion
async function deletePerson(personId) {
  // Delete by ID
  const deletedCount = await Person.query()
    .deleteById(personId);
  
  // Conditional deletion
  const deletedInactiveCount = await Person.query()
    .where('lastLoginAt', '<', new Date(Date.now() - 365 * 24 * 60 * 60 * 1000))
    .delete();
  
  return { deletedCount, deletedInactiveCount };
}

// Execution example
async function runCrudExamples() {
  try {
    const newPerson = await createPerson();
    const searchResults = await findPersons();
    const updateResults = await updatePerson(newPerson.id);
    
    console.log('CRUD operations completed');
  } catch (error) {
    console.error('Error:', error);
  }
}

Relation Operations and Graph Queries

// Finding with relations
async function findWithRelations() {
  // One-to-many relations
  const personsWithPets = await Person.query()
    .withGraphFetched('pets')
    .where('age', '>', 20);

  // Deep nesting
  const personsWithMoviesAndActors = await Person.query()
    .withGraphFetched('[pets, movies.[actors, director]]')
    .findById(1);

  // Relations with filtering
  const personWithRecentMovies = await Person.query()
    .withGraphFetched('movies(recentMovies)')
    .findById(1);

  // Modifier definitions
  Person.modifiers = {
    recentMovies(builder) {
      builder.where('releaseDate', '>', '2020-01-01');
    }
  };

  return { personsWithPets, personsWithMoviesAndActors };
}

// Graph insertion, updates, and deletion
async function graphOperations() {
  // Create with related data
  const personWithRelations = await Person.query()
    .upsertGraph({
      firstName: 'Alice',
      lastName: 'Smith',
      pets: [
        {
          name: 'Rex',
          species: 'dog'
        },
        {
          name: 'Whiskers',
          species: 'cat'
        }
      ],
      movies: [
        {
          '#dbRef': 1 // Associate with existing movie ID 1
        },
        {
          name: 'New Movie',
          releaseDate: '2024-01-01'
        }
      ]
    });

  // Partial graph update
  await Person.query()
    .upsertGraph({
      id: 1,
      firstName: 'Updated Name',
      pets: [
        {
          id: 1,
          name: 'Updated Pet Name'
        },
        {
          // No ID = create new
          name: 'New Pet',
          species: 'bird'
        }
      ]
    });

  // Relation-only operations
  const person = await Person.query().findById(1);
  
  // Add pet
  await person.$relatedQuery('pets')
    .insert({
      name: 'Hamster',
      species: 'hamster'
    });

  // Add movie association (many-to-many)
  await person.$relatedQuery('movies')
    .relate(movieId); // Existing movie ID

  // Remove relation
  await person.$relatedQuery('pets')
    .where('species', 'hamster')
    .delete();

  return personWithRelations;
}

// Aggregation and grouping
async function aggregationQueries() {
  // Basic aggregation
  const stats = await Person.query()
    .select('age')
    .count('id as personCount')
    .avg('age as averageAge')
    .min('age as minAge')
    .max('age as maxAge')
    .groupBy('age')
    .having('personCount', '>', 1);

  // Aggregation with relations
  const personsWithPetCount = await Person.query()
    .select('persons.*')
    .count('pets.id as petCount')
    .leftJoin('pets', 'persons.id', 'pets.ownerId')
    .groupBy('persons.id');

  // Subquery aggregation
  const personsWithStats = await Person.query()
    .select('*')
    .select(
      Person.relatedQuery('pets')
        .count()
        .as('petCount')
    )
    .select(
      Person.relatedQuery('movies')
        .avg('rating')
        .as('averageMovieRating')
    );

  return { stats, personsWithPetCount, personsWithStats };
}

Advanced Queries and Raw SQL

// Using Raw SQL
async function rawSqlQueries() {
  // Raw SQL expressions
  const personsWithComputedField = await Person.query()
    .select('*')
    .select(Person.raw('UPPER(first_name || \' \' || last_name) as full_name_upper'))
    .where(Person.raw('age + ? > ?', [5, 30]));

  // Raw SQL queries
  const customQuery = await Person.knex().raw(`
    SELECT p.first_name, p.last_name, COUNT(pets.id) as pet_count
    FROM persons p
    LEFT JOIN pets ON p.id = pets.owner_id
    WHERE p.age > ?
    GROUP BY p.id, p.first_name, p.last_name
    HAVING COUNT(pets.id) > ?
  `, [25, 1]);

  // Complex queries with WITH clause
  const withQuery = await Person.query()
    .with('adult_persons', Person.query()
      .select('*')
      .where('age', '>=', 18)
    )
    .select('*')
    .from('adult_persons as ap')
    .join('pets', 'ap.id', 'pets.owner_id');

  return { personsWithComputedField, customQuery, withQuery };
}

// Window functions and CTE
async function advancedSqlFeatures() {
  // Window functions
  const rankedPersons = await Person.query()
    .select('*')
    .select(Person.raw('ROW_NUMBER() OVER (ORDER BY age DESC) as age_rank'))
    .select(Person.raw('RANK() OVER (PARTITION BY LEFT(last_name, 1) ORDER BY age) as name_group_rank'));

  // Recursive CTE (hierarchical data)
  const familyTree = await Person.knex().raw(`
    WITH RECURSIVE family_tree AS (
      -- Base case: people without parents (roots)
      SELECT id, first_name, last_name, mother_id, 0 as level
      FROM persons
      WHERE mother_id IS NULL
      
      UNION ALL
      
      -- Recursive case: add children
      SELECT p.id, p.first_name, p.last_name, p.mother_id, ft.level + 1
      FROM persons p
      INNER JOIN family_tree ft ON p.mother_id = ft.id
    )
    SELECT * FROM family_tree
    ORDER BY level, last_name, first_name
  `);

  return { rankedPersons, familyTree };
}

Transaction Management and Error Handling

// Transaction management
async function transactionExamples() {
  // Basic transaction
  const result = await Person.transaction(async (trx) => {
    // Create new person
    const person = await Person.query(trx)
      .insert({
        firstName: 'Transaction',
        lastName: 'Test',
        email: '[email protected]'
      });

    // Add pet
    const pet = await person.$relatedQuery('pets', trx)
      .insert({
        name: 'Transaction Pet',
        species: 'dog'
      });

    // Rollback on some condition
    if (person.firstName === 'Failure') {
      throw new Error('Intentional failure');
    }

    return { person, pet };
  });

  console.log('Transaction successful:', result);
}

// Error handling
async function errorHandlingExamples() {
  try {
    // Validation error
    await Person.query().insert({
      firstName: '', // Required field is empty
      lastName: 'Test'
    });
  } catch (error) {
    if (error instanceof ValidationError) {
      console.log('Validation error:', error.data);
    }
  }

  try {
    // Unique constraint error
    await Person.query().insert({
      firstName: 'Duplicate',
      lastName: 'User',
      email: '[email protected]' // Existing email
    });
  } catch (error) {
    if (error.code === '23505') { // PostgreSQL unique violation
      console.log('Unique constraint violation:', error.detail);
    }
  }

  // Custom error handling
  const handleDbError = (error) => {
    switch (error.code) {
      case '23505': // Unique violation
        throw new Error('This email address is already in use');
      case '23503': // Foreign key violation
        throw new Error('Referenced data does not exist');
      case '23502': // Not null violation
        throw new Error('Required fields are not filled');
      default:
        throw error;
    }
  };

  try {
    await Person.query().insert(invalidData);
  } catch (error) {
    handleDbError(error);
  }
}

// Execution example
async function runAdvancedExamples() {
  try {
    await findWithRelations();
    await graphOperations();
    await aggregationQueries();
    await rawSqlQueries();
    await transactionExamples();
  } catch (error) {
    console.error('Error in advanced operations:', error);
  }
}

Performance Optimization and Best Practices

// Solving N+1 problems
async function optimizedQueries() {
  // ❌ Pattern causing N+1 problem
  const persons = await Person.query();
  for (const person of persons) {
    person.pets = await person.$relatedQuery('pets'); // N queries
  }

  // ✅ Optimized pattern
  const personsWithPets = await Person.query()
    .withGraphFetched('pets');

  // Eager loading with filtering
  const optimizedQuery = await Person.query()
    .withGraphFetched('[pets(onlyDogs), movies(recentMovies)]')
    .modifiers({
      onlyDogs(builder) {
        builder.where('species', 'dog');
      },
      recentMovies(builder) {
        builder.where('releaseDate', '>', '2020-01-01');
      }
    });

  return personsWithPets;
}

// Database connection management
function connectionManagement() {
  // Connection pool configuration
  const knex = require('knex')({
    client: 'pg',
    connection: {
      host: 'localhost',
      user: 'username',
      password: 'password',
      database: 'myapp'
    },
    pool: {
      min: 2,         // Minimum connections
      max: 10,        // Maximum connections
      acquireTimeoutMillis: 30000,  // Acquire timeout
      idleTimeoutMillis: 600000     // Idle timeout
    },
    acquireConnectionTimeout: 60000,
    debug: false // false in production
  });

  // Proper cleanup
  process.on('SIGINT', async () => {
    console.log('Shutting down application...');
    await knex.destroy();
    process.exit(0);
  });
}

// Query debugging and logging
function debuggingAndLogging() {
  // Enable query logging
  const knex = require('knex')({
    client: 'pg',
    connection: connectionConfig,
    debug: true // true only in development
  });

  // Custom query logging
  knex.on('query', (queryData) => {
    console.log('SQL:', queryData.sql);
    console.log('Bindings:', queryData.bindings);
  });

  // Execution time monitoring
  knex.on('query-response', (response, queryData, builder) => {
    console.log(`Query took ${Date.now() - queryData.__queryStartTime}ms`);
  });

  // Error logging
  knex.on('query-error', (error, queryData) => {
    console.error('Query error:', error);
    console.error('Failed query:', queryData.sql);
  });
}

// Custom validation
class PersonWithCustomValidation extends Person {
  async $beforeInsert(queryContext) {
    await super.$beforeInsert(queryContext);
    
    // Custom validation
    if (this.age < 0) {
      throw new ValidationError({
        type: 'ModelValidation',
        message: 'Age must be 0 or greater'
      });
    }

    // Automatic timestamps
    this.createdAt = new Date().toISOString();
    this.updatedAt = new Date().toISOString();
  }

  async $beforeUpdate(opt, queryContext) {
    await super.$beforeUpdate(opt, queryContext);
    this.updatedAt = new Date().toISOString();
  }

  // Async validation
  async $afterValidate(json, opt) {
    await super.$afterValidate(json, opt);
    
    // Email uniqueness check
    if (json.email) {
      const existing = await Person.query()
        .where('email', json.email)
        .whereNot('id', this.id || 0)
        .first();
        
      if (existing) {
        throw new ValidationError({
          type: 'UniqueViolation',
          message: 'This email address is already in use'
        });
      }
    }
  }
}