BlinkDB has a powerful query system and can filter, sort & limit your data.
const userTable = createTable<User>(db, 'users')();
const users = await many(userTable, {
where: {
age: { gt: 16 },
name: { in: ['Alice', 'Bob'] },
todoCount: { between: [0, 10] }
},
sort: {
key: 'age',
order: 'asc'
}
});
When you call many(), one(),
first(), or watch(), you can supply
a query in order to tell BlinkDB what items you want to receive & how you want
to receive them.
The properties you can supply in the query are always the same:
export type Filter<T, P extends keyof T> = {
where?: Where<T> | Or<T> | And<T>;
sort?: Sort<T>;
limit?: Limit<T, P>;
};
With where, you specify what items to receive, sort specifies how
items are to be sorted, and limit tells BlinkDB how many items
you want.
With where, you can specify if you want to load an entity from the
database based on their properties. The simplest case is equality
checking - with the following query, you can load all users from
the database who are 16 years old and called Alice:
// Retrieves all users called Alice who are 16 years old
const users = await many(userTable, {
where: {
age: 16,
name: 'Alice'
}
});If you’re matching based on equality, you can also use a so-called “matcher”:
// Retrieves all users called Alice who are 16 years old
const users = await many(userTable, {
where: {
age: { eq: 16 },
name: { eq: 'Alice' }
}
});
That might look unnecessary, but eq is not the only matcher available -
There are many more below.
in
in matches all entities where the set property is one of the
supplied values.
// Match all users called Alice, Bob, or Vance
const users = await many(userTable, {
where: {
name: { in: ['Alice', 'Bob', 'Vance'] }
}
});gt
gt matches all entities where the set property is greater than
the supplied value (either a string, number, or Date).
// Match all users older than 43yrs
const users = await many(userTable, {
where: {
age: { gt: 43 }
}
});gte
gte matches all entities where the set property is equal to
or greater than the supplied value (either a string, number, or Date).
// Match all users who are 87yrs old or older
const users = await many(userTable, {
where: {
age: { gte: 87 }
}
});lt
lt matches all entities where the set property is less than
the supplied value (either a string, number, or Date).
// Match all users younger than 20yrs
const users = await many(userTable, {
where: {
age: { lt: 20 }
}
});lte
lte matches all entities where the set property is equal to
or less than the supplied value (either a string, number, or Date).
// Match all users who are 31yrs old or younger
const users = await many(userTable, {
where: {
age: { lte: 31 }
}
});between
between matches all entities where the set property is between
the first supplied value (inclusive) and the second supplied value (inclusive).
The property to check against can be of type string, number, or Date.
// Match all users who are between 40yrs and 50yrs old
const users = await many(userTable, {
where: {
age: { between: [40, 50] }
}
});contains
contains matches all entities where the set array property
includes the given value.
interface Users {
nicknames: string[];
}
// Match all users who have 'Big Nick' as a nickname
const users = await many(userTable, {
where: {
nicknames: { contains: 'Big Nick' }
}
});AND
AND matches all entities which match all
of the supplied matchers.
// Match all users called Alice who are 26yrs old
const users = await many(userTable, {
where: {
AND: [
{
age: 26
},
{
name: 'Alice'
}
]
}
});
The above query can also be written without and(...), like this:
// Match all users called Alice who are 26yrs old
const users = await many(userTable, {
where: {
age: 26,
name: 'Alice'
}
});OR
OR matches all entities which match at least one
of the supplied matchers.
// Match all users who are either younger than 40yrs
// or older than 50yrs
const users = await many(userTable, {
where: {
OR: [
{
age: { lt: 40 }
},
{
age: { gt: 50 }
}
]
}
});
BlinkDB can sort your items for you, which can be much faster
than you doing it yourself (depending on the query). To sort your received items, specify the key (which property to sort on),
and an order, either asc (ascending) or desc (descending).
The query below will give you all users sorted by age:
// Retrieve all users sorted descending by age
// e.g. the oldest user first, the youngest user last
const users = await many(userTable, {
sort: {
key: 'age',
order: 'desc'
}
});Retrieving all items from a table can take a long time, especially if you have a lot of entities. Most frontends have either pagination or infinite scrolling in order to prevent loading all items at once. In the backend, this is most often solved by offset-based pagination or cursor-based pagination - both of which BlinkDB supports.
For offset-based pagination, you must specify:
skip, or how many items to skip before returning them
take, or how many items to return
If you would like to display 10 users per page, to retrieve the contents of the first page, you would use:
// Retrieve all users on the first page
const users = await many(userTable, {
limit: {
skip: 0 * 10,
take: 10
}
});Consequently, for the second page you would use:
// Retrieve all users on the second page
const users = await many(userTable, {
limit: {
skip: 1 * 10,
take: 10
}
});For cursor-based pagination, you must specify:
from, the primary id of the first entity to return
take, or how many items to return
skip: 1, if you don’t want to return the entity with the primary key of from)
If you would like to display 10 users per page, to retrieve the contents of the first page, you would use:
// Retrieve all users on the first page
const users = await many(userTable, {
limit: {
take: 10
}
});
// Get the user id of the last user
const lastUserId = users[users.length - 1].id;And for the second page you would use:
// Retrieve all users on the second page
const users = await many(userTable, {
limit: {
from: lastUserId,
// In order to skip the lastUser of page 1
skip: 1,
take: 10
}
});