Link Search Menu Expand Document

Overview

Initialize

import Easybase from "easybasejs";
import ebconfig from "./ebconfig.js";

const table = Easybase.EasybaseProvider({ ebconfig }).db("MYTABLE");
const { e } = table; // Optional query expressions

Example Table

For the following queries, consider the data table below:

[
  { "title": "Avatar", "rating": 83 },
  { "title": "Titanic", "rating": 75 },
  { "title": "The Lion King", "rating": 55 },
  { "title": "Jurassic World", "rating": 59 }
]

Select

.return builds queries to select from. After building a query , use .all or .one to execute it. This applies to all types of queries.

await table.return().all();

[
  { "title": "Avatar", "rating": 83 },
  { "title": "Titanic", "rating": 75 },
  { "title": "The Lion King", "rating": 55 },
  { "title": "Jurassic World", "rating": 59 },
]

.one will only return the first match.

await table.return().one();

{ "title": "Avatar", "rating": 83 }

Pass arguments into .return to select only specific columns.

await table.return("title").one();

{ "title": "Avatar" }

Note that each record will also have a persistent, unique identifier called _key. This is useful for performing operations on specific records.

await table.return().one();

{ "title": "Avatar", "rating": 83, "_key": "507f191e810d19729de860ea" },

Where

.where builds where clauses. Expressions, e, are are used to create comparisons and boolean logic. Learn more about expressions and operations.

const { e } = table;
await table.return().where(e.eq("title", "The Lion King")).one() // Equals

{ "title": "The Lion King", "rating": 55 }

Conditions can be objects in the form { field: value }.

Each property generates a field = value clause.

await table.return().where({ title: "The Lion King", rating: 55 }).all()

[
  { "title": "The Lion King", "rating": 55 }
]

Use Operations to build complex conditions with e.and, e.or and e.not.

await table.return().where(
  e.or(
    e.eq("title", "The Lion King"), // Equals
    e.gt("rating", 80) // Greater than
  )
).all()

[
  { "title": "Avatar", "rating": 83 },
  { "title": "The Lion King", "rating": 55 }
]

array arguments generate a field in values expression.

await table.return().where({ rating: [55, 56, 57, 58, 59] }).all()

[
  { "title": "The Lion King", "rating": 55 },
  { "title": "Jurassic World", "rating": 59 }
]

Order By

Specify row ordering with .orderBy. This function accepts objects.

The property by is used for ordering. Set property sort to either 'asc' or 'desc'.

await table().return().orderBy({ by: "rating", sort: "asc" }).all()

[
  { "title": "The Lion King", "rating": 55 },
  { "title": "Jurassic World", "rating": 59 },
  { "title": "Titanic", "rating": 75 },
  { "title": "Avatar", "rating": 83 }
]

.orderBy accepts multiple sort objects, which will be evaluated in order.

await table().return().orderBy({ by: "rating", sort: "asc" }, { by: "title", sort: "desc" }).all()

[
  { "title": "The Lion King", "rating": 55 },
  { "title": "Jurassic World", "rating": 59 },
  { "title": "Titanic", "rating": 75 },
  { "title": "Avatar", "rating": 83 }
]

Limit

Pass .limit the maximum number of rows to fetch.

await table.return().limit(2).all()

[
  { "title": "Avatar", "rating": 83 },
  { "title": "Titanic", "rating": 75 }
]

Offset

Pass .offset the number of rows to skip before returning rows.

await table.return().offset(1).all()

[
  { "title": "Titanic", "rating": 75 },
  { "title": "The Lion King", "rating": 55 },
  { "title": "Jurassic World", "rating": 59 }
]

.offset and .limit can be used in conjugation for pagination of your data table.

let page = 0;

await table.return().limit(2).offset(page * 2).all();

[
  { "title": "Avatar", "rating": 83 },
  { "title": "Titanic", "rating": 75 }
]

page++;
await table.return().limit(2).offset(page * 2).all();

[
  { "title": "The Lion King", "rating": 55 },
  { "title": "Jurassic World", "rating": 59 }
]

Aggregate

The following aggregators can be used in .return from the expression object: .min, .max, .sum, .avg, and .count.

await table.return(e.avg('rating')).all()

[ { avg_rating: 68 } ]

Aggregators are powerful when combined be used with a .where clause.

await table.return(e.count('rating')).where(e.gt('rating', 70)).all()

{ count: 2 }

Can be called with either .one or .all. There is no difference between the two.

await table.return(e.sum('rating')).one()

[ { sum_rating: 272 } ]

Group By

.groupBy accepts a column name and builds group by clauses. You must also provide some aggregator function in return. This often has no effect on the resulting aggregation.

await table.return(e.avg('rating')).groupBy('rating').all()

[ { avg_rating: 68 } ]

Copyright © 2018-2021 Easybase