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();
