Best Practices - ServiceSQL

This guide covers best practices for using ServiceSQL efficiently, including performance optimization, common patterns, and how to avoid the N+1 query problem.

Performance Optimization

Use Eager Loading to Avoid N+1 Queries

The N+1 query problem occurs when you load related data in a loop, causing one query for the main data and N additional queries for related data.

❌ Bad: N+1 Problem

// This executes 1 + N queries!
const posts = db.table("Posts").get();

posts.each(post => {
  // Separate query for EACH post
  const author = db.table("Users")
    .where("id", post.user_id)
    .first();
  Logger.log(`${post.title} by ${author.name}`);
});

✅ Good: Eager Loading

// This executes only 2 queries!
const posts = Post.with("author").get();

posts.each(post => {
  // Author is already loaded
  Logger.log(`${post.title} by ${post.author.name}`);
});

Eager Loading Multiple Relations

// Load multiple relationships efficiently
const posts = Post.with("author", "comments", "tags").get();

// Even nested relationships
const posts = Post.with("author", "comments.author", "tags").get();

posts.each(post => {
  Logger.log(`Post: ${post.title}`);
  Logger.log(`Author: ${post.author.name}`);
  Logger.log(`Comments: ${post.comments.count()}`);
  post.comments.each(comment => {
    Logger.log(`  - ${comment.author.name}: ${comment.text}`);
  });
  Logger.log(`Tags: ${post.tags.pluck("name").all().join(", ")}`);
});

Always use with() when accessing related data. It transforms N+1 queries into just 2-3 queries regardless of the number of records.

Select Only Necessary Columns

Don’t fetch data you don’t need:

❌ Bad: Fetching Everything

// Loads all columns
const users = db.table("Users").get();
const names = users.map(u => u.name);

✅ Good: Select Specific Columns

// Only loads needed columns
const users = db.table("Users")
  .select("id", "name")
  .get();
const names = users.pluck("name").all();

Use Pagination for Large Datasets

❌ Bad: Loading Everything

// Can timeout with large datasets
const allUsers = db.table("Users").get();

✅ Good: Use Pagination

// Loads data in manageable chunks
const result = db.table("Users")
  .orderBy("id")
  .paginate(100, page);

Prefer whereIn Over Multiple OR Conditions

❌ Bad: Multiple ORs

const users = db.table("Users")
  .where("country", "ES")
  .orWhere("country", "MX")
  .orWhere("country", "AR")
  .orWhere("country", "CO")
  .get();

✅ Good: Use whereIn

const users = db.table("Users")
  .whereIn("country", ["ES", "MX", "AR", "CO"])
  .get();

Use Aggregations Instead of Loading and Counting

❌ Bad: Load Then Count

const users = db.table("Users")
  .where("status", "active")
  .get();
const count = users.count();

✅ Good: Use count()

const count = db.table("Users")
  .where("status", "active")
  .count();

Query Builder Best Practices

Chain Methods for Readability

// ✅ Clean, readable query
const activeUsers = db.table("Users")
  .where("status", "active")
  .where("age", ">=", 18)
  .where("verified", true)
  .orderBy("created_at", "desc")
  .limit(50)
  .get();

Use Scopes for Reusable Logic

❌ Bad: Repeated Queries

// Repeated logic
const active1 = db.table("Users")
  .where("status", "active")
  .where("verified", true)
  .get();

const active2 = db.table("Users")
  .where("status", "active")
  .where("verified", true)
  .where("role", "admin")
  .get();

✅ Good: Use Scopes

class User extends Model {
  static scopeActive(query) {
    return query.where("status", "active");
  }
  
  static scopeVerified(query) {
    return query.where("verified", true);
  }
}

// Reuse logic
const active1 = User.scope("active", "verified").get();
const active2 = User.scope("active", "verified")
  .where("role", "admin")
  .get();

Don’t Modify Queries After Execution

❌ Bad: Reusing Executed Query

const query = db.table("Users").where("status", "active");
const users = query.get(); // Executes query

query.where("age", ">", 18); // ❌ Query already executed!
const adults = query.get();

✅ Good: Create New Queries

const users = db.table("Users")
  .where("status", "active")
  .get();

const adults = db.table("Users")
  .where("status", "active")
  .where("age", ">", 18)
  .get();

Data Integrity

Validate Before Inserting

function createUser(userData) {
  // Validate
  if (!userData.email || !isValidEmail(userData.email)) {
    throw new Error("Invalid email address");
  }
  
  if (!userData.name || userData.name.trim() === "") {
    throw new Error("Name is required");
  }
  
  // Normalize
  const normalized = {
    name: userData.name.trim(),
    email: userData.email.toLowerCase().trim(),
    age: parseInt(userData.age) || null,
    created_at: new Date().toISOString()
  };
  
  // Insert
  return db.table("Users").insert(normalized);
}

function isValidEmail(email) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email);
}

Use Fillable to Protect Against Mass Assignment

class User extends Model {
  static _fillable = ["name", "email", "age"];
  // "role" is NOT fillable - prevents users from making themselves admins
}

User.use(db);

// Safe - only fillable fields are set
const user = User.create({
  name: "John",
  email: "[email protected]",
  role: "admin" // ❌ Ignored! Not in fillable
});

// To set protected fields, use direct assignment
user.role = "admin";
user.save();

Normalize Data Before Storage

class User extends Model {
  // Mutator: automatically lowercase emails
  setEmailAttribute(value) {
    return value.toLowerCase().trim();
  }
  
  // Mutator: ensure proper name capitalization
  setNameAttribute(value) {
    return value.trim().split(" ")
      .map(word => word.charAt(0).toUpperCase() + word.slice(1).toLowerCase())
      .join(" ");
  }
}

const user = User.create({
  name: "john DOE",
  email: "  [email protected]  "
});

// Stored as:
// name: "John Doe"
// email: "[email protected]"

Table Management

Define Complete Structure Early

✅ Good: Complete Structure

users.create([
  "id", "name", "email", "age", "status",
  "verified", "created_at", "updated_at"
]);

❌ Bad: Incremental Structure

users.create(["id", "name"]);
users.addColumn("email");     // Added later
users.addColumn("age");       // Added even later
users.addColumn("status");    // And later

Use ensureHeaders in Initialization Scripts

function initializeDatabase() {
  const db = APPSQL.init({ spreadsheetId: "YOUR_ID" });
  
  // Safe - won't overwrite existing data
  db.table("Users").ensureHeaders([
    "id", "name", "email", "role", "created_at"
  ]);
  
  db.table("Posts").ensureHeaders([
    "id", "user_id", "title", "body", "published"
  ]);
}

Preserve Data When Renaming

✅ Good: Rename Individual Columns

users.renameColumn("name", "full_name");
users.renameColumn("email", "email_address");

❌ Bad: setHeaders with Overwrite

// ❌ Deletes all data!
users.setHeaders(["id", "full_name", "email_address"], true);

Always Confirm Destructive Operations

function deleteOldRecords() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.alert(
    "Delete all inactive users?",
    "This cannot be undone.",
    ui.ButtonSet.YES_NO
  );
  
  if (response === ui.Button.YES) {
    db.table("Users")
      .where("status", "inactive")
      .delete();
    ui.alert("Users deleted");
  }
}

Model Best Practices

Use Timestamps

class User extends Model {
  static _table = "Users";
  static _timestamps = true; // Enables created_at and updated_at
}

const user = User.create({ name: "John" });
// Automatically sets:
// created_at: "2026-03-06T10:00:00.000Z"
// updated_at: "2026-03-06T10:00:00.000Z"

user.name = "Jane";
user.save();
// Automatically updates:
// updated_at: "2026-03-06T10:05:00.000Z"

Use Casts for Type Safety

class Product extends Model {
  static _casts = {
    price: "float",
    stock: "int",
    active: "bool",
    features: "json",
    release_date: "date"
  };
}

const product = Product.find(1);
Logger.log(typeof product.price);        // "number"
Logger.log(typeof product.active);       // "boolean"
Logger.log(typeof product.features);     // "object"
Logger.log(product.release_date instanceof Date); // true

Use Soft Deletes for Recoverability

class Post extends Model {
  static _softDeletes = true;
}

const post = Post.find(1);
post.delete(); // Sets deleted_at, doesn't actually delete

// Query excludes soft-deleted records
const posts = Post.all(); // Doesn't include deleted posts

// Include deleted records
const all = Post.withTrashed().get();

// Only deleted records
const deleted = Post.onlyTrashed().get();

// Permanently delete
post.forceDelete();

Collections Best Practices

Use Collection Methods Instead of Native Array Operations

✅ Good: Use Collection Methods

const users = User.all();

// Collection methods are chainable
const result = users
  .where("status", "active")
  .sortBy("name")
  .take(10)
  .pluck("email")
  .all();

❌ Bad: Convert to Array Too Early

const users = User.all().all(); // Converts to array

// Now you lose collection methods
const filtered = users.filter(u => u.status === "active");
const sorted = filtered.sort((a, b) => a.name.localeCompare(b.name));
const limited = sorted.slice(0, 10);
const emails = limited.map(u => u.email);

Leverage Collection Transformations

const users = User.all();

// Group by country
const byCountry = users.groupBy("country");
byCountry.each((group, country) => {
  Logger.log(`${country}: ${group.count()} users`);
});

// Create lookup maps
const usersById = users.keyBy("id");
const user = usersById.get(123);

// Aggregate data
const stats = {
  total: users.count(),
  avgAge: users.avg("age"),
  totalPoints: users.sum("points")
};

Avoid Common Pitfalls

Don’t Store Complex Objects Directly

❌ Bad: Store Objects

db.table("Users").insert({
  id: 1,
  settings: { theme: "dark", lang: "en" } // ❌ Won't work!
});

✅ Good: Use JSON Casts

class User extends Model {
  static _casts = {
    settings: "json"
  };
}

User.create({
  id: 1,
  settings: { theme: "dark", lang: "en" } // ✅ Automatically serialized
});

Don’t Forget to Call save() After Modifications

const user = User.find(1);

// ❌ Changes not persisted
user.name = "New Name";
// Missing user.save()!

// ✅ Changes persisted
user.name = "New Name";
user.save();

Handle Missing Records Gracefully

// ❌ Can throw errors
const user = User.find(999); // Returns null if not found
Logger.log(user.name); // ❌ Error: Cannot read property 'name' of null

// ✅ Safe handling
const user = User.find(999);
if (user) {
  Logger.log(user.name);
} else {
  Logger.log("User not found");
}

Summary

Key takeaways for ServiceSQL best practices:

Performance

✅ Use eager loading with with() to avoid N+1 queries
✅ Select only needed columns with select()
✅ Paginate large datasets with paginate()
✅ Use aggregations instead of loading all data
✅ Prefer whereIn() over multiple ORs

Data Integrity

✅ Validate and normalize data before insertion
✅ Use fillable to protect against mass assignment
✅ Use mutators for consistent data formatting
✅ Enable timestamps for audit trails
✅ Use soft deletes for recoverability

Query Building

✅ Chain methods for readability
✅ Define reusable scopes
✅ Don’t reuse executed queries
✅ Always order paginated results

Table Management

✅ Define complete structure early
✅ Use ensureHeaders() in init scripts
✅ Rename columns individually to preserve data
✅ Confirm destructive operations

Following these practices will help you build efficient, maintainable applications with ServiceSQL. For more information, see:

Models - Working with Models
QueryBuilder - Building queries
Relations - Eager loading relationships

Getting Started

Core Concepts

Advanced Features

Guides

​Performance Optimization

​Use Eager Loading to Avoid N+1 Queries

❌ Bad: N+1 Problem

✅ Good: Eager Loading

​Eager Loading Multiple Relations

​Select Only Necessary Columns

❌ Bad: Fetching Everything

✅ Good: Select Specific Columns

​Use Pagination for Large Datasets

❌ Bad: Loading Everything

✅ Good: Use Pagination

​Prefer whereIn Over Multiple OR Conditions

❌ Bad: Multiple ORs

✅ Good: Use whereIn

​Use Aggregations Instead of Loading and Counting

❌ Bad: Load Then Count

✅ Good: Use count()

​Query Builder Best Practices

​Chain Methods for Readability

​Use Scopes for Reusable Logic

❌ Bad: Repeated Queries

✅ Good: Use Scopes

​Don’t Modify Queries After Execution

❌ Bad: Reusing Executed Query

✅ Good: Create New Queries

​Data Integrity

​Validate Before Inserting

​Use Fillable to Protect Against Mass Assignment

​Normalize Data Before Storage

​Table Management

​Define Complete Structure Early

✅ Good: Complete Structure

❌ Bad: Incremental Structure

​Use ensureHeaders in Initialization Scripts

​Preserve Data When Renaming

✅ Good: Rename Individual Columns

❌ Bad: setHeaders with Overwrite

​Always Confirm Destructive Operations

​Model Best Practices

​Use Timestamps

​Use Casts for Type Safety

​Use Soft Deletes for Recoverability

​Collections Best Practices

​Use Collection Methods Instead of Native Array Operations

✅ Good: Use Collection Methods

❌ Bad: Convert to Array Too Early

​Leverage Collection Transformations

​Avoid Common Pitfalls

​Don’t Store Complex Objects Directly

❌ Bad: Store Objects

✅ Good: Use JSON Casts

​Don’t Forget to Call save() After Modifications

​Handle Missing Records Gracefully

​Summary

​Performance

​Data Integrity

​Query Building

​Table Management

Build docs developers (and LLMs) love

Performance Optimization

Use Eager Loading to Avoid N+1 Queries

Eager Loading Multiple Relations

Select Only Necessary Columns

Use Pagination for Large Datasets

Prefer whereIn Over Multiple OR Conditions

Use Aggregations Instead of Loading and Counting

Query Builder Best Practices

Chain Methods for Readability

Use Scopes for Reusable Logic

Don’t Modify Queries After Execution

Data Integrity

Validate Before Inserting

Use Fillable to Protect Against Mass Assignment

Normalize Data Before Storage

Table Management

Define Complete Structure Early

Use ensureHeaders in Initialization Scripts

Preserve Data When Renaming

Always Confirm Destructive Operations

Model Best Practices

Use Timestamps

Use Casts for Type Safety

Use Soft Deletes for Recoverability

Collections Best Practices

Use Collection Methods Instead of Native Array Operations

Leverage Collection Transformations

Avoid Common Pitfalls

Don’t Store Complex Objects Directly

Don’t Forget to Call save() After Modifications

Handle Missing Records Gracefully

Summary

Performance

Data Integrity

Query Building

Table Management