Sutando

https://sutando.org/guide/
Admin
Sutando is an object-relational mapper (ORM) inspired by Eloquent, designed to make interacting with...
Tokens:47,048
Snippets:513
Trust Score:5.7
Update:9 months ago
Show doc for...
Tokens:
Raw
### Sutando Database Connection and Query Builder Example

Source: https://sutando.org/guide/getting-started

Demonstrates how to establish a database connection with Sutando and utilize the query builder to select records from the 'users' table. It also shows how to create a new table using the schema builder and interact with database records using the ORM.

```js
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();

// Using The Query Builder
const users = await sutando.table('users').where('votes', '>', 100).get();
// or
const users = await db.table('users').where('votes', '>', 100).get();

// Using The Schema Builder
await sutando.schema().createTable('users', table => {
  table.increments('id').primary();
  table.integer('votes');
  table.timestamps();
});

// Using The ORM
class User extends Model {}
const users = await User.query().where('votes', '>', 100).get();
```

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

### Install Sutando and mysql2

Source: https://sutando.org/guide/getting-started

Install the Sutando ORM and the mysql2 database driver using your preferred package manager. This is the first step to setting up your database interactions.

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

```sh
$ yarn add sutando mysql2
```

```sh
$ pnpm add sutando mysql2
```

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

### Implementing UUID Primary Keys with Sutando

Source: https://sutando.org/guide/models

Provides an example of how to use UUIDs as primary keys in Sutando models by composing with `HasUniqueIds` and implementing the `newUniqueId` method. Includes installation instructions for the `uuid` package.

```bash
$ npm install uuid --save
```

```bash
$ yarn add uuid
```

```bash
$ pnpm add uuid
```

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

class Article extends compose(Model, HasUniqueIds) {
  newUniqueId() {
    return uuid.v4();
  }

  // ...
}

const article = await Article.create({
  title: 'Traveling to Europe'
});

article.id; // "8f8e8478-9035-4d23-b9a7-62f4d2612ce5"
```

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

### Sutando Usage Examples: Models, Collections, and Pagination

Source: https://sutando.org/guide/browser

Demonstrates practical examples of using Sutando's `make`, `makeCollection`, and `makePaginator` functions with fetched API data to create model instances, collections, and handle pagination.

```javascript
// Convert API response to model instance
const response = await fetch('/api/users/1');
const data = await response.json();
const user = make(User, data);

// Use model accessors and other features
console.log(user.full_name);  // Assuming a full_name accessor exists

// Handle list data
const usersResponse = await fetch('/api/users');
const usersData = await usersResponse.json();
const users = makeCollection(User, usersData);

// Handle paginated data
const pageResponse = await fetch('/api/users?page=1');
const pageData = await response.json();
const paginator = makePaginator(User, pageData);
```

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

### Install Sutando with yarn

Source: https://sutando.org/guide/installation

Installs the Sutando library using yarn. Yarn is an alternative package manager for JavaScript.

```sh
$ yarn add sutando
```

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

### Initialize Sutando Project

Source: https://sutando.org/guide/migrations

Generates the `sutando.config.js` file for database connection and migration settings. Can be installed globally for command-line use.

```bash
npx sutando init
```

```bash
npm install -g sutando
sutando init
```

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

### Install Database Drivers with npm

Source: https://sutando.org/guide/installation

Installs various database drivers (e.g., pg, sqlite3, mysql, tedious) for use with Sutando via npm. Choose the driver that matches your database.

```sh
$ npm install pg --save
$ npm install sqlite3 --save
$ npm install better-sqlite3 --save
$ npm install mysql --save
$ npm install mysql2 --save
$ npm install tedious --save
```

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

### Install Database Drivers with yarn

Source: https://sutando.org/guide/installation

Installs various database drivers (e.g., pg, sqlite3, mysql, tedious) for use with Sutando via yarn. Choose the driver that matches your database.

```sh
$ yarn add pg
$ yarn add sqlite3
$ yarn add better-sqlite3
$ yarn add mysql
$ yarn add mysql2
$ yarn add tedious
```

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

### Install Database Drivers with pnpm

Source: https://sutando.org/guide/installation

Installs various database drivers (e.g., pg, sqlite3, mysql, tedious) for use with Sutando via pnpm. Choose the driver that matches your database.

```sh
$ pnpm add pg
$ pnpm add sqlite3
$ pnpm add better-sqlite3
$ pnpm add mysql
$ pnpm add mysql2
$ pnpm add tedious
```

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

### Install Sutando with pnpm

Source: https://sutando.org/guide/installation

Installs the Sutando library using pnpm. pnpm is a performant package manager for Node.js.

```sh
$ pnpm add sutando
```

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

### Install Sutando with npm

Source: https://sutando.org/guide/installation

Installs the Sutando library using npm. This is the primary package manager for Node.js environments.

```sh
$ npm install sutando --save
```

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

### Configure MySQL Connection with Sutando

Source: https://sutando.org/guide/installation

Adds a default MySQL connection to Sutando using the 'mysql2' client. This example demonstrates setting host, port, user, password, and database name. Multiple connections can be added by specifying a name.

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

sutando.addConnection({
  client: 'mysql2',
  connection: {
    host : '127.0.0.1',
    port : 3306,
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  },
});

// And you can add multiple connections, just specify the connection name.
sutando.addConnection({
  client: 'mysql2',
  connection: {
    host : '127.0.0.1',
    port : 3306,
    user : 'another_database_user',
    password : 'another_database_password',
    database : 'myapp_another'
  },
}, 'another_mysql');

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

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

### Using a Parameterized Sutando Plugin

Source: https://sutando.org/guide/plugin

Illustrates how to use a parameterized Sutando plugin, such as `HasSlug`, by passing configuration options during its integration. This example shows how to specify a custom database column name for the generated slug, making the plugin more versatile.

```javascript
const { Model, compose } = require('sutando');
const HasSlug = require('./plugins/sutando-slug');

class Post extends compose(
  Model,
  HasSlug({ column: 'custom_slug' })
) {
  // ...
}

const post = new Post;
post.title = 'The First Post Title';
await post.save();

console.log(post.custom_slug); // the-first-post-title
```

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

### Configure SQLite3 Connection with Flags

Source: https://sutando.org/guide/installation

Sets up a SQLite3 connection with specific flags, such as 'OPEN_URI' and 'OPEN_SHAREDCACHE', which can be used for advanced configurations like shared cache modes or URI-based file access.

```javascript
sutando.addConnection({
  client: 'sqlite3',
  connection: {
    filename: "file:memDb1?mode=memory&cache=shared",
    flags: ['OPEN_URI', 'OPEN_SHAREDCACHE']
  }
});
```

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

### Create Named Indexes

Source: https://sutando.org/guide/migrations

This example demonstrates how to customize index names during creation. You can provide a specific name as a second parameter to the 'index' or 'unique' methods. This aids in better index management and identification. Dependencies: Sutando schema builder.

```javascript
table.index(['name', 'last_name'], 'idx_name_last_name');
table.unique('email', {
   indexName: 'unique_email'
});
```

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

### Configure PostgreSQL Connection with Sutando

Source: https://sutando.org/guide/installation

Adds a PostgreSQL connection to Sutando, allowing specification of the database version. This is useful for connecting to non-standard PostgreSQL database versions or configurations.

```javascript
sutando.addConnection({
  client: 'pg',
  version: '7.2',
  connection: {
    host : '127.0.0.1',
    port : 3306,
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  }
});
```

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

### Chaining Collection Operations (JavaScript)

Source: https://sutando.org/guide/collections

Shows how to chain powerful map and reduce operations on Sutando collections. This example filters out inactive users and then extracts their names.

```javascript
const names = (await User.query().all()).reject(user => {
  return user.active === false;
}).map(user => {
  return user.name;
});
```

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

### Using a Custom Sutando Plugin with Hooks

Source: https://sutando.org/guide/plugin

Shows how to integrate a custom Sutando plugin, like the `HasSlug` mixin, into a model. This example uses the `compose` helper and demonstrates how the plugin automatically generates a slug from the `title` attribute during the `save` operation.

```javascript
const { Model, compose } = require('sutando');
const HasSlug = require('./plugins/sutando-slug');

class Post extends compose(
  Model,
  HasSlug
) {
  // ...
}

const post = new Post;
post.title = 'The First Post Title';
await post.save();

console.log(post.slug); // the-first-post-title
```

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

### Execute All Migrations

Source: https://sutando.org/guide/migrations

Runs all pending (unexecuted) database migrations using the `migrate:run` command.

```bash
npx sutando migrate:run
```

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

### Integrating Pagination in Express.js Route

Source: https://sutando.org/guide/pagination

Shows a practical example of how to use Sutando's pagination within an Express.js application. The `users` paginator instance is automatically serialized to JSON when sent in the response. It handles fetching the current page from the query parameters.

```javascript
const app = require('express')();

app.get('/', async (req, res) => {
  const users = await User.query().paginate(req.query.page || 1);

  res.send(users);
});
```

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

### Sutando Type Safety Notes and Examples (TypeScript)

Source: https://sutando.org/guide/typescript

Illustrates limitations in Sutando's automatic type inference for dynamic queries and relationship types. It shows examples where types might default to `any` or be incomplete.

```typescript
// Dynamic queries may not have accurate type inference
const result = await User.query()
  .select(['name', 'email'])
  .where('age', '>', 18)
  .first()

// Relationship query type inference might be incomplete
const userWithPosts = await User.query()
  .with('posts')
  .first()
```

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

### Rename and Delete Database Tables with Sutando

Source: https://sutando.org/guide/migrations

Provides examples of renaming a table from 'from' to 'to' using `renameTable` and dropping tables using `dropTable` and `dropTableIfExists` methods in Sutando migrations. `dropTableIfExists` is safer as it avoids errors if the table does not exist.

```javascript
await schema.renameTable(from, to);

await schema.dropTable('users');

await schema.dropTableIfExists('users');

```

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

### Creating a Basic Sutando Plugin (Class Mixin)

Source: https://sutando.org/guide/plugin

Illustrates how to create a custom Sutando plugin by implementing it as a class mixin. A mixin is a function that accepts a class and returns a subclass, allowing for modular extension of model behavior. This example creates a `HasSlug` mixin.

```javascript
// plugins/sutando-slug.js
const _ = require('lodash');

const HasSlug = (Model) => {
  return class extends Model {
    static booted() {
      // Execute booted of the parent class
      Model.booted();

      // Set the creating hook
      this.creating(model => {
        // If slug is not set, it will be automatically generated based on the title attribute
        if (model.slug === undefined) {
          model.slug = _.kebabCase(model.title);
        }
      });
    }
  }
}

module.exports = HasSlug;
```

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

### Create Foreign Key Constraint

Source: https://sutando.org/guide/migrations

This example demonstrates how to establish a foreign key constraint between two tables to enforce referential integrity. It defines a 'user_id' column in the 'posts' table that references the 'id' column in the 'users' table. Dependencies: Sutando schema builder.

```javascript
await schema.createTable('posts', (table) => {
   table.integer('user_id').unsigned().notNullable();
   table.string('title', 30);
   table.string('content');

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

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

### Define and Query Sutando Model Relationships

Source: https://sutando.org/guide/relationships

Provides an example of defining a `hasMany` relationship (`relationPosts`) on a Sutando `Model` and then querying that relationship with additional constraints. It highlights how Sutando relationships act as query builders, allowing chaining of methods like `where` and `get`.

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

class User extends Model {
  relationPosts() {
    return this.hasMany(Post);
  }
}
```

```javascript
const { User } = require('./models');

const user = await User.query().find(1);

await user.related('posts').where('active', 1).get();
```

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

### Configure SQLite3/Better-SQLite3 File Connection

Source: https://sutando.org/guide/installation

Configures Sutando to connect to a SQLite3 database file. The 'filename' option specifies the path to the database file. This can be used with either 'sqlite3' or 'better-sqlite3' clients.

```javascript
sutando.addConnection({
  client: 'sqlite3', // or 'better-sqlite3'
  connection: {
    filename: "./mydb.sqlite"
  }
});
```

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

### Iterate and Map Sutando Collections (JavaScript)

Source: https://sutando.org/guide/collections

Demonstrates iterating over Sutando collections using for...of loops and the map function, similar to arrays. Collections are returned by methods like `get` when multiple models are retrieved.

```javascript
const { User } = require('./models');

const users = await User.query().where('active', 1).get();

users.map(user => {
  console.log(user.name);
});

for (let user of users) {
  console.log(user.name);
}
```

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

### Configure In-Memory SQLite3/Better-SQLite3 Connection

Source: https://sutando.org/guide/installation

Configures Sutando to use an in-memory SQLite3 database. By setting the 'filename' to ':memory:', the database resides entirely in RAM, offering high performance for temporary data.

```javascript
sutando.addConnection({
  client: 'sqlite3', // or 'better-sqlite3'
  connection: {
    filename: ":memory:"
  }
});
```

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

### Create Index After Column Definition

Source: https://sutando.org/guide/migrations

This example shows how to create an index after the column has already been defined. The 'unique' method is called on the structure builder, passing the column name as an argument. This is an alternative approach to defining unique constraints. Dependencies: Sutando schema builder.

```javascript
table.unique('email');
```

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

### Define Dynamic Scopes with Parameters in Sutando

Source: https://sutando.org/guide/models

Explains how to create dynamic scopes that accept parameters. Additional parameters are added to the scope method signature after the 'query' parameter.

```javascript
const { Model } = require('./models');

class User extends Model {
  scopeOfType(query, type){
    return query.where('type', type);
  }
}
```

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

### Create CREATED_AT and UPDATED_AT Columns

Source: https://sutando.org/guide/migrations

Creates two TIMESTAMP equivalent columns, `created_at` and `updated_at`, automatically managed by the database.

```javascript
table.timestamps();

```

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

### Specifying a String Key Type for Primary Keys

Source: https://sutando.org/guide/models

Illustrates how to define the `keyType` property as 'string' for Sutando models when the primary key is not an integer, such as UUIDs or other string-based identifiers.

```javascript
class Flight extends Model {
  // The data type of the auto-incrementing ID.
  keyType = 'string';
}
```

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

### Utilize Dynamic Scopes with Arguments in Sutando

Source: https://sutando.org/guide/models

Shows how to pass arguments when calling a dynamic scope. The arguments are passed to the scope method after the query builder instance.

```javascript
const users = await User.query().ofType('admin').get();
```

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

### Configure Sutando Database Connection

Source: https://sutando.org/guide/migrations

Defines database connection details, including client type, host, user, and password. Supports multiple named connections.

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

module.exports = {
   client: 'mysql2',
   connection: {
     host: 'localhost',
     database: 'database',
     user: 'root',
     password: 'password'
   },
  // You can add multiple connections, just specify the connection name.
  connections: {
    pgsql: {
      client: 'pg',
      connection: {
        host: 'localhost',
        database: 'another_database',
        user:     'root',
        password: 'password'
      }
    }
  },
   migrations: {
     table: 'migrations',
     path: 'migrations'
   },
   models: {
     path: 'models',
   }
};
```

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

### Compare Model Instances in Sutando

Source: https://sutando.org/guide/models

Details the `is` and `isNot` methods for comparing two model instances. These methods check if models share the same primary key, table, and database connection.

```javascript
if (post.is(anotherPost)) {
  //
}
 
if (post.isNot(anotherPost)) {
  //
}
```

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

### Creating a Basic Sutando Model

Source: https://sutando.org/guide/models

Defines a fundamental Sutando model by extending the base Model class. This sets up the foundation for interacting with a database table using active record conventions.

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

class Flight extends Model {
  //
}
```

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

### Customizing Timestamp Column Names in Sutando

Source: https://sutando.org/guide/models

Demonstrates how to customize the names of the columns used for tracking creation and update times in Sutando models by setting the static `CREATED_AT` and `UPDATED_AT` properties.

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

class Flight extends Model {
  static CREATED_AT = 'creation_date';
  static UPDATED_AT = 'updated_date';
}
```

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

### Query Soft Deleted Models in Sutando

Source: https://sutando.org/guide/models

Illustrates how to include soft-deleted models in query results using the `withTrashed` method. It also covers how to retrieve only soft-deleted models with the `onlyTrashed` method.

```javascript
const { Flight } = require('./models');

const flights = await Flight.query().withTrashed()
  .where('account_id', 1)
  .get();
```

```javascript
await flight.related('history').withTrashed().get();
```

```javascript
const flights = await Flight.query().onlyTrashed()
  .where('airline_id', 1)
  .get();
```

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

### Specify Migration Database Connection

Source: https://sutando.org/guide/migrations

Allows a migration to use a specific database connection defined in the configuration file by setting the `connection` property of the migration class.

```javascript
module.exports = class extends Migration {
  connection = 'pgsql';

  /**
   * Run the migrations.
   */
  async up(schema) {
    // ...
  }
}
```

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

### Specifying a Custom Database Connection for a Model

Source: https://sutando.org/guide/models

Explains how to assign a Sutando model to a specific database connection that differs from the default. This is achieved by defining the `connection` property on the model.

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

class Flight extends Model {
  connection = 'sqlite';
}
```

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

### Retrieve Fresh Model Instances with `fresh` (JavaScript)

Source: https://sutando.org/guide/collections

The `fresh` method re-fetches all models in the collection from the database, ensuring they are up-to-date. It also supports eager loading of specified relationships.

```javascript
const newUsers = await users.fresh();

const newUsers = await users.fresh('comments');
```

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

### Build and Execute Sutando ORM Queries

Source: https://sutando.org/guide/models

Demonstrates how to build complex queries by chaining methods like `.where()`, `.orderBy()`, and `.take()` before executing the query with `.get()`, `.first()`, or `.find()`. These methods allow for targeted data retrieval, returning either a Collection or a single model instance. Input is defined by query constraints, and output is data matching those constraints.

```javascript
const flights = await Flight.query().where('active', 1)
  .orderBy('name')
  .take(10)
  .get();

const flight = await Flight.query().where('active', 1).first();

const flight = await Flight.query().find(5);
```

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

### Using Sutando Plugins with `compose`

Source: https://sutando.org/guide/plugin

Demonstrates how to load multiple plugins into a Sutando model using the `compose` helper function for cleaner code and better organization. Plugins extend model functionality, such as soft deletion and unique ID generation.

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

class User extends compose(Model, SoftDeletes) {}

class Post extends compose(Model, SoftDeletes, HasUniqueIds) {}
```

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

### Get Model Primary Keys with `modelKeys` (JavaScript)

Source: https://sutando.org/guide/collections

The `modelKeys` method efficiently extracts the primary keys from all models contained within a Sutando collection, returning them as an array.

```javascript
users.modelKeys();

// [1, 2, 3, 4, 5]
```

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

### Create Database Table with Sutando Migration

Source: https://sutando.org/guide/migrations

Demonstrates how to create a new database table named 'users' with 'id', 'name', 'email', and timestamp columns using Sutando's `createTable` method within a migration file. It also includes methods for reversing the migration by dropping the table.

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

module.exports = class extends Migration {
   /**
     * Run the migrations.
     */
   async up(schema) {
     await schema.createTable('users', (table) => {
       table.increments('id');
       table.string('name');
       table.string('email');
       table.timestamps();
     });
   }

   /**
     * Reverse the migrations.
     */
   async down(schema) {
     await schema.dropTableIfExists('users');
   }
};

```

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

### Build Basic WHERE Clauses with Operators (JavaScript)

Source: https://sutando.org/guide/query-builder

Illustrates how to construct basic 'where' clauses using the `where` method. It covers specifying column, operator, and value, as well as shorthand for equality checks and using various operators like '>=', '<>', and 'like'.

```javascript
const users = await db.table('users')
  .where('votes', '=', 100)
  .where('age', '>', 35)
  .get();

const users = await db.table('users').where('votes', 100).get();

const users = await db.table('users')
  .where('votes', '>=', 100)
  .get();

const users = await db.table('users')
  .where('votes', '<>', 100)
  .get();

const users = await db.table('users')
  .where('name', 'like', 'T%')
  .get();
```

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

### Creating a Parameterized Sutando Plugin

Source: https://sutando.org/guide/plugin

Demonstrates how to create a flexible Sutando plugin that accepts parameters, such as a custom column name for the slug. This allows the plugin to be reused with different database field names, enhancing its adaptability.

```javascript
// plugins/sutando-slug.js
const _ = require('lodash');

const HasSlug = ({ column }) => (Model) => {
  return class extends Model {
    static booted() {
      // Execute booted of the parent class
      Model.booted();

      // Set the creating hook
      this.creating(model => {
        // If slug is not set, it will be automatically generated based on the title attribute
        if (model[column] === undefined) {
          model[column] = _.kebabCase(model.title);
        }
      });
    }
  }
}

module.exports = HasSlug;
```

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

### Basic Pagination with `paginate` Method

Source: https://sutando.org/guide/pagination

Demonstrates how to paginate query results using the `paginate` method in Sutando. It accepts the page number and rows per page as arguments. The `paginate` method returns a `Paginator` instance. Ensure the `users` table and `User` model are correctly defined.

```javascript
const users = await db.table('users')
  .where('vote', '>', 1)
  .paginate(2, 15); // instanceof Paginator

const users = await User.query()
  .where('vote', '>', 1)
  .paginate(1, 15); // instanceof Paginator
```

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

### Define Accessor for Custom Attribute (JavaScript)

Source: https://sutando.org/guide/serialization

Defines a custom accessor for a model attribute that doesn't directly map to a database column. This accessor provides logic for how the attribute's value is derived. It uses the `Attribute.make` method with a `get` function.

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

class User extends Model {
  attributeIsAdmin() {
    return Attribute.make({
      get: (value, attributes) => (attributes.admin === 'yes')
    });
  }
}
```

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

### Retrieve or Create/New Models with Sutando

Source: https://sutando.org/guide/models

Explains the firstOrCreate and firstOrNew methods for efficiently handling model retrieval and creation. firstOrCreate attempts to find a record and creates it if it doesn't exist, while firstOrNew returns a new instance if no record is found, requiring manual saving.

```javascript
const { Flight } = require('./modles');

// Retrieve flight by name or create it if it doesn't exist...
const flight = await Flight.query().firstOrCreate({
  name: 'London to Paris'
});

// Retrieve flight by name or create it with the name, delayed, and arrival_time attributes...
const flight = await Flight.query().firstOrCreate(
  { name: 'London to Paris' },
  { delayed: 1, arrival_time: '11:30' }
);

// Retrieve flight by name or instantiate a new Flight instance...
const flight = await Flight.query().firstOrNew({
  name: 'London to Paris'
});

// Retrieve flight by name or instantiate with the name, delayed, and arrival_time attributes...
const flight = await Flight.query().firstOrNew(
  { name: 'Tokyo to Sydney' },
  { delayed: 1, arrival_time: '11:30' }
);
```

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

### SQL Queries for Eager Loading Optimization

Source: https://sutando.org/guide/relationships

These SQL queries illustrate the optimized database interaction achieved through eager loading. The first query retrieves all books, and the second efficiently fetches all necessary authors using an 'IN' clause, contrasting with the N+1 query approach.

```sql
select * from books
 
select * from authors where id in (1, 2, 3, 4, 5, ...)
```

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

### Fetching Paginated Data with `forPage` Method

Source: https://sutando.org/guide/pagination

Shows how to fetch paginated data using the `forPage` method, which returns results as an Array or Collection. This method is useful when you only need the data for a specific page without the full paginator metadata. Ensure the `users` table and `User` model are correctly defined.

```javascript
const users = await db.table('users')
  .where('vote', '>', 1)
  .forPage(2, 15)
  .get(); // instanceof Array

const users = await User.query()
  .where('vote', '>', 1)
  .forPage(1, 15)
  .get(); // instanceof Collection
```

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

### Declaring Multiple Hooks in Sutando Model's booted Method

Source: https://sutando.org/guide/hooks

This example shows how to declare multiple Sutando model hooks, including 'creating' and 'created', within the static `booted` method of a model. This approach is useful for organizing hook declarations.

```javascript
class User {
  static booted() {
    this.creating(user => {
      //
    });

    this.created(user => {
      //
    });
  }
}
```

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

### Dynamic Where Clauses (WhereX) in JavaScript

Source: https://sutando.org/guide/query-builder

Introduces the 'WhereX' pattern, allowing for dynamic construction of `where` clauses based on method names. This simplifies queries by directly mapping column names and values to methods.

```javascript
const users = await User.query().whereApproved(1).get();
const posts = await Post.query().whereViewsCount('>', 100).get();
```

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

### Restore Soft Deleted Models in Sutando

Source: https://sutando.org/guide/models

Demonstrates how to restore a single soft-deleted model by calling the `restore` method on the model instance. It also shows how to perform mass restoration using a query builder and restore related models.

```javascript
await flight.restore();
```

```javascript
await Flight.query().withTrashed()
  .where('airline_id', 1)
  .restore();
```

```javascript
await flight.related('history').restore();
```

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

### Drop Single Column

Source: https://sutando.org/guide/migrations

Removes a specific column from a table. This operation is irreversible.

```javascript
await schema.table('users', (table) => {
   table.dropColumn('votes');
});

```

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

### Check Migration Status

Source: https://sutando.org/guide/migrations

Displays the current status of all migrations, indicating which have been executed and which are pending.

```bash
npx sutando migrate:status
```

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

### Eager Load Multiple Relationships in Sutando

Source: https://sutando.org/guide/relationships

This example demonstrates how to eager load multiple relationships ('author' and 'publisher') for a model by passing an array of relationship names to the 'with' method. This further optimizes data retrieval by loading all necessary related data in a single query set.

```javascript
const books = await Book.query().with(['author', 'publisher']).get();
```

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

### Place Column First

Source: https://sutando.org/guide/migrations

Places a new column as the very first column in the table. This modifier is specific to MySQL.

```javascript
table.string('uuid').first();

```

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

### Check Table and Column Existence in Sutando

Source: https://sutando.org/guide/migrations

Illustrates how to verify the existence of a database table and a specific column within that table using Sutando's `hasTable` and `hasColumn` methods. These methods are asynchronous and return a boolean.

```javascript
if (await schema.hasTable('users')) {
   // "users" table exists...
}

if (await schema.hasColumn('users', 'email')) {
   // The "users" table exists and has the "email" column...
}

```

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

### Set Column After Another

Source: https://sutando.org/guide/migrations

Places a new column immediately after an existing column. This modifier is specific to MySQL.

```javascript
table.string('email').after('name');

```

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

### Create POINT Column

Source: https://sutando.org/guide/migrations

Creates a column of type POINT. This is typically used for storing 2D coordinates.

```javascript
table.point('position');

```

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

### Create JSON Column

Source: https://sutando.org/guide/migrations

Creates a column of type JSON. This is used for storing JSON data directly.

```javascript
table.json('options');

```

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

### Rollback Last Migration Batch

Source: https://sutando.org/guide/migrations

Reverts the most recent batch of executed migrations. This can be undone by running the migrations again.

```bash
npx sutando migrate:rollback
```

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

### Manual Transaction Control

Source: https://sutando.org/guide/transactions

Manually control the lifecycle of a database transaction using beginTransaction, rollback, and commit methods.

```APIDOC
## Manual Transaction Control

### Description
Provides granular control over database transactions, allowing manual initiation, rollback, and commit.

### Methods
- `db.beginTransaction()`: Initiates a new transaction and returns a transaction object.
- `trx.rollback()`: Rolls back the current transaction.
- `trx.commit()`: Commits the current transaction.

### Parameters
None for `beginTransaction`.

### Request Example (Initiating Transaction)
```javascript
const { sutando } = require('sutando');

const db = sutando.connection();
const trx = await db.beginTransaction();
```

### Request Example (Full Control)
```javascript
const { sutando } = require('sutando');

const db = sutando.connection();
const trx = await db.beginTransaction();

try {
  const user = new User;
  user.name = 'Sally';

  await user.save({
    client: trx,
  });

  await trx.commit();
} catch (e) {
  await trx.rollback();

  console.log(e.stack);
}
```

### Response
#### Success Response (200)
Transaction committed successfully via `trx.commit()`.

#### Error Response
Transaction rolled back via `trx.rollback()` due to an exception.
```

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

### Utilize and Chain Query Scopes in Sutando

Source: https://sutando.org/guide/models

Demonstrates how to apply defined scopes to model queries by calling the scope method without the 'scope' prefix. It also shows how to chain multiple scopes and use closures for 'or' conditions.

```javascript
const { User } = require('./models');

const users = await User.query().popular().active().orderBy('created_at').get();
```

```javascript
const users = await User.query().popular().orWhere(query => {
  query.active();
}).get();
```

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

### Set Column to Unsigned

Source: https://sutando.org/guide/migrations

Sets an integer-based column to be unsigned, meaning it can only store non-negative values. This is specific to MySQL.

```javascript
table.integer('age').unsigned();

```

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

### Serializing Paginator to Object/JSON

Source: https://sutando.org/guide/pagination

Demonstrates how to serialize the `Paginator` instance to a JSON-compatible object using `toData` or `toJson`. This is useful for sending pagination data in API responses. The output keys are snake_case by default.

```json
{
  "total": 45,
  "per_page": 15,
  "current_page": 1,
  "last_page": 3,
  "count": 15,
  "data": [
    {
      // Record...
    },
    {
      // Record...
    }
  ]
}
```

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

### Set Default Column Value

Source: https://sutando.org/guide/migrations

Assigns a default value to a column, which will be used if no value is explicitly provided during insertion.

```javascript
table.integer('status').defaultTo(0);

```

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

### Set Column Charset

Source: https://sutando.org/guide/migrations

Specifies the character set for a column. This is particularly relevant for string-based columns and is specific to MySQL.

```javascript
table.string('name').charset('utf8mb4');

```

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

### Update Existing Records with Sutando

Source: https://sutando.org/guide/models

Demonstrates how to update existing records in the database using Sutando. It involves retrieving a model, modifying its attributes, and then calling the save method. The `updated_at` timestamp is automatically handled.

```javascript
const { Flight } = require('./model');

const flight = await Flight.query().find(1);
flight.name = 'Paris to London';
await flight.save();
```

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

### Make Column Nullable

Source: https://sutando.org/guide/migrations

Adds a column that allows NULL values. This is a common modifier used when a field is optional.

```javascript
await schema.table('users', (table) => {
   table.string('email').nullable();
})

```

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

### Eager Load Relationship Counts and Aggregates (JavaScript)

Source: https://sutando.org/guide/collections

Demonstrates methods for eager loading relationship counts (`loadCount`) and aggregate values like max, min, sum, and average (`loadMax`, `loadMin`, `loadSum`, `loadAvg`) for models in a collection.

```javascript
await users.loadCount(['comments', 'posts']);

await users.loadMax('posts', 'vote');

await users.loadMin('posts', 'vote');

await users.loadSum('posts', 'vote');

await users.loadAvg('posts', 'vote');
```

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

### Create TEXT Column

Source: https://sutando.org/guide/migrations

Creates a column of type TEXT. This is suitable for storing large amounts of character data.

```javascript
table.text('description');

```

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

### Perform Inner Join on Multiple Tables (JavaScript)

Source: https://sutando.org/guide/query-builder

Demonstrates how to use the `join` method to perform an inner join on multiple tables. The method specifies the table to join and the join conditions. This is useful for retrieving data that spans across several related tables.

```javascript
const users = await db.table('users')
  .join('contacts', 'users.id', '=', 'contacts.user_id')
  .join('orders', 'users.id', '=', 'orders.user_id')
  .select('users.*', 'contacts.phone', 'orders.price')
  .get();
```

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

### Create VARCHAR Column

Source: https://sutando.org/guide/migrations

Creates a column of type VARCHAR with a specified maximum length. This is equivalent to VARCHAR(length).

```javascript
table.string('name', 100);

```

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

### Insert New Records with Sutando

Source: https://sutando.org/guide/models

Illustrates two ways to insert new records into the database using Sutando. The first method involves instantiating a model, setting attributes, and calling save. The second method uses the create method for a more concise insertion.

```javascript
// express
const { Flight } = require('./model');

app.post('/flights', async (req, res) => {
  // Validate the request...

  const flight = new Flight;
  flight.name = req.name;
  await flight.save();

  res.send(flight);
});
```

```javascript
const { Flight } = require('./model');

const flight = await Flight.query().create({
  name: 'London to Paris',
});
```

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

### Drop Multiple Columns

Source: https://sutando.org/guide/migrations

Removes multiple columns from a table in a single operation. This is more efficient than calling `dropColumn` multiple times.

```javascript
await schema.table('users', (table) => {
   table.dropColumns('votes', 'avatar', 'location');
});

```

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

### Run Raw SQL Queries with Sutando

Source: https://sutando.org/guide/query-builder

Execute raw SQL queries against the database using the `raw` method. This is useful for executing specific SQL commands not directly covered by the query builder's fluent interface. The response format depends on the underlying SQL library.

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

const response = await db.raw('SET TIME_ZONE = ?', ['UTC']);
```

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

### Multi-Column Sorting with orderBy in JavaScript

Source: https://sutando.org/guide/query-builder

Shows how to sort query results by multiple columns by chaining multiple `orderBy` calls. The order of calls determines the sorting priority.

```javascript
const users = await db.table('users')
  .orderBy('name', 'desc')
  .orderBy('email', 'asc')
  .get();
```

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

### Create GEOMETRY Column

Source: https://sutando.org/guide/migrations

Creates a column of type GEOMETRY. This method is a direct equivalent to the GEOMETRY SQL data type.

```javascript
table.geometry('positions');

```

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

### Configure MySQL Table Engine, Charset, and Collation with Sutando

Source: https://sutando.org/guide/migrations

Shows how to specify the storage engine (e.g., 'InnoDB'), character set ('utf8mb4'), and collation ('utf8mb4_unicode_ci') for MySQL tables during creation using Sutando's migration builder. Table comments are also supported for MySQL and PostgreSQL.

```javascript
await schema.createTable('users', (table) => {
   table.engine('InnoDB');

   // ...
});

await schema.createTable('users', (table) => {
   table.charset('utf8mb4');
   table.collate('utf8mb4_unicode_ci');

   // ...
});

await schema.createTable('calculations', (table) => {
   table.comment('Business calculations');

   // ...
});

```

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

### Rename Column

Source: https://sutando.org/guide/migrations

Renames an existing column in a table. This method takes the old column name and the new column name as arguments.

```javascript
await schema.table('users', (table) => {
   table.renameColumn('from', 'to');
});

```

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

### Delete Model Instance in Sutando JS

Source: https://sutando.org/guide/models

Deletes a specific model instance from the database after retrieving it using a query. This is a direct deletion of the record.

```javascript
const { Flight } = require('./models');

const flight = await Flight.query().find(1);
await flight.delete();
```

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

### Limit and Offset Query Results with limit()/offset()

Source: https://sutando.org/guide/query-builder

The `limit` and `offset` methods provide an alternative way to control query result set size, functionally equivalent to `take` and `skip` respectively. `offset` skips a specified number of records, and `limit` restricts the maximum number of records returned, commonly used for implementing pagination.

```javascript
const users = await db.table('users')
  .offset(10)
  .limit(5)
  .get();
```

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

### Create JSONB Column

Source: https://sutando.org/guide/migrations

Creates a column of type JSONB (JSON Binary). This format is more efficient for querying and storing JSON data.

```javascript
table.jsonb('options');

```

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

### Limit and Offset Query Results with skip()/take()

Source: https://sutando.org/guide/query-builder

The `skip` and `take` methods are used to control the number of results returned by a query. `skip` defines the number of records to bypass from the beginning of the result set, while `take` specifies the maximum number of records to include. They are essential for pagination.

```javascript
const users = await db.table('users').skip(10).take(5).get();
```

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

### Create INTEGER Column

Source: https://sutando.org/guide/migrations

Creates a column of type INTEGER. This is suitable for storing whole numbers within the standard integer range.

```javascript
table.integer('votes');

```

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

### Create Auto-Incrementing Primary Key Column

Source: https://sutando.org/guide/migrations

Creates an auto-incrementing column that also serves as the primary key. This is equivalent to UNSIGNED INTEGER.

```javascript
table.increments('id');

```

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

### Retrieve Aggregate Data with Sutando

Source: https://sutando.org/guide/models

Shows how to use aggregate methods like count, sum, and max provided by the Sutando query builder. These methods return scalar values instead of model instances, useful for summary statistics.

```javascript
const count = await Flight.query().where('active', 1).count(); // 100

const max = await Flight.query().where('active', 1).max('price'); // 104

const flight = await Flight.query().find(1); // flight instanceof Flight
```

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

### Add Column Comment

Source: https://sutando.org/guide/migrations

Adds a descriptive comment to a column, which can be useful for documentation or understanding the column's purpose. Supported by MySQL and PostgreSQL.

```javascript
table.string('name').comment('User full name');

```

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

### Perform Left and Right Joins (JavaScript)

Source: https://sutando.org/guide/query-builder

Shows how to execute 'left' and 'right' join operations using `leftJoin` and `rightJoin` methods respectively. These methods maintain the structure of the left or right table while matching records from the other, similar to the standard `join` method.

```javascript
const users = await db.table('users')
  .leftJoin('posts', 'users.id', '=', 'posts.user_id')
  .get();

const users = await db.table('users')
  .rightJoin('posts', 'users.id', '=', 'posts.user_id')
  .get();
```

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

### Set Column Collation

Source: https://sutando.org/guide/migrations

Specifies the collation for a column, which defines rules for sorting and comparing characters. Supported by MySQL, PostgreSQL, and SQL Server.

```javascript
table.string('name').collate('utf8_unicode_ci');

```

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

### Generate New Migration File

Source: https://sutando.org/guide/migrations

Creates a new migration file in the specified directory (default 'migrations'). The file name includes a timestamp for ordering. An optional `--path` flag can specify a custom directory.

```bash
npx sutando migrate:make create_flights_table
```

```bash
npx sutando migrate:make --path=database/migrations create_users_table
```

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

### Rollback Specific Number of Migrations

Source: https://sutando.org/guide/migrations

Rolls back a specified number of the most recent migrations using the `step` parameter with the `migrate:rollback` command.

```bash
npx sutando migrate:rollback --step=5
```
Tokens:
Raw