ReptileJS

Supports both MySQL 5.7 and 8
A straightforward ORM for MySQL and Nodejs using sidorares/node-mysql2 and js-sql-syntax.

Contents

• Installation
• Usage
• Security
• Getting started
• Database
• Model
  • Declaration
  • Validation
  • Interacting with the database
  • Transactions
• Why reptile
• License

Installation

npm install reptilejs

Usage

const { Database, Model } = require('reptilejs')

// Connect to the database
const myDatabase = new Database({
    host: '127.0.0.1',
    user: 'myUser',
    password: 'myPass',
    database: 'myDatabase'
})

// Declare a model
class MyModel extends Model {
    static get table () {
        return 'myTable'
    }

    static get fields () {
        return {
            'id': { type: 'string', max: 5, min: 5 },
            'myNum': { type: 'number', max: 11 },
            'myDate': { type: Date }
        }
    }
}

// Register the model
myDatabase.register('myModel', MyModel)

// Create an item
const myItem = new MyModel('abcde', {
  myNum: 1,
  myDate: new Date()
})

myItem.save()
  .then(res => {
    // res is mysql response
  })

// Retrieve an item
MyModel.findOne({ id: 'abcde' })
  .then(res => {
    // res is null or instance of MyModel
  })

Security

Security is vital when dealing with databases and data in general, and it's important to understand what third party code do and does not do in your project. This module simply provides model management and is using the js-sql-syntax project for generating queries. As a rule of thumb however, whenever you create queries values are safely passed to prepared statements but keys are not escaped. For further information, please see the js-sql-syntax repository.

Getting started

The framework consists of two classes, Database and Model.
Both are explained below.

Database

The class Database is used to create a connection pool to a database.

new Database(opts)

Connect to a database

opts Object

An object containing the connection details for the connection pool.
As Database uses mysqljs/mysql internally available options are anything outlined at https://github.com/mysqljs/mysql#pooling-connections

database.register(name, model)

Register a model to be used by other models registered to a database

name String

The name to register the model as, only one model can use a specific name

model Model

The model to register

Model

A model is used to act like an interface between the database and the business logic by allowing for easy manipulating of data. The class Model needs to be inherited from in order to work.

Declaring a model

const sql = require('js-sql-syntax')
const { Model, relations } = require('reptilejs')

// Declare a model by inheriting from the Model-class
class User extends Model {

  /*
    REQUIRED
    Every model has to specify the table to which it maps
    by returning the name of the table as a string when
    requesting the static parameter `table`
  */
  static get table () {
    return 'users'
  }

  /*
    REQUIRED
    Every model has to specify the fields it should map
    to the table.

    Fields are described as an object where the column name is the key
    and the validation details is the value such as the following:
  */
  static get fields () {
    return {
      'id': { type: 'number' },
      'given_name': { type: 'string', max: 255 },
      'family_name': { type: 'string', max: 255 }
    }
  }

  /*
    OPTIONAL
    The static field `primary` declares the primary fields
    of the model as an array of strings.

    DEFAULT
    [ 'id' ]
  */
  static get primary () {
    return 'id'
  }

  /*
    OPTIONAL
    A model can have direct relations to any other model
    registered to the same database, this is done by
    returning an object from the static field `relations`.

    The key is the property to mount the related row/rows to
    and the value is a set of options for the relation.

    Note that relations will not be populated unless specified
    as an option in Model.findOne or Model.findMany.

    DEFAULT
    {}
  */
  static get relations () {
    return {
      'address': { // REQUIRED user.address will house the relation
        'model': 'Address', // REQUIRED The registered name of the related model from database.register(name, model)
        'type': relations.type.HAS_MANY, // REQUIRED The type of the relation, HAS_MANY or HAS_ONE
        'local': 'id', // REQUIRED The local column to match on,
        'foreign': 'user_id', // REQUIRED The foreign column to match on, must exist in the related table
        'cols': [ 'street', 'city' ], // OPTIONAL An array of which columns to include from the related table, defaults to all columns
        'transform': rows => { ... } // OPTIONAL A function to apply to the row(s) before mounting, parameter is a single object or a row depending on the relation's type
      }
    }
  }

  /*
    OPTIONAL

    A function that will modify the data returned from the
    database before an instance of the model is created
  */
  static transform (data) {
    // Modify data
    return data
  }

  // MARK: Custom methods

  /*
    OPTIONAL
    A model can implement or override any method to set or retrieve
    data by simply using the `this.constructor.execute` -function
  */
  findOneByGivenName (name, opts) {

    // Using the 'js-sql-syntax' framework
    const query = sql().select().from(this.constructor.table).where({ 'given_name': name }).limit(1)
    return this.constructor.execute(query.getQuery(), query.getValues())

    // Using strings
    const query = `SELECT * FROM ${this.constructor.table} WHERE given_name=? LIMIT 1`
    const values = [ name ]
    return this.constructor.execute(query, values)
  }
}

Validation

When defining fields for a model you also define the type, length and any other parameters that the value should conform to. Validation of this is done automatically when creating an instance of a model.

type

Type String, Type

The type property specifies the type of the value, either as a string to match the result of a typical typeof check or the name of a type to match an instanceof check.

Valid values: 'string', 'number', 'boolean', Date, String, Number, Boolean

max and min

Type Number

The max and min properties behave differenly depending on the type of the field. If the type is a number these properties will define the maximum and minimum values, while if the type is a string these will define the length of the string.

required

Type Boolean

Declares if the contents of the field can be null or not.

enum

Type Array

Define an array of allowed values for the field.

custom

Type Function

Use a custom validation method for the field. The function should take the value of the field as its only parameter and return a boolean of whether the value passed or failed the validation. It can also throw a string explaining a reason why the validation failed which will be wrapped in a FailedCustomValidationError.

API

new Model(primary, data [, opts])

In order to use a model you need to instantiate it. One instance of a model translates to one row in the database.

primary Object, String, Number, Date or null

The parameter primary is most often used when updating a partial instance. It's type should be an object where the keys are the names of the primary columns and the values are its values, or null if no primary values can be specified at that point in time.

For convenience however, if only one primary column is specified the value of this parameter can be the direct primary value.

Example 1. Object

class MyModel extends Model {}
const myInstance = new MyModel({ id: 7 }, {})

Example 2. Direct value

class MyModel extends Model {}
const myInstance = new MyModel(7, {})

data Object

The parameter data specifies the data with which the model should be instantiated. This resembles the values for each column of the row (instance).

opts Object

Options for the instance

{
  validate: null | Array<String>, // Declares the fields that should be validated, defaults to `null` (all fields)
  removeExcessProperties: Boolean // Whether or not to remove all properties from the `data` parameter that are not declared as fields in the model. Useful if, for example, data is raw user input. Defaults to `true`.
}

Interacting with the database

Options

Almost all default functions interacting with the database allows for options as the last parameter, except for a few specific ones (which you can read about under each function) they can all handle the following:

{
  connection: Connection, // OPTIONAL A database connection to use instead of the pool provided from the `Database` class
}

Model.findMany(query [, opts])

Find many rows based on a query and return them as an array of instances.

query Object

An object specifying a query with keys mapping to columns and values to values. Values are escaped, keys are not.

opts Object

An optional object specifying options. These can be everything under the Options-heading and any of the following:

{
  limit: Number,
  offset: Number,
  populate: Boolean | Array // Specifies which relations to populate as an array of the names of the relations or a boolean meaning none `false` or all `true`. Defaults to `false`.
}

Model.findOne(query [, opts])

A convenience function around Model.findMany but limiting the response to 1 and returning the instance directly instead of in an array.

Model.findOneById(id [, opts])

A convenience function around Model.findOne but creating the query automatically for the field id.

id String, Number, Date

Model.findOneByPartial(partial [, opts])

A convenience function around Model.findOne but creating the query automatically from an already existing instance.

partial Model

Model.delete(query [, opts])

Delete a row in the database.

query Object

The query needs to specify at least one field: value pair or the function will throw.

Model.insert(data [, opts])

Insert data into the table specified for the model without validation.

data Object

Data to insert, values are escaped, keys are not.

Model.update(query, values [, opts])

Update some data where the query match.

query Object

Values are escaped, keys are not.

values Object

Values to set. Values are escaped, keys are not.

Model.execute(query, values [, opts])

Execute an SQL statement on the table specified for the model.

query String

An SQL-query.

values Array

Values to insert at placeholders in the query marked with ?.

Transactions

Model.transaction(fns)

Create database transactions.

fns Array<Function>

An array of functions, each taking a connection as its only parameter. Every function should return a promise. If a promise fails the transaction will abort and any changes will be reverted.

Example

class MyModel extends Model {}
class MyOtherModel extends Model {}

const myInstance = new MyModel(null, {})

MyModel.transaction([
  conn => {
    return myInstance.save({ connection: conn })
  },
  conn => {
    const myOtherInstance = new MyOtherModel(null, { 'other_id': myInstance.id })
    return myOtherInstance.save({ connection: conn })
  }
])

model.save([opts])

Save the instance to the database by either inserting or updating the row. This function uses ON DUPLICATE KEY UPDATE internally.

opts Object

All default options and:

{
  insertOnly: Boolean // Indicates if only insertion is permitted (no update). Defaults to false.
}

model.update([opts])

Update the database with the current values of the instance. The instance's primary values will be used to create a query.

Why reptile

The term "orm" translates to snake in Swedish.

License

MIT