0.1.4 • Published 1 year ago

mysql-query-builder-ts v0.1.4

Weekly downloads
-
License
ISC
Repository
github
Last release
1 year ago

Query Builder

Create Reference Database

const dbname = Database('dbname');

Create Table Schema

dbname.table('User', {
  code: 'pk',
  name: 'string',
});

dbname.table('Rating', {
  id: 'pk_auto_increment',
  user_code: 'fk',
  note: 'number',
});

Data Manipulation

SQL Commands

  • INSERT
    // INSERT INTO dbname.User (code, name) VALUES ('1234', 'Jhon');
    Models.User
      .insert({
        code: '1234',
        name: 'Jhon'
      })
      .exec();
  • UPDATE
    // UPDATE dbname.User SET name = 'Zoe' WHERE User.code = '1234';
    Models.User
      .update({
        name: 'Zoe',
      })
      .where({
        column: 'User.code',
        operator: '=',
        data: '1234'
      })
      .exec();
  • DELETE
    // DELETE FROM dbname.User WHERE User.code = '1234';
    Models.User
      .delete()
      .where({
        column: 'User.code',
        operator: '=',
        data: '1234'
      })
      .exec();

Data Query

SQL Commands

  • SELECT
    • All attributes
      // SELECT * FROM dbname.User;
      Models.User
        .select()
        .exec();
    • Only attributes
      // SELECT User.name FROM dbname.User;
      Models.User
        .select('User.name')
        .exec();

Join Clauses

  • INNER JOIN
    // SELECT * FROM dbname.Rating INNER JOIN dbname.User ON dbname.User.code = dbname.Rating.user_code;
    Models.Rating
      .join<Models.UserColumns>({
        type: 'INNER',
        table: Models.User.name,
        leftColumn: 'User.code',
        rightColumn: 'Rating.user_code'
      })
      .select()
      .exec();
  • LEFT JOIN
    // SELECT * FROM dbname.Rating LEFT JOIN dbname.User ON dbname.User.code = dbname.Rating.user_code;
    Models.Rating
      .join<Models.UserColumns>({
        type: 'LEFT',
        table: Models.User.name,
        leftColumn: 'User.code',
        rightColumn: 'Rating.user_code'
      })
      .select()
      .exec();
  • RIGHT JOIN
    // SELECT * FROM dbname.Rating RIGHT JOIN dbname.User ON dbname.User.code = dbname.Rating.user_code;
    Models.Rating
      .join<Models.UserColumns>({
        type: 'RIGHT',
        table: Models.User.name,
        leftColumn: 'User.code',
        rightColumn: 'Rating.user_code'
      })
      .select()
      .exec();

Clauses

  • WHERE
    // SELECT * FROM dbname.User WHERE User.code = '1234';
    Models.User
      .select()
      .where({
        column: 'User.code',
        operator: '=',
        data: '1234',
      })
      .exec();
  • GROUP BY
  • HAVING
  • ORDER BY
  • DISTINCT
  • UNION

Logical Operators

  • AND

    // SELECT * FROM dbname.User WHERE User.code = '1234' AND User.name = 'Jhon';
    Models.User
      .select()
      .where({
        column: 'User.code',
        operator: '=',
        data: '1234',
      })
      .and({
        column: 'User.name',
        operator: '=',
        data: 'Jhon',
      })
      .exec();
    
    // SELECT * FROM dbname.User WHERE User.code = '1234' AND ( User.name = 'Jhon' OR User.name = 'Zoe' );
    Models.User
      .select()
      .where({
        column: 'User.code',
        operator: '=',
        data: '1234',
      })
      .and({
        column: 'User.name',
        operator: '=',
        data: 'Jhon',
        priority: 'START',
      }).or({
        column: 'User.name',
        operator: '=',
        data: 'Zoe',
        priority: 'END',
      })
      .exec();
  • OR

    // SELECT * FROM dbname.User WHERE User.code = '1234' OR User.name = 'Jhon';
    Models.User
      .select()
      .where({
        column: 'User.code',
        operator: '=',
        data: '1234',
      })
      .or({
        column: 'User.name',
        operator: '=',
        data: 'Jhon',
      })
      .exec();
    
    // SELECT * FROM dbname.User WHERE User.code = '1234' OR ( User.name = 'Jhon' AND User.name = 'Zoe' );
    Models.User
      .select()
      .where({
        column: 'User.code',
        operator: '=',
        data: '1234',
      })
      .or({
        column: 'User.name',
        operator: '=',
        data: 'Jhon',
        priority: 'START',
      }).and({
        column: 'User.name',
        operator: '=',
        data: 'Zoe',
        priority: 'END',
      })
      .exec();
  • NOT

Relational Operators

  • EQUAL
    // SELECT * FROM dbname.User WHERE Rating.note = 1;
    Models.Rating
      .select()
      .where({
        column: 'Rating.note',
        operator: '=', // Equal Operator
        data: 1,
      })
      .exec();
  • GREATER THAN
    // SELECT * FROM dbname.User WHERE Rating.note > 1;
    Models.Rating
      .select()
      .where({
        column: 'Rating.note',
        operator: '>', // Greater Than operator
        data: 1,
      })
      .exec();
  • LESS THAN
    // SELECT * FROM dbname.User WHERE Rating.note < 1;
    Models.Rating
      .select()
      .where({
        column: 'Rating.note',
        operator: '<', // Less Than operator
        data: 1,
      })
      .exec();
  • GREATER THAN OR EQUAL
    // SELECT * FROM dbname.User WHERE Rating.note >= 1;
    Models.Rating
      .select()
      .where({
        column: 'Rating.note',
        operator: '>=', // Greater Than Or Equal operator
        data: 1,
      })
      .exec();
  • LESS THAN OR EQUAL
    // SELECT * FROM dbname.User WHERE Rating.note <= 1;
    Models.Rating
      .select()
      .where({
        column: 'Rating.note',
        operator: '<=', // Less Than Or Equal operator
        data: 1,
      })
      .exec();
  • DIFFERENT THAN
    // SELECT * FROM dbname.User WHERE Rating.note <> 1;
    Models.Rating
      .select()
      .where({
        column: 'Rating.note',
        operator: '<>', // Different Than operator
        data: 1,
      })
      .exec();
  • LIKE
    // SELECT * FROM dbname.User WHERE User.name LIKE 'Jh%';
    Models.User
      .select()
      .where({
        column: 'User.name',
        operator: 'LIKE',
        data: {
          data: 'Jh',
          likeOperator: 'STARTS',
          not: false,
        }
      })
      .exec();
  • BETWEEN
    // SELECT * FROM dbname.Rating WHERE Rating.note BETWEEN 3 AND 5;
    Models.Rating
      .select()
      .where({
        column: 'Rating.note',
        operator: 'BETWEEN',
        data: {
          rangeStart: 3,
          rangeEnd: 5,
          not: false,
        },
      })
      .exec();
  • IN
    // SELECT * FROM dbname.User WHERE User.name IN ('Jhon', 'Zoe');
    Models.User
      .select()
      .where({
        column: 'User.name',
        operator: 'IN',
        data: {
          data: ['Jhon', 'Zoe'],
          not: false,
        }
      })
      .exec();

Aggregation Functions

  • AVG
  • COUNT
  • SUM
  • MAX
  • MIN

License

mysql-query-builder-ts is licensed under the MIT license. MIT

0.1.2

1 year ago

0.1.4

1 year ago

0.1.3

1 year ago

0.1.1

1 year ago

0.1.0

2 years ago

0.0.3

2 years ago

0.0.2

2 years ago

0.0.1

2 years ago