Skip to main content

​
Overview

The Model class provides an elegant ActiveRecord implementation for ServiceSQL. Models represent tables in your spreadsheet and provide intuitive methods for querying, creating, updating, and deleting records.

​
Setup

​
Creating a Model

class User extends Model {
  static tableName = 'Users';
  static _primaryKey = 'id';
  static _timestamps = true;
  static _fillable = ['name', 'email', 'status'];
  static _casts = {
    email: 'string',
    active: 'boolean'
  };
}

// Register with database
db.model(User);

​
Model Configuration

​
_table
string
Table/sheet name (defaults to class name)
​
_primaryKey
string
default:"id"
Primary key column name
​
_timestamps
boolean
default:"false"
Enable automatic created_at and updated_at management
​
_fillable
array
default:"null"
Array of columns that can be mass-assigned. null means all columns are fillable
​
_casts
object
default:"{}"
Define type casting for attributesSupported types: int, integer, float, double, string, bool, boolean, json, array, date, datetime, dmy, dmyhms
​
_dates
array
default:"['created_at', 'updated_at', 'deleted_at']"
Columns that should be cast to Date objects
​
_softDeletes
boolean
default:"false"
Enable soft deletes (sets deleted_at instead of removing records)
​
_deletedAt
string
default:"deleted_at"
Column name for soft delete timestamp

​
Static Methods

​
Initialization

Manually set the database connection for a model.
Model.use(db);
​
db
APPSQL
required
The database connection instance
This is automatically called when using db.model(). Only use this for manual configuration.
Lifecycle hook called once when the model is initialized. Override this to set up observers, global scopes, etc.
class User extends Model {
  static boot() {
    super.boot();
    
    this.observe({
      creating: (user) => {
        console.log('Creating user:', user.email);
      },
      created: (user) => {
        console.log('User created:', user.id);
      }
    });
  }
}

​
Query Methods

Get a new query builder instance for the model.
const query = User.query()
  .where('status', 'active')
  .orderBy('created_at', 'desc');
​
return
QueryBuilder
Returns a QueryBuilder instance with the model’s table and soft delete handling configured
Shorthand for creating a query with a where clause.
const activeUsers = User.where('status', 'active').get();
const premiumUsers = User.where('plan', 'premium').where('verified', true).get();
​
column
string
required
Column name to filter
​
operator
string
Comparison operator (defaults to == if not provided)
​
value
any
required
Value to compare against
​
return
QueryBuilder
Returns a QueryBuilder instance with the where clause applied
Retrieve all records from the model’s table.
const allUsers = User.all();
​
return
Collection
Returns a Collection of model instances
Retrieve the first record.
const firstUser = User.first();
const oldestUser = User.orderBy('created_at', 'asc').first();
​
return
Model|null
Returns a model instance or null if no records found
Find a record by primary key.
const user = User.find(42);
if (user) {
  console.log(user.name);
}
​
id
any
required
Value of the primary key
​
return
Model|null
Returns a model instance or null if not found
Find a record by primary key or throw an error.
try {
  const user = User.findOrFail(42);
  console.log(user.name);
} catch (e) {
  console.log('User not found');
}
​
id
any
required
Value of the primary key
​
return
Model
Returns a model instance or throws an error
Create and save a new record.
const user = User.create({
  name: 'John Doe',
  email: '[email protected]',
  status: 'active'
});

console.log(user.id); // Auto-generated ID
​
attrs
object
default:"{}"
Attribute values for the new record
​
return
Model
Returns the created model instance with ID populated
Triggers creating and created events. Automatically sets timestamps if enabled.

​
Eager Loading

Eager load relationships to avoid N+1 queries.
const users = User.with('posts', 'comments').get();

users.each(user => {
  console.log(user.name);
  console.log(user.posts); // Already loaded
  console.log(user.comments); // Already loaded
});
​
...relations
string
required
Names of relationships to eager load
​
return
QueryBuilder
Returns a QueryBuilder with eager loading configured

​
Scopes

Apply a query scope defined in the model.
class User extends Model {
  static scopeActive(query) {
    return query.where('status', 'active');
  }
  
  static scopePremium(query) {
    return query.where('plan', 'premium');
  }
}

const users = User.scope('active').get();
const premiumUsers = User.scope('premium').scope('active').get();
​
name
string
required
Name of the scope (without ‘scope’ prefix)
​
...args
any
Additional arguments to pass to the scope method
​
return
QueryBuilder
Returns a QueryBuilder with the scope applied

​
Soft Deletes

Include soft-deleted records in queries.
class User extends Model {
  static _softDeletes = true;
}

const allUsers = User.withTrashed().get();
​
return
class
Returns the model class with trash mode set to ‘with’
Retrieve only soft-deleted records.
const deletedUsers = User.onlyTrashed().get();
​
return
class
Returns the model class with trash mode set to ‘only’

​
Observers

Register an observer for model events.
User.observe({
  creating: (user) => {
    console.log('About to create:', user);
  },
  created: (user) => {
    console.log('Created:', user.id);
  },
  updating: (user) => {
    console.log('About to update:', user.id);
  },
  updated: (user) => {
    console.log('Updated:', user.id);
  },
  deleting: (user) => {
    console.log('About to delete:', user.id);
  },
  deleted: (user) => {
    console.log('Deleted:', user.id);
  }
});
​
observer
object|function
required
Object with event handler methods or a function that receives (eventName, instance)
Available events: creating, created, updating, updated, deleting, deleted

​
Instance Methods

Save the model instance (create or update).
const user = new User({ name: 'John', email: '[email protected]' });
user.save(); // Creates new record

user.name = 'Jane';
user.save(); // Updates existing record
​
return
Model
Returns the model instance
Automatically determines whether to create or update based on primary key presence.
Update the model with new attributes and save.
const user = User.find(1);
user.update({
  name: 'Updated Name',
  status: 'inactive'
});
​
attrs
object
default:"{}"
Attributes to update
​
return
Model
Returns the updated model instance
Delete the model instance.
const user = User.find(1);
user.delete();
​
return
boolean
Returns true if deleted successfully
If soft deletes are enabled, sets deleted_at timestamp instead of removing the record.
Fill the model with an array of attributes.
const user = new User();
user.fill({
  name: 'John',
  email: '[email protected]',
  password: 'secret' // Ignored if not in _fillable
});
​
attrs
object
default:"{}"
Attributes to fill
​
return
Model
Returns the model instance
Respects the _fillable property for mass assignment protection.
Convert the model to a plain JavaScript object.
const user = User.find(1);
const json = user.toJSON();
console.log(json); // { id: 1, name: 'John', email: '[email protected]' }
​
return
object
Returns a plain object with all non-private properties

​
Relationships

Define a belongs-to relationship.
class Post extends Model {
  user() {
    return this.belongsTo(User, 'user_id', 'id');
  }
}
​
RelatedClass
class
required
The related model class
​
foreignKey
string
Foreign key column (defaults to {RelatedClass._table}_id)
​
ownerKey
string
default:"id"
Primary key on the related model
​
return
object
Returns a relationship definition object
Define a one-to-many relationship.
class User extends Model {
  posts() {
    return this.hasMany(Post, 'user_id', 'id');
  }
}
​
RelatedClass
class
required
The related model class
​
foreignKey
string
Foreign key on the related model (defaults to {this._table}_id)
​
localKey
string
default:"id"
Primary key on this model
​
return
object
Returns a relationship definition object
Define a one-to-one relationship.
class User extends Model {
  profile() {
    return this.hasOne(Profile, 'user_id', 'id');
  }
}
​
RelatedClass
class
required
The related model class
​
foreignKey
string
Foreign key on the related model (defaults to {this._table}_id)
​
localKey
string
default:"id"
Primary key on this model
​
return
object
Returns a relationship definition object
Define a many-to-many relationship.
class User extends Model {
  roles() {
    return this.manyToMany(Role, 'user_roles', 'user_id', 'role_id');
  }
}
​
RelatedClass
class
required
The related model class
​
pivotTable
string
required
Name of the pivot/junction table
​
parentKey
string
Foreign key for this model in pivot table (defaults to {this._table}_id)
​
relatedKey
string
Foreign key for related model in pivot table (defaults to {RelatedClass._table}_id)
​
return
object
Returns a relationship definition object

​
Accessors & Mutators

Define custom attribute accessors to transform data when retrieving.
class User extends Model {
  getFullNameAttribute(value) {
    return `${this.first_name} ${this.last_name}`;
  }
  
  getEmailAttribute(value) {
    return value.toLowerCase();
  }
}

const user = User.find(1);
console.log(user.full_name); // "John Doe"
console.log(user.email); // "[email protected]"
Accessor methods follow the pattern: get{AttributeName}Attribute(value)
Define custom attribute mutators to transform data before saving.
class User extends Model {
  static setPasswordAttribute(value) {
    // In a real app, you'd hash the password
    return value.toUpperCase();
  }
  
  static setEmailAttribute(value) {
    return value.toLowerCase().trim();
  }
}

const user = User.create({
  email: '  [email protected]  ',
  password: 'secret'
});
// Saved as: { email: '[email protected]', password: 'SECRET' }
Mutator methods follow the pattern: set{AttributeName}Attribute(value) and must be static

​
Complete Example

class User extends Model {
  static tableName = 'Users';
  static _timestamps = true;
  static _fillable = ['name', 'email', 'role', 'status'];
  static _casts = {
    active: 'boolean',
    login_count: 'integer'
  };
  static _softDeletes = true;
  
  static boot() {
    super.boot();
    
    this.observe({
      creating: (user) => {
        user.status = user.status || 'pending';
      },
      created: (user) => {
        Logger.log(`User ${user.id} created`);
      }
    });
  }
  
  static scopeActive(query) {
    return query.where('status', 'active');
  }
  
  static scopeAdmins(query) {
    return query.where('role', 'admin');
  }
  
  posts() {
    return this.hasMany(Post, 'user_id');
  }
  
  profile() {
    return this.hasOne(Profile, 'user_id');
  }
  
  getFullNameAttribute() {
    return `${this.first_name} ${this.last_name}`;
  }
  
  static setEmailAttribute(value) {
    return value.toLowerCase().trim();
  }
}

// Register model
db.model(User);

// Usage
const user = User.create({
  name: 'John Doe',
  email: '  [email protected]  ',
  role: 'admin'
});

const activeAdmins = User.scope('active').scope('admins').get();
const usersWithPosts = User.with('posts').get();

user.update({ status: 'active' });
user.delete();

Build docs developers (and LLMs) love

Get started for freeTalk to us