🎲 This is a basic ORM project created from the mysql2/promise

   npm i mysql2-orm

• ES Modules

  import { MySQL } from 'mysql2-orm';

• CommonJS

  const { MySQL } = require('mysql2-orm');

• TypeScript

  import { MySQL } from 'mysql2-orm';
  import { ... } from 'mysql2-orm/types';

  • You will need to install @types/node

    • npm i @types/node -D

const mysql = new MySQL({
   host: 'localhost',
   port: 3306,
   user: 'user',
   password: 'pass',
   database: 'dbname',
});

await mysql.end();

• Select all rows

  await mysql.select({
     table: 'pokemons',
  });
  
  // Returns an array with the results

  SELECT * FROM `pokemons`;

• Select specific rows

  await mysql.select({
     columns: ['name', 'type'],
     table: 'pokemons',
     where: 'type IN(?, ?)',
     params: ['water', 'grass'],
     limit: 2,
     orderBy: ['name', 'DESC'],
  });
  
  // Returns an array with the results

  SELECT `name`, `type` FROM `pokemons` WHERE type IN(?, ?) ORDER BY `name` DESC LIMIT 2;
  
  -- params: [ 'water', 'grass' ]

• Count all rows

  await mysql.select({
     columns: 'COUNT(*) AS `total`',
     table: 'pokemons',
     limit: 1,
  });
  
  // Because "limit: 1", it returns a direct object with the result: { total: ... }

  SELECT COUNT(*) AS `total` FROM `pokemons` LIMIT 1;

• JOIN: inner | left | right | cross

  await mysql.select({
     columns: ['pokemons.name', 'pokemons.type'],
     table: 'captureds',
     join: {
        type: 'left',
        // outer: false,
        table: 'pokemons',
        on: {
           a: 'captureds.pokemonId',
           b: 'pokemons.id',
        },
     },
     where: 'userId = ?',
     params: [1],
  });
  
  // Returns an array with the results

  SELECT `pokemons`.`name`, `pokemons`.`type`
     FROM `captureds`
     LEFT JOIN `pokemons`
        ON `captureds`.`pokemonId` = `pokemons`.`id`
     WHERE userId = ?
  ;
  
  -- params: [ 1 ]

  • The join option accetps a direct object or an array with multiple JOIN

distinct, columns, join, where, params, limit and orderBy are optionals
columns: the default value is '*' and accepts a string or an array with the columns
orderBy: [ 'column' ] or [ 'column', 'ASC' | 'DESC' ]

• Insert a single row

  await mysql.insert({
     table: 'pokemons',
     values: {
        name: 'Mew',
        type: 'psychic',
     },
  });
  
  // Returns: last insert id

  INSERT INTO `pokemons` (`name`, `type`) VALUES (?, ?);
  
  -- params: [ 'Mew', 'psychic' ]

• Insert multiple rows

  await mysql.insert({
     table: 'pokemons',
     values: [
        { name: 'Pichu', type: 'electric' },
        { name: 'Mewtwo', type: 'psychic' },
     ],
  });
  
  // Returns: first row id from multiple insert

  INSERT INTO `pokemons` (`name`, `type`) VALUES (?, ?), (?, ?);
  
  -- params: [ 'Pichu', 'electric', 'Mewtwo', 'psychic' ]

• Example

  await mysql.update({
     table: 'pokemons',
     set: {
        name: 'Squirtle',
        type: 'water',
     },
     where: 'id = ?',
     params: [2],
     limit: 1,
  });
  
  // Returns the number of affectedRows

  UPDATE `pokemons` SET `name` = ?, `type` = ? WHERE id = ? LIMIT 1;
  
  -- params: [ 'Squirtle', 'water', 2 ]

where, params and limit are optionals

• Example

  await mysql.delete({
     table: 'pokemons',
     where: 'id = ?',
     params: [2],
     limit: 1,
  });
  
  // Returns the number of affectedRows

  DELETE FROM `pokemons` WHERE id = ? LIMIT 1;
  
  -- params: [ 2 ]

where, params and limit are optionals

await mysql.select({
   // ...
   mountOnly: true,
});

• Returns an object with the final query and params, without execute the query
• Works with select, insert and update ORM functions
• This is very useful for subqueries (WHERE, UNION, INTERSECT, etc.) 😉

• Getting the original mysql2 connection:

  const mysql2 = await mysql.getConnection();
  
  /**
   * mysql2.execute,
   * mysql2.beginTransaction,
   * mysql2.commit,
   * mysql2.rollback,
   * etc.
   *
   * See all options in: https://github.com/sidorares/node-mysql2
   */

• Mixing the Packages

  await mysql2.beginTransaction();
  
  try {
     const inserted = await mysql.insert({
        table: 'pokemons',
        values: [
           { name: 'Pichu', type: 'electric' },
           { name: 'Mewtwo', type: 'psychic' },
        ],
     });
  
     if (!inserted) throw new Error('Something is wrong, coming back');
  
     await mysql2.commit();
  } catch (error) {
     await mysql2.rollback();
     console.error(error.message);
  } finally {
     await mysql.end();
  }

import { setBacktick } from 'mysql2-orm';

setBacktick('table');         // `table`
setBacktick('column');        // `column`
setBacktick('table.column');  // `table`.`column`

• See practical examples in samples

• Use verbose to see final queries in console

• • Features
    • SELECT
      • DISTINCT
      • JOIN
      • WHERE
      • GROUP BY
      • ORDER BY
      • LIMIT
      • OFFSET
    • UPDATE
      • WHERE
      • LIMIT
    • DELETE
      • WHERE
      • LIMIT
    • INSERT
    • TRANSACTION
    • SUBQUERIES

Project	Author
mysql2
mysql2-orm

• Check the original mysql2 project here.