Skip to main content

Select

Return

The quickest way to get your data is to use db().return(). This is how you build select queries.

After building a query, use .all or .one to execute it.

Consider the following table called MOVIES:

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

Database return 1

.all returns all matches.

const { db } = useEasybase();

await db('MOVIES').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 db('MOVIES').return().one();

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

Pass arguments into .return to select only specific columns.

await db('MOVIES').return("title").one();

{ "title": "Avatar" }
tip

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

await db('MOVIES').return().one();

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

Example

Being that a lot of Easybase use-cases involve displaying remote data, here are two examples using the MOVIES table.

import { useEffect, useState } from 'react';
import { useEasybase } from 'easybase-react';

function MyComponent() {
const [dataArr, setDataArr] = useState([]);
const { db } = useEasybase();

async function mounted() {
const ebData = await db('MOVIES').return().all();
setDataArr(ebData);
}

useEffect(() => {
mounted();
}, [])

return (
{dataArr.map(ele => JSON.stringify(ele))}
);
}

Example using the useReturn hook:

import { useEasybase } from 'easybase-react';

function MyComponent() {
const { db, useReturn } = useEasybase();
const { frame } = useReturn(() => db('MOVIES').return()), []);

return (
{frame.map(ele => JSON.stringify(ele))}
);
}
note

Both of these examples result in the same logic.

If you plan on using stateful variables, wrap your db instance in the useReturn hook. In this case, your data will automatically be re-fetched with new parameters if your state changes, similar to React's native useEffect and useCallback hooks. Learn more here

Where

.where builds where clauses. Expressions, e, are used to create comparisons and boolean logic. Learn more about expressions and operations. This is also gathered from the useEasybase hook. So perhaps our initial example looks like the following:

const { db, e } = useEasybase();

await db('MOVIES').return().where(e.eq("title", "The Lion King")).one() // Equals

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

In this case, the expression e.eq means equals, such it will return all records where the Title column equals "The Lion King".

All expressions are available here.

From there, use Operations to build complex conditions with e.and, e.or and e.not.

await db('MOVIES').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 db('MOVIES').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 db('MOVIES').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 db('MOVIES').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

Use .limit to set a maximum number of rows to fetch.

await db('MOVIES').return().limit(2).all()

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

Offset

User .offset to set the number of rows to skip.

await db('MOVIES').return().offset(1).all()

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

Pagination

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

let page = 0;

await db('MOVIES').return().limit(2).offset(page * 2).all();

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

page++;
await db('MOVIES').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: .min, .max, .sum, .avg, and .count.

await db('MOVIES').return(e.avg('rating')).all()

[ { avg_rating: 68 } ]

Aggregators can be extremely powerful when used in combination with .where.

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

{ count: 2 }

Call aggregations with either .one or .all. There is no difference.

await db('MOVIES').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 does not affect the resulting aggregation.

await db('MOVIES').return(e.avg('rating')).groupBy('rating').all()

[ { avg_rating: 68 } ]