Echo Database Queries
Introduction
Echo Database provides a powerful yet intuitive way to query and retrieve data from your collections. Whether you need to fetch all records, find specific items, or perform complex filtering operations, Echo Database's query system offers the tools you need to efficiently access your data.
In this guide, we'll explore how to construct queries in Echo Database, from basic operations to more advanced techniques. We'll cover filtering, sorting, pagination, and other essential query capabilities that will help you build effective data access patterns in your applications.
Basic Query Operations
Fetching All Records
The simplest query is retrieving all records from a collection. Here's how to do it:
const allUsers = await db.users.getAll();
console.log(allUsers);
Output:
[
{ id: '1', name: 'Alice', age: 28 },
{ id: '2', name: 'Bob', age: 32 },
{ id: '3', name: 'Charlie', age: 24 }
]
Getting a Single Record by ID
To fetch a specific record using its unique ID:
const user = await db.users.get('2');
console.log(user);
Output:
{ id: '2', name: 'Bob', age: 32 }
Filtering Records
Echo Database offers powerful filtering capabilities to find records that match specific criteria.
Simple Filtering
To find records that match an exact field value:
const youngUsers = await db.users.find({ age: 24 });
console.log(youngUsers);
Output:
[{ id: '3', name: 'Charlie', age: 24 }]
Advanced Filtering with Operators
Echo Database supports various comparison operators for more complex queries:
// Find users older than 25
const olderUsers = await db.users.find({
age: { $gt: 25 }
});
console.log(olderUsers);
Output:
[
{ id: '1', name: 'Alice', age: 28 },
{ id: '2', name: 'Bob', age: 32 }
]
Here are the commonly used comparison operators:
$eq: Equal to (default when not specified)$ne: Not equal to$gt: Greater than$gte: Greater than or equal to$lt: Less than$lte: Less than or equal to$in: In an array of values$nin: Not in an array of values
Compound Filtering with Logical Operators
You can combine multiple conditions using logical operators:
// Find users between ages 25 and 30
const middleAgedUsers = await db.users.find({
$and: [
{ age: { $gte: 25 } },
{ age: { $lte: 30 } }
]
});
console.log(middleAgedUsers);
Output:
[{ id: '1', name: 'Alice', age: 28 }]
Logical operators include:
$and: All conditions must be true$or: At least one condition must be true$not: Negates the condition
Sorting Results
You can sort query results by one or more fields:
// Sort users by age in ascending order
const sortedUsers = await db.users.find({}, {
sort: { age: 1 }
});
console.log(sortedUsers);
Output:
[
{ id: '3', name: 'Charlie', age: 24 },
{ id: '1', name: 'Alice', age: 28 },
{ id: '2', name: 'Bob', age: 32 }
]
For descending order, use -1 instead of 1:
// Sort users by age in descending order
const reverseSortedUsers = await db.users.find({}, {
sort: { age: -1 }
});
console.log(reverseSortedUsers);
Output:
[
{ id: '2', name: 'Bob', age: 32 },
{ id: '1', name: 'Alice', age: 28 },
{ id: '3', name: 'Charlie', age: 24 }
]
Pagination
For collections with many records, pagination helps manage data in chunks:
// Get the first page with 2 items per page
const firstPage = await db.users.find({}, {
limit: 2,
skip: 0
});
console.log(firstPage);
// Get the second page
const secondPage = await db.users.find({}, {
limit: 2,
skip: 2
});
console.log(secondPage);
Output (firstPage):
[
{ id: '1', name: 'Alice', age: 28 },
{ id: '2', name: 'Bob', age: 32 }
]
Output (secondPage):
[
{ id: '3', name: 'Charlie', age: 24 }
]
Projection: Selecting Specific Fields
You can limit the fields returned in your query results using projection:
// Get only names from user records
const userNames = await db.users.find({}, {
projection: { name: 1 }
});
console.log(userNames);
Output:
[
{ id: '1', name: 'Alice' },
{ id: '2', name: 'Bob' },
{ id: '3', name: 'Charlie' }
]
To exclude fields instead of including them:
// Get everything except the age field
const usersWithoutAge = await db.users.find({}, {
projection: { age: 0 }
});
console.log(usersWithoutAge);
Output:
[
{ id: '1', name: 'Alice' },
{ id: '2', name: 'Bob' },
{ id: '3', name: 'Charlie' }
]
Aggregation Operations
Echo Database provides basic aggregation functions for common operations:
Count
Count the number of records matching a query:
const adultCount = await db.users.count({ age: { $gte: 18 } });
console.log(`Number of adult users: ${adultCount}`);
Output:
Number of adult users: 3
Sum, Average, Min, Max
For numeric fields, you can perform calculations:
const stats = await db.users.aggregate([
{ $match: {} }, // Match all records
{
$group: {
_id: null,
totalAge: { $sum: "$age" },
averageAge: { $avg: "$age" },
youngestAge: { $min: "$age" },
oldestAge: { $max: "$age" }
}
}
]);
console.log(stats[0]);
Output:
{
_id: null,
totalAge: 84,
averageAge: 28,
youngestAge: 24,
oldestAge: 32
}
Real-World Examples
User Authentication
async function authenticateUser(email, password) {
// Find user with matching email
const user = await db.users.findOne({ email });
if (!user) {
return { success: false, message: "User not found" };
}
// In a real app, you would hash and compare passwords
if (user.password === password) {
return { success: true, user: { id: user.id, name: user.name, email: user.email } };
} else {
return { success: false, message: "Incorrect password" };
}
}
// Usage
const result = await authenticateUser("[email protected]", "securepass");
if (result.success) {
console.log("Login successful:", result.user);
} else {
console.error("Login failed:", result.message);
}
Product Filtering in E-commerce
async function searchProducts(options) {
const query = {};
// Filter by category
if (options.category) {
query.category = options.category;
}
// Filter by price range
if (options.minPrice !== undefined || options.maxPrice !== undefined) {
query.price = {};
if (options.minPrice !== undefined) {
query.price.$gte = options.minPrice;
}
if (options.maxPrice !== undefined) {
query.price.$lte = options.maxPrice;
}
}
// Search by name
if (options.searchTerm) {
query.name = { $regex: options.searchTerm, $options: 'i' }; // Case-insensitive search
}
// Define sort order
const sort = {};
if (options.sortBy) {
sort[options.sortBy] = options.sortOrder === 'desc' ? -1 : 1;
} else {
sort.name = 1; // Default: sort by name ascending
}
// Pagination
const limit = options.limit || 10;
const skip = (options.page || 0) * limit;
// Execute query
return await db.products.find(query, { sort, limit, skip });
}
// Example usage
const electronicsUnder1000 = await searchProducts({
category: 'electronics',
maxPrice: 1000,
sortBy: 'price',
page: 0,
limit: 5
});
console.log(electronicsUnder1000);
Task Management Dashboard
async function getUserTaskSummary(userId) {
// Get all tasks for the user
const allTasks = await db.tasks.find({ userId });
// Get counts by status
const pending = await db.tasks.count({ userId, status: 'pending' });
const inProgress = await db.tasks.count({ userId, status: 'in-progress' });
const completed = await db.tasks.count({ userId, status: 'completed' });
// Get upcoming tasks (due in the next 7 days)
const today = new Date();
const nextWeek = new Date();
nextWeek.setDate(today.getDate() + 7);
const upcoming = await db.tasks.find({
userId,
dueDate: {
$gte: today.toISOString().split('T')[0],
$lte: nextWeek.toISOString().split('T')[0]
},
status: { $ne: 'completed' }
}, {
sort: { dueDate: 1 },
limit: 5
});
return {
counts: { total: allTasks.length, pending, inProgress, completed },
upcomingTasks: upcoming
};
}
// Example usage
const taskSummary = await getUserTaskSummary('user123');
console.log(taskSummary);
Summary
In this guide, we've covered the essentials of querying data in Echo Database:
- Basic operations like fetching all records or a single record by ID
- Filtering data using simple criteria and advanced operators
- Sorting results to organize your data
- Implementing pagination for large data sets
- Selecting specific fields with projection
- Performing simple aggregation operations
- Real-world examples demonstrating practical applications
Echo Database's query system is designed to be intuitive for beginners while offering the flexibility needed for more complex data operations. By mastering these query techniques, you'll be able to efficiently retrieve and manipulate data in your applications.
Additional Resources
- Echo Database API Documentation (fictional link)
- Query Performance Optimization Guide (fictional link)
- Advanced Aggregation Techniques (fictional link)
Exercises
- Basic Query: Write a query to find all users who are exactly 30 years old.
- Compound Query: Find all products that are either in the "electronics" category with a price under 30.
- Sorting and Pagination: Write a query to get the 5 most expensive products in the "clothing" category.
- Aggregation: Calculate the average price of products in each category.
- Real-world Challenge: Implement a function that returns a list of blog posts, filtered by tag, sorted by date, with pagination, including the author's name and avatar (hint: you'll need to use a join-like operation between posts and users collections).
Happy querying!
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)