bs-pimp-my-sql

ReasonML implementation of the pimp-my-sql wrapper.

Why?

This is a SQL wrapper that provides many convenience features and a "light" ORM interface that uses plain SQL as the underlying language. Currently it is only compatible with the [bs-mysql2] client.

How do I install it?

Inside of a BuckleScript project:

yarn add @glennsl/bs-json bs-mysql2 bs-pimp-my-sql bs-result bs-sql-common bs-sql-composer

Then add @glennsl/bs-json, bs-mysql2, bs-pimp-my-sql, bs-result, bs-sql-common, and bs-sql-composer to your bs-dependencies in bsconfig.json:

{
  "bs-dependencies": [
    "@glennsl/bs-json",
    "bs-mysql2",
    "bs-pimp-my-sql",
    "bs-result",
    "bs-sql-common",
    "bs-sql-composer"
  ]
}

How do I use it?

General Usage

The way you should access modules in PimpMySql is as follows:

PimpMySql.<Module>

Of course you can always directly call the internal files, namespaced with PimpMySql_, but that is not recommended since these files are implementation details.

Using the Factory Model.

Below are the requirements necessary to use the FactoryModel. Each requirement is documented with examples below. The requirements include: creating the connection, creating the config, and creating the model.

Creating the Connection

module Sql = SqlCommon.Make_sql(MySql2);

let conn = Sql.connect(~host="127.0.0.1", ~port=3306, ~user="root", ~database="example", ());

Creating the Config

Creating the Config is quite simple, below is a brief explanation for each field in the Config:

• t: the record type that will be mapped to
• table: the name of the database table
• decoder: a function to map the query response to t
• base: the base query for the model, try to keep this as thin as possible (i.e. minimal where clauses, etc.)

let table = "animal";

type animal = {
  id: int,
  type_: string,
  deleted: int,
};

module Config = {
  type t = animal;
  let table = table;
  let decoder = json =>
    Json.Decode.{
      id: field("id", int, json),
      type_: field("type_", string, json),
      deleted: field("deleted", int, json),
    };
  let base =
    SqlComposer.Select.(
      select
      |> field({j|$table.`id`|j})
      |> field({j|$table.`type_`|j})
      |> field({j|$table.`deleted`|j})
      |> order_by(`Desc({j|$table.`id`|j}))
    );
};

Creating the Model

Creating the model is quite simple once the Config is setup:

module Model = PimpMySql.FactoryModel.Generator(Config);

Usage Examples

Below are a few examples on how to use the model, refer to the documentation below for the full list of functions/methods:

Model.getOneById(1, conn)
|> Js.Promise.then_(res =>
     (
       switch (res) {
       | Some(x) => <handle case for successfully fetching a row>
       | None => <handle case for failing to fetch a row>
       }
     )
     |> Js.Promise.resolve
   );

Model.archiveOneById(1, conn)
|> Js.Promise.then_(res =>
     (
       switch (res) {
       | Result.Ok(Some(x)) => <handle case for successfully archiving a row and returning the result>
       | Result.Ok(None) => <handle case for successfully archiving a row and returning no result>
       | None => <handle case for failing to archive a row>
       }
     )
     |> Js.Promise.resolve
   );

Note: you will notice that some methods will return Result.Ok(None), this means that the row(s) were altered successfully but when an attempt to fetch the same row(s) was made the operation failed; this is because the Model's base query filters out the row(s) after update.

What's missing?

Everything not checked...

• [ ] Query Interface
  • [x] (raw) Raw SQL query
  • [ ] (rawInsert) Raw SQL insert
  • [ ] (rawUpdate) Raw SQL update
  • [x] INSERT
    • [x] (insertOne) basic wrapper
    • [x] (insertBatch) basic wrapper
  • [ ] UPDATE
    • [x] (updateOneById) Basic wrapper using the id column - must fit PrimaryId interface
    • [ ] (updateWhereParams) with the ObjectHash interface
    • [x] (incrementOneById) increment an integer column using the id column - must fit the Counter and PrimaryId interfaces
  • [x] DELETE
    • [x] (deleteBy) using a custom where clause
    • [x] (deleteOneById) - must fit the PrimaryId interface
  • [x] Archive
    • [x] (deactivateOneById) Deactivate a row using the id column - must fit the Activated and PrimaryId interfaces
    • [x] (archiveOneById) Soft DELETE a row using the id column - must fit the Archive interface
    • [x] (archiveCompoundBy) Soft Compound DELETE using a custom where clause - must fit the ArchiveCompound interface
    • [x] (archiveCompoundOneById) Soft Compound DELETE a row using the id column - must fit the ArchiveCompound and PrimaryId interfaces
  • [ ] SELECT
    • [ ] Transforms
      • [ ] JSON column parse
      • [ ] Nest dot notation transform
      • [ ] Null out nested objects
    • [x] (get) using the Compositional SQL DSL
    • [x] (getByIdList) using the id column - must fit PrimaryId interface
    • [x] (getOneBy) with custom where clause
    • [x] (getOneById) using the id column - must fit PrimaryId interface
    • [x] (getWhere) using a custom where clause
    • [ ] (getWhereParams) using the ObjectHash interface
• [ ] Model
  • [x] Compositional SQL DSL
  • [x] Model Creation DSL
  • [x] Query interface
    • [ ] INSERT
      • [x] (insertOne)
      • [x] (insertBatch)
      • [ ] Pre-Create intercept
      • [ ] Post-Create intercept
    • [ ] UPDATE
      • [x] (updateOneById) using the id column - must fit PrimaryId interface
      • [x] (incrementOneById) increment an integer column using the id column - must fit the Counter and PrimaryId interfaces
      • [ ] Pre-Update intercept
      • [ ] Post-Update intercept
    • [x] DELETE
      • [x] (deleteBy) using a custom where clause
      • [x] (deleteOneById) - must fit the PrimaryId interface
    • [x] Archive
      • [x] (deactivateOneById) Deactivate a row using the id column - must fit the Activated and PrimaryId interfaces
      • [x] (archiveOneById) Soft DELETE a row using the id column - must fit the Archive interface
      • [x] (archiveCompoundBy) Soft Compound DELETE using a custom where clause - must fit the ArchiveCompound interface
      • [x] (archiveCompoundOneById) Soft Compound DELETE a row using the id column - must fit the ArchiveCompound and PrimaryId interfaces
    • [ ] SELECT
      • [ ] Transforms - (Dependent upon Query Interface implementation)
      • [x] (get) using the Compositional SQL DSL
      • [x] (getByIdList) using the id column - must fit PrimaryId interface
      • [x] (getOneBy) with custom where clause
      • [x] (getOneById) using the id column - must fit PrimaryId interface
      • [x] (getWhere) using a custom where clause
      • [ ] (getWhereParams) using the ObjectHash interface
• [ ] Search - This needs some re-design to better fit ReasonML language semantics.
• [ ] Utilities
  • [ ] TIMESTAMP conversion
  • [ ] ObjectHash interface interpolation
  • [ ] Caching