jakecyr/slim-node-mysql

MySQL database class to abstract pooling and prepared statements

Slim Node MySQL

MySQL database class to abstract pooling and prepared statements.

Table of Contents

• Installation
• Download
• Example Setup
• Usage
• Methods
  • query
  • execute
  • getOne
  • getValue
  • execute
  • exists
• Testing
  • Unit Tests
  • Functional Tests

Installation

To install the package from npm run:

npm install slim-node-mysql

Download

Download the latest Slim Node MySQL package.

Example Setup

It is recommended that a single SlimNodeMySQL instance is used throughout your app so it can effectively manage pooling.

const { SlimNodeMySQL } = require('slim-node-mysql');

// create new database instance with the MySQL connection string
const database = new SlimNodeMySQL(env.database);

Usage

If non-SELECT queries are executed, the resulting value will be of the type ExecuteResult containing the following properties:

interface ExecuteResult {
  fieldCount: number;
  affectedRows: number;
  changedRows: number;
  insertId: number;
  serverStatus: number;
  warningCount: number;
  message: string;
  procotol41: boolean;
}

Methods

query

// returns an array of rows found or an empty array if nothing is found
const data: User = await database.query<User>(
  `
  SELECT
      *
  FROM
      User
  WHERE
      id = @id
  `,
  {
    id: 1,
  }
);

execute

Used to run all non-SELECT queries.

Example update:

const result: ExecuteResult = await database.execute(
  `
    UPDATE User
    SET username = @username
    WHERE id = @id
  `,
  {
    id: 1,
    username: 'newUsername',
  }
);

console.log(result.affectedRows); // 1
console.log(result.changedRows); // 1

Example insert:

const result: ExecuteResult = await database.execute(
  `
    INSERT INTO User (id, username)
    VALUES (@id, @username)
  `,
  {
    id: 3
    username: 'newUsername',
  }
);

console.log(result.affectedRows); // 1
console.log(result.insertId); // 3
console.log(result.changedRows); // 0

getOne

// returns an object with data from the matched row or null if no match was found
const data: User = await database.getOne<User>(
  `
    SELECT
        *
    FROM
        User
    WHERE
        id = @id
    LIMIT 1
`,
  {
    id: 1,
  }
);

getValue

// returns value from column specified (generics are optional)
const username: string = database.getValue<User, 'username'>(
  'username',
  `
    SELECT
        *
    FROM
        User
    WHERE
        id = @id
    LIMIT 1
`,
  {
    id: 1,
  }
);

exists

// returns a boolean value depending on if any rows are returned or not
const exists: boolean = await database.exists<User>(
  `
    SELECT *
    FROM User
    WHERE id = @id
    LIMIT 1
`,
  {
    id: 1,
  }
);

console.log(userExists); // true

Testing

Unit Tests

npm run test

Functional Tests

CONNECTION_STRING=<TEST DATABASE CONNECTION STRING> ./node_modules/.bin/jest functional-tests/**/*.test.ts --runInBand