Skip to main content

Database package

leemons-database is a package for standardizing database-related stuff.

It manages all connections, queries, models and connects with each connector.

Connectors​

Connectors are those packages that transform general information into ORM-specific instructions.

NAME CONVENTION

Every connector must be named as follows: leemons-connector-CONNECTOR_NAME

No connector comes installed by default, so you need to install your favorite one:

yarn add leemons-connector-CONNECTOR_NAME

Database Manager​

The Database Manager is the entry point for this package. It contains a Connector Registry which initializes every connection.

The Database Manager is also responsible for building queries and manage the different connectors.

tip

You can access the Database Manager through leemons.db

Connector Registry​

The connector registry is a function helper for managing all the connectors. It provides the following methods:

load

load

Registers the different connectors that database connections uses in connectorRegistry.connectors.

The registry imports each connector for using them when required.

connectorRegistry.connectors.load();
REMEMBER

You should have installed the connectors

init

init

The registry calls every connector's initialization method and saves the resulting models in databaseManager.models.

You can access these models through leemons.db.models.

connectorRegistry.connectors.init();
getAll

getAll

Gets all the connectors objects (without the names)

connectorRegistry.connectors.getAll();
get

get

Gets the specified connector object

connectorRegistry.connectors.get('connector name');
getFromConnection

getFromConnection

Gets the connector used in the given connection

connectorRegistry.connectors.getFromConnection('connection name');
default

default

Gets the connector used in the default connection

connectorRegistry.connectors.default;
set

set

Sets the given value on databaseManager.connector[provided key]

connectorRegistry.connectors.set('connector name', newValue);

Query Builder​

When you try to query something from the database, it is sometimes difficult to find the desired model. With the query builder is very easy; you only need to remember the model name.

INFO

The builder search for the models in databaseManager.models.

When you call the query builder, it gets your model and connector for generating an object with the queries. Once it's generated, it caches it for the following calls.

Queries​

The queries are the primary way of interacting with the database. These are standardized instructions that are interpreted by the connectors. If you need to run a custom query, you need to check how it is done with your selected connector.

The provided queries may change depending on the connector, see your connector's docs, but should be as follows:

*Many queries

The *many queries run in transactions, so if an entry fails, the whole query rolls back (returns to the previous state)

create

create

If you want to create any new entry, call this query with an object describing the new values.

Once the user is created, the resulting object is resolved. If an error occurred, the promise rejects the database error.

leemons.query('users').create({
  name: 'Jane',
  email: 'JaneDoe@leemons.io',
});
createMany

createMany

You can also create many entries in one call. The queries run inside a transaction.

If all the creations are done correctly, an array with all the users is resolved; if not, the promise rejects the database's accumulated errors.

leemons.query('users').createMany([
  { name: 'Jane', email: 'JaneDoe@leemons.io' },
  { name: 'John', email: 'JohnDoe@leemons.io' },
]);
update

update

For updating an item, you need to provide a filter for matching the entry, then describe the new item values.

When the entry is updated, an object with the new values is resolved. If no entry matches the query or an error occurred, the promise rejects the database error.

leemons.query('users').update({ id: 1 }, { name: 'Janie' });
updateMany

updateMany

For updating multiple items, you need to provide an array with each update information:

  • filters so we can update the matching entry.
  • The new item values.

The update runs inside a transaction. When all the entries are updated, an array with the new values is resolved. If an error occurs, the promise rejects the database error.

leemons.query('users').updateMany([
  {
    query: { id: 1 },
    item: { name: 'Jane' },
  },
  {
    query: { id: 2 },
    item: { name: 'John' },
  },
]);
set

set

The set query lets you update the value of an entry if it exists; if not, it creates it.

// If a user with the email 'JaneDoe@leemons.io' exists, it sets its name to 'Jane Doe'.
// If the user does not exist, it creates a new one with the given email and name.
leemons.query('users').set(
  { email: 'JaneDoe@leemons.io' },
  { name: 'Jane Doe' }
);

</details>
<details>
<summary>delete</summary>

  `delete`

  Deleting an entry is as easy as providing the necessary [filters](#filters) for matching an entry.

  Once the entry is deleted, an empty object resolves. If an error occurs, the promise rejects the database error.

```js
leemons.query('users').delete({ id: 1 });
deleteMany

deleteMany

You can also delete many items matching some filters. This query is separated from delete to avoid unwanted data loss.

The query runs inside a transaction. When all the items are deleted, an array with empty objects is resolved. If any error occurs, the promise rejects the database error.

leemons.query('users').deleteMany({ name: 'Jane' });
find

find

When you need to get data from the database, you only have to run a find query; all we need is the filters you want.

When some results are found, an array with them is resolved. If there are no matching results, an empty array is returned.

If an error occurs, the promise rejects the error.

leemons.query('users').find({ name: 'Jane' });
findOne

findOne

The findOne query is the same as find, but the $limit filter is added to the query.

leemons.query('users').findOne({ name: 'Jane' });

Filters​

The filters are the fundamental way of selecting entries from the database. For supporting different database languages, we have abstracted some filters.

The filters are divided into three groups: visualization, logic, comparison.

Visualization​

With the visualization filters, you can modify how the data is represented.

sort

sort

You can sort the outputted data in ascendant or descendant order based on different fields.

The sorting is done in the database engine; this means that the resulting order may depend on the used connector or database engine.

The $sort filter is a comma-separated string with different fields and order directions.

leemons.query('users').find({ $sort: 'name:ASC, email:DESC' });
offset

offset

Sometimes you need to skip some results from the database; this is done with the offset.

The $offset filter is a numeric value, and it can also be called $start.

leemons.query('users').find({ $offset: 10 });
limit

limit

The limit is an instruction for getting a fixed number of results.

The $limit filter is a numeric value.

leemons.query('users').find({ $limit: 42 });

Logic​

You can use the logic filters to apply logic operators to your queries.

where

where

You can use the $where filter to apply the comparison filters; this is useful for grouping the filters easily.

leemons.query('users').find({
  $where: { id: 1 },
});

You can also use the $where filter to apply AND logic.

// Selects all the users in second grade who are delegates
leemons.query('users').find({
  $where: [{ course: '2' }, { role: 'delegate' }],
});
or

or

You can use the $or filter for matching entries with different values.

// Selects all the users who are delegates in second grade or teachers
leemons.query('users').find({
  $or: [{ course: '2', role: 'delegate' }, { role: 'teacher' }],
});

Comparison​

equals

equals

Search for entries in the database whose field equals the desired value.

Only specify the field and the value (e.g. { id: 1 })

leemons.query('users').find({ id: 1, name: 'Jane' });
not equals

not equals

You can also search for entries whose fields are different from the desired value.

Specify the column name ending with the suffix _$ne.

leemons.query('users').find({ name_$ne: 'Jane' });
in

in

Instead of using $or, you can use _$in to specify which values you are looking for.

Specify the column name ending with the suffix _$in.

leemons.query('users').find({ name_$in: ['Jane', 'John'] });
not in

nin

You can also find those entries without the values you are not looking for.

Specify the column name ending with the suffix _$nin.

leemons.query('users').find({ name_$nin: ['Jane', 'John'] });
contains

contains

Search for entries containing a text without case sensitivity.

Specify the column name ending with the suffix _$contains.

leemons.query('users').find({ name_$contains: 'a' });
not contains

ncontains

You can also search for those entries not containing the case insensitive text.

Specify the column name ending with the suffix _$ncontains.

leemons.query('users').find({ name_$ncontains: 'a' });
strict contains

containss

Search entries containing the case sensitive text.

Specify the column name ending with the suffix _$containss.

leemons.query('users').find({ name_$containss: 'S' });
strict not contains

ncontainss

You can also search for those entries not containing the exact text.

Specify the column name ending with the suffix _$ncontains.

leemons.query('users').find({ name_$ncontainss: 'S' });
lower than

lower than

Search for entries whose values are lower than the desired value.

Specify the column name ending with the suffix _$lt.

leemons.query('users_grades').find({ grade_$lt: 5 });
lower than or equal

lower than or equal

Search for entries whose values are lower or equal to the desired value.

Specify the column name ending with the suffix _$lte.

leemons.query('users_grades').find({ grade_$lte: 4 });
greater than

greater than

Search for entries whose values are greater than the desired value.

Specify the column name ending with the suffix _$gt.

leemons.query('users_grades').find({ grade_$gt: 4 });
greater than or equal

Search for entries whose values are greater or equal to the desired value.

Specify the column name ending with the suffix _$gte.

leemons.query('users_grades').find({ grade_$lte: 5 });
is NULL

Search for entries whose values are null (true) or not (false).

Specify the column name ending with the suffix _$null.

leemons.query('users').find({ email_$null: true, phone_$null: false });