Sutando

https://github.com/sutandojs/sutando
Admin
Sutando is an object-relational mapper (ORM) that provides an enjoyable way to interact with...
Tokens:7,156
Snippets:42
Trust Score:5.9
License:MIT
Update:10 months ago
Show doc for...
Tokens:
Raw
### Install Sutando and MySQL Driver

Source: https://github.com/sutandojs/sutando/blob/main/README.md

Installs the Sutando ORM and the mysql2 library for MySQL database connections using npm.

```bash
$ npm install sutando mysql2 --save
```

--------------------------------

### Database Connection Setup with Sutando

Source: https://context7.com/sutandojs/sutando/llms.txt

Configures database connections for Sutando using the addConnection method. Supports multiple database clients like MySQL, PostgreSQL, and SQLite, with options for default and named connections. Returns connection instances.

```javascript
const { sutando, Model } = require('sutando');

// MySQL connection
sutando.addConnection({
  client: 'mysql2',
  connection: {
    host: '127.0.0.1',
    port: 3306,
    user: 'root',
    password: 'secret',
    database: 'myapp'
  }
}, 'default');

// PostgreSQL connection (named)
sutando.addConnection({
  client: 'pg',
  connection: {
    host: 'localhost',
    port: 5432,
    user: 'postgres',
    password: 'password',
    database: 'testdb'
  }
}, 'postgres_connection');

// SQLite connection
sutando.addConnection({
  client: 'sqlite3',
  connection: {
    filename: './database.sqlite'
  }
});

// Get connection instance
const db = sutando.connection();
const pgDb = sutando.connection('postgres_connection');
```

--------------------------------

### SutandoJS Collections API Examples (JavaScript)

Source: https://context7.com/sutandojs/sutando/llms.txt

Demonstrates various utility methods available on SutandoJS Collections for mapping, filtering, finding, plucking, grouping, loading relationships, and checking for model existence. It also covers advanced operations like lazy loading, aggregate counts, and asynchronous mapping.

```javascript
const users = await User.query().get(); // Returns Collection instance

// Map over results
const names = users.map(user => user.name);

// Filter
const adults = users.filter(user => user.age >= 18);

// Find specific item
const john = users.find(user => user.name === 'John');

// Pluck specific attribute
const emails = users.pluck('email'); // ['user1@example.com', 'user2@example.com']

// Group by attribute
const byCountry = users.groupBy('country');

// Lazy load relationships on collection
const usersCollection = await User.query().get();
await usersCollection.load('posts', 'comments');

// Load aggregate counts
await usersCollection.loadCount('posts');
users.forEach(user => {
  console.log(`${user.name}: ${user.posts_count} posts`);
});

// Load aggregates
await usersCollection.loadSum('orders', 'total');
await usersCollection.loadAvg('orders', 'rating');
await usersCollection.loadMax('orders', 'amount');

// Map with async operations
await usersCollection.mapThen(async user => {
  await user.load('posts');
  return user;
});

// Check if collection contains model
const hasUser = users.contains(someUser);

// Unique values
const uniqueCountries = users.pluck('country').unique();

// Sorting
const sorted = users.sortBy('created_at');
const sortedDesc = users.sortByDesc('age');
```

--------------------------------

### Define One-to-Many Relationships in JavaScript

Source: https://context7.com/sutandojs/sutando/llms.txt

Demonstrates how to define one-to-many relationships using `hasMany` and `belongsTo` in Sutando ORM models. It also shows eager loading examples for efficient data retrieval, including nested and constrained loading.

```javascript
class User extends Model {
  relationPosts() {
    return this.hasMany(Post, 'user_id', 'id');
  }

  relationComments() {
    return this.hasMany(Comment);
  }
}

class Post extends Model {
  relationAuthor() {
    return this.belongsTo(User, 'user_id', 'id');
  }

  relationComments() {
    return this.hasMany(Comment);
  }
}

class Comment extends Model {
  relationPost() {
    return this.belongsTo(Post);
  }

  relationAuthor() {
    return this.belongsTo(User, 'user_id');
  }
}

// Eager loading relationships (N+1 solution)
const usersWithPosts = await User.query()
  .with('posts')
  .get();

usersWithPosts.forEach(user => {
  console.log(`${user.name} has ${user.posts.length} posts`);
});

// Multiple relationships
const posts = await Post.query()
  .with('author', 'comments')
  .get();

// Nested eager loading
const users = await User.query()
  .with({
    posts: q => q.with('comments')
  })
  .get();

// Constrained eager loading
const activeUsers = await User.query()
  .with({
    posts: q => q.where('status', 'published').orderBy('created_at', 'desc')
  })
  .get();

// Lazy eager loading
const user = await User.query().find(1);
await user.load('posts');
console.log(user.posts);
```

--------------------------------

### Configure Database Connection with Sutando

Source: https://github.com/sutandojs/sutando/blob/main/README.md

Demonstrates how to add a MySQL database connection configuration to Sutando and retrieve a database connection instance. This is the initial step before performing any database operations.

```javascript
const { sutando, Model } = require('sutando');

// Add SQL Connection Info
sutando.addConnection({
  client: 'mysql2',
  connection: {
    host : '127.0.0.1',
    port : 3306,
    user : 'root',
    password : '',
    database : 'test'
  },
});

const db = sutando.connection();
```

--------------------------------

### SutandoJS Running Migrations Programmatically (JavaScript)

Source: https://context7.com/sutandojs/sutando/llms.txt

Illustrates how to programmatically run, rollback, and check the status of database migrations using the SutandoJS API. It includes configuration for database connections and specifies the migration directory.

```javascript
const { migrateRun, migrateRollback, migrateStatus, sutando } = require('sutando');

// Configure connection first
sutando.addConnection({
  client: 'mysql2',
  connection: {
    host: '127.0.0.1',
    user: 'root',
    password: 'secret',
    database: 'myapp'
  }
});

// Run pending migrations
await migrateRun({
  connection: sutando.connection(),
  directory: './migrations'
});

// Rollback last batch
await migrateRollback({
  connection: sutando.connection(),
  directory: './migrations'
});

// Rollback specific number of migrations
await migrateRollback({
  connection: sutando.connection(),
  directory: './migrations',
  step: 2
});

// Check migration status
const status = await migrateStatus({
  connection: sutando.connection(),
  directory: './migrations'
});

console.log(status); // Array of migrations with run status

// CLI usage (package.json script or direct):
// sutando migrate:run --path=./migrations
// sutando migrate:rollback
// sutando migrate:status
```

--------------------------------

### Basic Queries with Sutando Query Builder

Source: https://context7.com/sutandojs/sutando/llms.txt

Executes SQL queries using Sutando's fluent query builder without defining models. Supports SELECT, INSERT, UPDATE, DELETE, and aggregate functions like AVG, COUNT, and MAX. Requires an active database connection.

```javascript
const db = sutando.connection();

// Select all users over 35
const users = await db.table('users')
  .where('age', '>', 35)
  .get();

// Select specific columns
const userEmails = await db.table('users')
  .select('email', 'name')
  .where('active', true)
  .get();

// Find by ID
const user = await db.table('users').find(1);

// Insert data
await db.table('users').insert({
  name: 'John Doe',
  email: 'john@example.com',
  age: 30
});

// Update records
await db.table('users')
  .where('id', 5)
  .update({ age: 31 });

// Delete records
await db.table('users')
  .where('status', 'inactive')
  .delete();

// Aggregate functions
const avgAge = await db.table('users').avg('age');
const userCount = await db.table('users').count();
const maxSalary = await db.table('users').max('salary');
```

--------------------------------

### Perform Database Queries with Sutando Query Builder

Source: https://github.com/sutandojs/sutando/blob/main/README.md

Illustrates how to use Sutando's query builder to fetch records from a database table based on specific criteria. It demonstrates a simple 'where' clause for filtering records.

```javascript
// Query Builder
const users = await db.table('users').where('age', '>', 35).get();
```

--------------------------------

### Eager Loading Relationships in Sutando

Source: https://github.com/sutandojs/sutando/blob/main/README.md

Demonstrates how to use Sutando's eager loading feature to load related data (e.g., 'posts' for 'users') along with the main query. This helps prevent the N+1 query problem.

```javascript
// Eager Loading
const users = await User.query().with('posts').get();
```

--------------------------------

### Define and Query Data with Sutando Models

Source: https://github.com/sutandojs/sutando/blob/main/README.md

Shows how to define a Model that maps to a database table and then use the Model's query interface to retrieve data. This approach leverages Sutando's ORM capabilities.

```javascript
// ORM
class User extends Model {}

// Query Data
const users = await User.query().where('age', '>', 35).get();
```

--------------------------------

### Sutando Plugin System and Model Composition

Source: https://context7.com/sutandojs/sutando/llms.txt

Demonstrates how to extend Sutando ORM models using a plugin system and the composition pattern. Plugins are functions that take a Model and return a new Model class with added functionality, such as UUID generation or trackable fields. Models can be composed using the `compose` function or extended using the `extend` method.

```javascript
const { Model, compose } = require('sutando');

// Define a plugin
const HasUuid = (Model) => {
  return class extends Model {
    static bootHasUuid() {
      this.addHook('creating', (model) => {
        model.uuid = generateUuid();
      });
    }

    initialize() {
      super.initialize();
      this.constructor.bootHasUuid();
    }
  };
};

// Define another plugin
const Trackable = (Model) => {
  return class extends Model {
    static bootTrackable() {
      this.addHook('creating', (model) => {
        model.created_by = getCurrentUserId();
      });

      this.addHook('updating', (model) => {
        model.updated_by = getCurrentUserId();
      });
    }

    initialize() {
      super.initialize();
      this.constructor.bootTrackable();
    }
  };
};

// Use plugins with composition
class User extends compose(
  Model,
  HasUuid,
  Trackable
) {
  // User-specific logic
  relationPosts() {
    return this.hasMany(Post);
  }
}

// Or use extend method
class Post extends Model {}
Post.extend(HasUuid);
Post.extend(Trackable);

// Create models with plugin functionality
const user = await User.query().create({
  name: 'Plugin User',
  email: 'plugin@example.com'
});

console.log(user.uuid); // Auto-generated UUID
console.log(user.created_by); // Current user ID

```

--------------------------------

### Insert Data using Sutando Models

Source: https://github.com/sutandojs/sutando/blob/main/README.md

Demonstrates how to create a new record in the database using a Sutando Model. A new instance of the Model is created, its properties are set, and then saved to the database.

```javascript
// Insert
const user = new User;
user.name = 'David Bowie';
await user.save();
```

--------------------------------

### Implement Pagination with Sutando Models

Source: https://github.com/sutandojs/sutando/blob/main/README.md

Shows how to retrieve paginated results from the database using Sutando's Model query interface. The `.paginate()` method handles the logic for fetching data in chunks.

```javascript
// Pagination
const users = await User.query().paginate();
```

--------------------------------

### SutandoJS Database Migrations - Create Posts Table (JavaScript)

Source: https://context7.com/sutandojs/sutando/llms.txt

Defines a database migration to create the 'posts' table, including a foreign key constraint referencing the 'users' table. This migration demonstrates how to set up relationships and cascade deletes.

```javascript
const { Migration } = require('sutando');

// Create posts table with foreign key
class CreatePostsTable extends Migration {
  async up() {
    await this.schema.createTable('posts', (table) => {
      table.increments('id');
      table.integer('user_id').unsigned().notNullable();
      table.string('title', 255);
      table.text('content');
      table.string('status').defaultTo('draft');
      table.timestamps(true, true);

      table.foreign('user_id').references('id').inTable('users').onDelete('CASCADE');
    });
  }

  async down() {
    await this.schema.dropTableIfExists('posts');
  }
}

module.exports = CreatePostsTable;
```

--------------------------------

### Lazy Eager Loading in Sutando

Source: https://github.com/sutandojs/sutando/blob/main/README.md

Illustrates how to perform lazy eager loading in Sutando, where related data is loaded only when explicitly requested for a specific model instance after the initial query.

```javascript
// Lazy Eager Loading
await user.load('posts');
```

--------------------------------

### Advanced Queries and Scopes in JavaScript

Source: https://context7.com/sutandojs/sutando/llms.txt

Covers advanced querying techniques including defining and using query scopes, dynamic where methods, complex query constructions, raw SQL queries, relationship counting, and relationship existence checks.

```javascript
class User extends Model {
  // Define query scopes
  scopeActive(query) {
    return query.where('status', 'active');
  }

  scopeAdults(query) {
    return query.where('age', '>=', 18);
  }

  scopePopular(query) {
    return query.where('followers_count', '>', 1000);
  }
}

// Use scopes
const activeUsers = await User.query().active().get();
const popularAdults = await User.query().adults().popular().get();

// Dynamic where methods (camelCase to snake_case)
const emailMatch = await User.query().whereEmail('test@example.com').first();
const ageRange = await User.query().whereAgeBetween([18, 65]).get();

// Complex queries
const results = await User.query()
  .where('age', '>', 18)
  .whereIn('country', ['US', 'CA', 'UK'])
  .whereNotNull('email_verified_at')
  .orderBy('created_at', 'desc')
  .limit(10)
  .get();

// Raw queries
const customQuery = await User.query()
  .whereRaw('age > ? AND status = ?', [18, 'active'])
  .get();

// Relationship counting
const usersWithPostCount = await User.query()
  .withCount('posts')
  .get();

usersWithPostCount.forEach(user => {
  console.log(`${user.name}: ${user.posts_count} posts`);
});

// Relationship existence
const usersWithPosts = await User.query()
  .has('posts')
  .get();

const usersWithManyPosts = await User.query()
  .has('posts', '>=', 5)
  .get();
```

--------------------------------

### Paginate Query Results in JavaScript

Source: https://context7.com/sutandojs/sutando/llms.txt

Explains how to paginate query results in Sutando ORM, including default and custom per-page counts, filtering, ordering, and loading relationships within pagination. It also introduces simple pagination.

```javascript
// Basic pagination (15 per page by default)
const paginatedUsers = await User.query().paginate();

console.log(paginatedUsers.data);        // Array of User models
console.log(paginatedUsers.total);       // Total number of records
console.log(paginatedUsers.per_page);    // Items per page
console.log(paginatedUsers.current_page); // Current page number
console.log(paginatedUsers.last_page);   // Last page number

// Custom page and per page
const page2 = await User.query().paginate(2, 25); // page 2, 25 items per page

// With filters and ordering
const filteredPagination = await User.query()
  .where('status', 'active')
  .orderBy('created_at', 'desc')
  .paginate(1, 10);

// With relationships
const usersWithPostsPaginated = await User.query()
  .with('posts')
  .paginate();

// Simple paginate (lighter, no total count)
const simplePaginated = await User.query().simplePaginate(1, 20);
```

--------------------------------

### Constraining Eager Loads in Sutando

Source: https://github.com/sutandojs/sutando/blob/main/README.md

Explains how to apply constraints to eager-loaded relationships in Sutando. This allows for more specific fetching of related data, such as only loading posts with more than 100 likes.

```javascript
// Constraining Eager Loads
const users = await User.query().with({
  posts: q => q.where('likes_count', '>', 100)
}).get();
```

--------------------------------

### SutandoJS Database Migrations - Create Users Table (JavaScript)

Source: https://context7.com/sutandojs/sutando/llms.txt

Defines a database migration to create the 'users' table using SutandoJS's Migration API. It includes common fields like id, name, email, age, and timestamps, with unique constraints and default values.

```javascript
const { Migration } = require('sutando');

// Create migration file: migrations/20231215_create_users_table.js
class CreateUsersTable extends Migration {
  async up() {
    await this.schema.createTable('users', (table) => {
      table.increments('id');
      table.string('name', 100);
      table.string('email').unique();
      table.integer('age').nullable();
      table.boolean('is_admin').defaultTo(false);
      table.json('metadata').nullable();
      table.timestamps(true, true); // created_at, updated_at
    });
  }

  async down() {
    await this.schema.dropTableIfExists('users');
  }
}

module.exports = CreateUsersTable;
```

--------------------------------

### Sutando Model Lifecycle Hooks and Events

Source: https://context7.com/sutandojs/sutando/llms.txt

Defines and utilizes various lifecycle hooks for Sutando ORM models. Hooks like 'creating', 'created', 'updating', 'saved', 'deleting', and 'deleted' allow custom logic execution before or after specific model operations. The 'saving' hook triggers before both create and update operations. The `saveQuietly` and `deleteQuietly` methods demonstrate how to bypass these hooks.

```javascript
class User extends Model {
  // Before creating new record
  static booting() {
    this.addHook('creating', async (user) => {
      user.uuid = generateUuid();
      console.log('About to create user');
    });

    // After record created
    this.addHook('created', async (user) => {
      await sendWelcomeEmail(user.email);
      console.log(`User ${user.id} created`);
    });

    // Before updating
    this.addHook('updating', async (user) => {
      console.log('About to update user');
      // Return false to cancel update
    });

    // After updating
    this.addHook('updated', async (user) => {
      await clearUserCache(user.id);
    });

    // Before saving (create or update)
    this.addHook('saving', async (user) => {
      user.updated_by = getCurrentUserId();
    });

    // After saving
    this.addHook('saved', async (user) => {
      await auditLog('user_saved', user.id);
    });

    // Before deleting
    this.addHook('deleting', async (user) => {
      await user.load('posts');
      if (user.posts.length > 0) {
        throw new Error('Cannot delete user with posts');
      }
    });

    // After deletion
    this.addHook('deleted', async (user) => {
      await cleanupUserFiles(user.id);
    });
  }
}

// Using hooks
const user = new User();
user.name = 'Test User';
user.email = 'test@example.com';
await user.save(); // Triggers: saving -> creating -> created -> saved hooks

// Execute without events/hooks
await user.saveQuietly(); // Skips all hooks
await user.deleteQuietly(); // Skips deletion hooks

```

--------------------------------

### Model Definition and CRUD with Sutando

Source: https://context7.com/sutandojs/sutando/llms.txt

Defines models by extending Sutando's Model class for ActiveRecord-style database operations. Supports automatic table and primary key inference, timestamps, and includes methods for create, find, query, update, and delete operations.

```javascript
const { Model } = require('sutando');

class User extends Model {
  // Table name is automatically inferred as 'users'
  // Override if needed:
  // table = 'custom_users';

  // Primary key defaults to 'id'
  // primaryKey = 'user_id';

  // Timestamps enabled by default (created_at, updated_at)
}

// Create and save
const user = new User();
user.name = 'Alice Johnson';
user.email = 'alice@example.com';
user.age = 28;
await user.save();

// Create in one step
const bob = await User.query().create({
  name: 'Bob Smith',
  email: 'bob@example.com',
  age: 35
});

// Find by primary key
const foundUser = await User.query().find(1);

// Query with conditions
const adults = await User.query()
  .where('age', '>=', 18)
  .orderBy('created_at', 'desc')
  .get();

// Update
const userToUpdate = await User.query().find(2);
userToUpdate.email = 'newemail@example.com';
await userToUpdate.save();

// Delete
const userToDelete = await User.query().find(3);
await userToDelete.delete();

// Or delete directly
await User.query().where('id', 4).delete();
```

--------------------------------

### Define Many-to-Many Relationships in JavaScript

Source: https://context7.com/sutandojs/sutando/llms.txt

Illustrates how to define many-to-many relationships using `belongsToMany`, including specifying the pivot table and foreign keys. It covers querying, attaching, detaching, syncing, and accessing pivot data.

```javascript
class Post extends Model {
  relationTags() {
    return this.belongsToMany(Tag, 'post_tag', 'post_id', 'tag_id');
  }
}

class Tag extends Model {
  relationPosts() {
    return this.belongsToMany(Post, 'post_tag', 'tag_id', 'post_id');
  }
}

// Query with many-to-many
const postsWithTags = await Post.query()
  .with('tags')
  .get();

postsWithTags.forEach(post => {
  const tagNames = post.tags.map(tag => tag.name).join(', ');
  console.log(`${post.title}: ${tagNames}`);
});

// Attach tags to a post
const post = await Post.query().find(1);
await post.tags().attach([1, 2, 3]);

// Detach tags
await post.tags().detach([2]);

// Sync (replace all relationships)
await post.tags().sync([1, 3, 4]);

// Access pivot data
const tagsWithPivot = await post.tags().get();
tagsWithPivot.forEach(tag => {
  console.log(tag.pivot.created_at);
});
```

--------------------------------

### Delete Data using Sutando Models

Source: https://github.com/sutandojs/sutando/blob/main/README.md

Illustrates how to delete a record from the database using a Sutando Model instance. Assumes a 'user' instance has already been retrieved or created.

```javascript
// Delete
await user.delete();
```

--------------------------------

### JavaScript Soft Deletes Implementation in Sutando.js Models

Source: https://context7.com/sutandojs/sutando/llms.txt

Implement soft deletion for Sutando.js models, allowing records to be marked as deleted without permanent removal. Includes methods for soft deletion, restoring, permanent deletion (forceDelete), and querying trashed or all records.

```javascript
const { Model, SoftDeletes, compose } = require('sutando');

class Post extends compose(Model, SoftDeletes) {
}

const post = await Post.query().find(1);
await post.delete(); 

const posts = await Post.query().get(); 

const allPosts = await Post.query().withTrashed().get();

const trashedPosts = await Post.query().onlyTrashed().get();

const deletedPost = await Post.query().onlyTrashed().find(1);
await deletedPost.restore(); 

const post2 = await Post.query().find(2);
await post2.forceDelete(); 

await Post.query().where('status', 'draft').delete(); 
await Post.query().where('id', 3).forceDelete(); 
await Post.query().onlyTrashed().restore();
```

--------------------------------

### JavaScript Transaction Management with Sutando.js

Source: https://context7.com/sutandojs/sutando/llms.txt

Execute multiple database operations atomically using Sutando.js's transaction support. Supports automatic commit/rollback via callbacks or manual control with beginTransaction, commit, and rollback. Transactions can also be tied to specific database connections.

```javascript
await sutando.transaction(async (trx) => {
  const user = await User.query(trx).create({
    name: 'Transaction User',
    email: 'trx@example.com'
  });

  await Post.query(trx).create({
    user_id: user.id,
    title: 'Transaction Post',
    content: 'Created in transaction'
  });
});

const trx = await sutando.beginTransaction();

try {
  const user = new User();
  user.name = 'Manual Transaction';
  user.email = 'manual@example.com';
  user.trx = trx;
  await user.save();

  const post = new Post();
  post.user_id = user.id;
  post.title = 'Another Post';
  post.trx = trx;
  await post.save();

  await trx.commit();
} catch (error) {
  await trx.rollback();
  throw error;
}

await sutando.transaction(async (trx) => {
  await User.query(trx).where('status', 'pending').delete();
  await Post.query(trx).where('draft', true).delete();
}, 'postgres_connection');
```

--------------------------------

### JavaScript Attribute Casting and Accessors in Sutando.js Models

Source: https://context7.com/sutandojs/sutando/llms.txt

Define custom data type conversions (casting) and computed properties (accessors/mutators) for Sutando.js models. Casts automatically convert data types on retrieval and before saving. Accessors and mutators allow for dynamic modification and computation of attribute values.

```javascript
const { Model, Attribute } = require('sutando');

class User extends Model {
  casts = {
    age: 'integer',
    is_admin: 'boolean',
    metadata: 'json',
    email_verified_at: 'datetime',
    score: 'float'
  };

  attributeFullName() {
    return Attribute.make({
      get: () => `${this.first_name} ${this.last_name}`
    });
  }

  attributePassword() {
    return Attribute.make({
      set: (value) => hashPassword(value) 
    });
  }

  attributeEmail() {
    return Attribute.make({
      get: (value) => value.toLowerCase(),
      set: (value) => value.toLowerCase().trim()
    });
  }
}

const user = new User();
user.first_name = 'John';
user.last_name = 'Doe';
user.age = '30'; 
user.is_admin = 'true'; 
user.metadata = { preferences: { theme: 'dark' } }; 
user.password = 'secret123'; 

console.log(user.full_name);
console.log(user.age);
console.log(user.is_admin);

await user.save();

const retrieved = await User.query().find(user.id);
console.log(retrieved.metadata.preferences.theme);
console.log(retrieved.email_verified_at instanceof Date);
```
Tokens:
Raw