@collectai/base-dao

Base DAO class for collectai applications

Usage no npm install needed!

<script type="module">
  import collectaiBaseDao from 'https://cdn.skypack.dev/@collectai/base-dao';
</script>

README

Table of contents

About

BaseDAO is a Light weight Data Access Object library use some of collectAI projects.

Dependencies:

installation

npm i -S @collectai/base-dao

Setup

To make use of this library of all it is necessary to create a new class extending the BaseDAO class:

// using ES5
const dao = require("@collectai/base-dao");

class BookStore extends dao.BaseDAO {}

// using ES6
import BaseDAO from "@collectai/base-dao";

class BookStore extends BaseDAO {}

If you are using typescript you must pass the type of your Entity

Table and Fields

import BaseDAO from "@collectai/base-dao";

type Book = {
  id: number;
  title: string;
  author: string;
  volume: number;
};

class BookStore extends BaseDAO<Book> {}

Once extended you must define your tableName and fields, there are two ways to do this:

// Javascript
class BookStore extends BaseDAO {}

BookStore.dbTable = "book";

BookStore.fields = {
  id: "id",
  author: "book_author",
  title: "book_title",
  volume: "book_volume",
};

// Typescript
class BookStore extends BaseDAO<Book> {
  static dbTable = "book";
  static fields = {
    id: "id",
    author: "book_author",
    title: "book_title",
    volume: "book_volume",
  };
}

Primary key

Additional to this there's the possibility to define the field that represents the table's primary key, if this is not set the baseDAO instance picks id as the primary key:

// Javascript
class BookStore extends BaseDAO {}

BookStore.dbTable = "book";
BookStore.primaryKey = "id";
BookStore.fields = {
  id: "id",
  author: "book_author",
  title: "book_title",
  volume: "book_volume",
};

// Typescript
class BookStore extends BaseDAO<Book> {
  static dbTable = "book";
  static primaryKey = "id";
  static fields = {
    id: "id",
    author: "book_author",
    title: "book_title",
    volume: "book_volume",
  };
}

The you are all setup you just need to create an instance and pass the Knex connection:

const connection = knex(/* your connection options*/);
const bookStore = new BookStore(connection);

Advanced fields options

It is possible to configure special data "casters" which change how the value of a field is return, for example:

-- given the SQL schema
TABLE book(
  id SERIAL PRIMARY KEY;
  book_title CHAR(150);
  book_author CHAR(100);
  book_volume CHAR(100);
);

class BookStore extends BaseDAO {
  static dbTable = "book";
  static fields = {
    id: "id",
    author: "book_author",
    title: "book_title",
    volume: {
      caster: "book_volume::INT AS volume",
    }
  };
}

const bookStore = new BookStore(connection);
const book  = await bookStore.findOne({ title }))

// Outputs
{ title: "The Lord of the Rings", author: "J. R. R. Tolkien", volume: 1 }

Custom error Handling

It's possible to add a custom error function in the constructor the BaseDAO instance:

function errorHandler(err) {
  /** do something with the error */
}

const bookStore = new BookStore(connection, errorHandler);

Search methods

Find

The find method searches for records in the table using the given query argument:

Where

Use the $where clause of the query can be used as follows:

const books = await bookStore.find({ $where: { author: "H.P. Lovecraft" } });

// Outputs
[{ id: 3, title: "The Call of Cthulhu", author: "H.P. Lovecraft", volume: 1 }];
In and not In

The $where clause supports the $in and $notIn operators:

const result = await bookStore.find({ $where: { id: { $in: [1, 3] } } });

// Outputs
[
  { id: 1, title: "The Analyst", author: "John Katzenbach", volume: 1 },
  { id: 3, title: "The Call of Cthulhu", author: "H.P. Lovecraft", volume: 1 },
];
Raw

The $where clause also supports to use a $raw operators:

const result = await bookStore.find({
  $where: {
    $raw: {
      query: "book_volume in (?,?)",
      values: [1, 2],
    },
  },
});

// Outputs
[
  { id: 1, title: "The Analyst", author: "John Katzenbach", volume: 1 },
  { id: 3, title: "The Call of Cthulhu", author: "H.P. Lovecraft", volume: 1 },
  { id: 5, title: "Dictionary", author: "John Doe", volume: 2 },
];

Fields

The $fields clause which gives the possibility to limit the fields that will be returned by the search:

const result = await bookStore.find({
  $where: { author: "H.P. Lovecraft" },
  $fields: ["id", "title"],
});

// Outputs
[{ id: 3, title: "The Call of Cthulhu" }];

Order by

The $orderBy clause allows to oder the result of the search:

const result = await bookStore.find({
  $orderBy: "author",
});

// Outputs
[
  { id: 3, title: "The Call of Cthulhu", author: "H.P. Lovecraft", volume: 1 },
  { id: 1, title: "The Analyst", author: "John Katzenbach", volume: 1 },
  { id: 2, title: "The Madman's Tale", author: "John Katzenbach", volume: 1 },
];

const result = await bookStore.find({
  $orderBy: ["author", "title"],
});

// Outputs
[
  { id: 3, title: "The Call of Cthulhu", author: "H.P. Lovecraft", volume: 1 },
  { id: 1, title: "The Analyst", author: "John Katzenbach", volume: 1 },
  { id: 2, title: "The Madman's Tale", author: "John Katzenbach", volume: 1 },
];

const result = await bookStore.find({
  $orderBy: {
    author: "asc",
    title: "desc",
  },
});

// Outputs
[
  { id: 3, title: "The Call of Cthulhu", author: "H.P. Lovecraft", volume: 1 },
  { id: 2, title: "The Madman's Tale", author: "John Katzenbach", volume: 1 },
  { id: 1, title: "The Analyst", author: "John Katzenbach", volume: 1 },
];

Limit

The $limit clause allows to return a specific number of records:

const result = await bookStore.find({ $limit: 1 });

// Outputs
[{ id: 1, title: "The Analyst", author: "John Katzenbach", volume: 1 }];

Offset

The $offset clause allows to give the search results an offset:

const result = await bookStore.find({ $offset: 1 });

// Outputs
[
  { id: 2, title: "The Madman's Tale", author: "John Katzenbach", volume: 1 },
  { id: 3, title: "The Call of Cthulhu", author: "H.P. Lovecraft", volume: 1 },
];

Find one

The findOne method searches for only one record that matches the given query:


const book = await bookStore.findOne({ $where: { author: "H.P. Lovecraft" } });
// Outputs
{ id: 3, title: "The Call of Cthulhu", author: "H.P. Lovecraft", volume: 1 }

Find by ID

The findId method searches for one record using the primary field key:

const book = await bookStore.findById(3);

// Outputs
{ id: 3, title: "The Call of Cthulhu", author: "H.P. Lovecraft", volume: 1 }

Modification methods

Create

It is possible to insert records by using the create method:

const book = await bookStore.create({ title: 'The Analyst', author: 'J.K.' });
// Outputs
{ id: 1, title: 'The Analyst', author: 'J.K.' }

const books = await bookStore.create([
  { title: "The Analyst", author: "J.K.", volume: 1 },
  { title: "The Madman's Tale", author: "J.K.", volume: 1 },
  { title: "The Call of Cthulhu", author: "H.P. Lovecraft", volume: 1 },
]);

// Outputs:
[
  { id: 1, title: "The Analyst", author: "J.K", volume: 1 },
  { id: 2, title: "The Madman's Tale", author: "J.K", volume: 1 },
  { id: 3, title: "The Call of Cthulhu", author: "H.P. Lovecraft", volume: 1 },
];

Update

It is possible to update records using the update method:

const book = await bookStore.update({
  $where: { id: 1 },
  $data: { author: "John Katzenbach" },
});

// Outputs
{ id: 2, title: 'The Madman\'s Tale', author: 'John Katzenbach', volume: 1 }

const books =  await bookStore.update(
  $where: { title: "The Madman's Tale" },
  $data: { author: "John Katzenbach" },
);

// Outputs:
[
 { id: 1, title: 'The Analyst', author: 'John Katzenbach' },
 { id: 2, title: 'The Madman\'s Tale', author: 'John Katzenbach' },
]

The $where clause of the update argument has the same conditions as the $where section in the find method

Update by ID

Using the updateBy method allows to delete a record using it's primary field key:

const book = await bookStore.updateById(2, {
  title: "The Madman's Tale",
  author: "John Katzenbach",
});

// Outputs:
{ id: 2, title: 'The Madman\'s Tale', author: 'John Katzenbach', volume: 1 }

Delete

It is possible to delete records using the del method:

await bookStore.del({ title: "The Madman's Tale" });

The only argument of this functions is a $where clause and the same conditions as the $where section in the find method

Delete by ID

Using the deleteById method allows to delete a record using it's primary field key:

await bookStore.deleteById(2);

Delete All

Using the deleteAll allows to delete all records of your table key:

await bookStore.deleteAll();