Best Practices

Avoiding the N+1 Query Problem
1. The Problem
2. The Solution: Eager Loading
Selecting Only Needed Columns
Batch Processing for Large Tables
Connection Pool Management
1. Configure Idle Timeout
2. Disconnect on Shutdown
Transaction Best Practices
1. Keep Transactions Short
2. Use Generator Functions for Cleaner Code
Indexing Strategies
1. Use Composite Indexes for Common Query Patterns
2. Use Index Hints When Needed
Model Organization
Security
1. Never Use Raw SQL with User Input
2. Use raw() Sparingly

Avoiding the N+1 Query Problem

The N+1 query problem occurs when you load a list of records and then make a separate query for each record’s associations.

The Problem

// BAD: 1 query for posts + N queries for comments
const posts = await Post.find({ authorId: 1 });
for (const post of posts) {
  post.comments = await Comment.find({ postId: post.id });
}

The Solution: Eager Loading

Use .with() or .include() to load associations in a single query:

// GOOD: 1 query with JOINs
const posts = await Post.find({ authorId: 1 }).with('comments');
for (const post of posts) {
  console.log(post.comments); // Already loaded
}

You can load multiple associations at once:

const posts = await Post.find().with('author', 'comments');

Selecting Only Needed Columns

By default, Leoric selects all columns (SELECT *). When you only need specific columns, use .select():

// BAD: loads all columns including large text fields
const posts = await Post.find();

// GOOD: only loads what you need
const posts = await Post.find().select('id', 'title', 'createdAt');

This is especially important for tables with large TEXT or BLOB columns.

Batch Processing for Large Tables

When processing large tables, avoid loading all records into memory at once. Use an indexed, unique cursor instead of an ever-growing offset:

// BAD: loads everything into memory
const allPosts = await Post.find();

// GOOD: process an unfiltered table in stable primary-key order
const pageSize = 100;
let lastId;
while (true) {
  let query = Post.order('id').limit(pageSize);
  if (lastId != null) {
    query = query.where({ id: { $gt: lastId } });
  }

  const posts = await query;
  if (posts.length === 0) break;

  for (const post of posts) {
    // process each post
  }
  lastId = posts.at(-1).id;
}

This example traverses the whole table without additional filters, so a primary-key range scan is efficient. For a filtered batch, make the cursor and ordering match the query’s composite index. Put equality-filtered columns first, followed by the ordered cursor columns, and append the primary key as the final unique tie-breaker. For example, a query filtered by tenantId and status and paginated by id should normally have an index on (tenant_id, status, id). Check the resulting plan with your database’s EXPLAIN facility.

Use immutable cursor columns where possible. See the pagination guide for limit/offset, composite cursors, and window-function pagination.

Connection Pool Management

Configure Idle Timeout

For long-running applications, configure the idle timeout to prevent stale connections:

const realm = new Realm({
  host: 'localhost',
  database: 'my_app',
  idleTimeout: 30000, // 30 seconds
});

Disconnect on Shutdown

Always disconnect when your application shuts down:

process.on('SIGTERM', async () => {
  await realm.disconnect();
  process.exit(0);
});

Transaction Best Practices

Keep Transactions Short

// BAD: external API call inside transaction holds connection
await Bone.transaction(async ({ connection }) => {
  const user = await User.create({ name: 'Alice' }, { connection });
  const result = await fetch('https://api.example.com/notify'); // Slow!
  await AuditLog.create({ action: 'user_created' }, { connection });
});

// GOOD: move external calls outside the transaction
const user = await Bone.transaction(async ({ connection }) => {
  const user = await User.create({ name: 'Alice' }, { connection });
  await AuditLog.create({ action: 'user_created' }, { connection });
  return user;
});
await fetch('https://api.example.com/notify');

Use Generator Functions for Cleaner Code

// Generator functions auto-pass the connection
await Bone.transaction(function* () {
  const user = yield User.create({ name: 'Alice' });
  yield AuditLog.create({ action: 'user_created', userId: user.id });
});

Indexing Strategies

Use Composite Indexes for Common Query Patterns

If you frequently query with multiple conditions:

// If this is a common query pattern:
Post.find({ authorId: 1, status: 'published' }).order('createdAt', 'desc')

Consider adding a composite index: (author_id, status, created_at).

Use Index Hints When Needed

When the query optimizer makes a suboptimal choice:

Post.find({ authorId: 1 }).forceIndex('idx_author_created')

See Index Hints for more details.

Model Organization

Use Directory-Based Model Loading

const realm = new Realm({
  models: 'app/models',  // Auto-loads all models from directory
});

Define Associations in `initialize()`

class Post extends Bone {
  static initialize() {
    this.belongsTo('author', { Model: 'User' });
    this.hasMany('comments');
    this.hasMany('tags', { through: 'tagMaps' });
  }
}

Use TypeScript Decorators When Possible

class Post extends Bone {
  @Column({ primaryKey: true })
  id: bigint;

  @BelongsTo()
  author: User;

  @HasMany()
  comments: Comment[];
}

Security

Never Use Raw SQL with User Input

// BAD: SQL injection vulnerability
await realm.query(`SELECT * FROM posts WHERE title = '${userInput}'`);

// GOOD: parameterized query
await realm.query('SELECT * FROM posts WHERE title = ?', [userInput]);

// GOOD: use the ORM query interface
await Post.find({ title: userInput });

Use `raw()` Sparingly

The raw() function bypasses escaping. Only use it for SQL functions and expressions, never for user-provided values:

// GOOD: SQL function
await Post.update({ id: 1 }, { viewCount: raw('view_count + 1') });

// BAD: user input in raw()
await Post.find({ title: raw(userInput) }); // SQL injection!

This site is open source. Improve this page.