Querying is how you ask your database for specific information. Instead of getting all records, you can filter, sort, and limit results to get exactly what you need.
Real-world analogy: Querying is like using filters on a shopping website. Instead of seeing all products, you filter by category, price range, brand, etc., to find exactly what you want.
// Find by primary keyconst user = await User.find(1);// Find by primary key or throw errorconst user = await User.findOrFail(1);// Find by any columnconst user = await User.findBy('email', 'john@example.com');// Get first recordconst user = await User.first();// Get first record or throw errorconst user = await User.firstOrFail();
When to use what?
find() - When you have the ID and it’s okay if the record doesn’t exist
findOrFail() - When you have the ID and the record must exist
findBy() - When searching by a specific column value
first() - When you want the first record from a query
How chaining works: Each method returns a query builder object, so you can keep adding more conditions. Think of it like building a sentence: “Get users WHERE active is true AND age is greater than 18, ORDER BY name, LIMIT to 10.”
// Order by single columnconst users = await User.query() .orderBy('name') .get();// Order by multiple columnsconst users = await User.query() .orderBy('role') .orderBy('name', 'desc') .get();// Random orderconst randomUsers = await User.query() .inRandomOrder() .limit(5) .get();// Order by raw SQLconst users = await User.query() .orderByRaw('CASE WHEN role = "admin" THEN 1 ELSE 2 END') .get();
// Group users by roleconst roleStats = await User.query() .select('role') .selectRaw('COUNT(*) as count') .groupBy('role') .get();// Only show roles with more than 5 usersconst popularRoles = await User.query() .select('role') .selectRaw('COUNT(*) as count') .groupBy('role') .having('count', '>', 5) .get();// Complex groupingconst monthlyStats = await Post.query() .selectRaw('YEAR(created_at) as year') .selectRaw('MONTH(created_at) as month') .selectRaw('COUNT(*) as post_count') .selectRaw('AVG(views) as avg_views') .groupBy('year', 'month') .orderBy('year', 'desc') .orderBy('month', 'desc') .get();
// Inner joinconst postsWithAuthors = await Post.query() .join('users', 'posts.user_id', 'users.id') .select('posts.*', 'users.name as author_name') .get();// Left join (includes posts without authors)const allPosts = await Post.query() .leftJoin('users', 'posts.user_id', 'users.id') .select('posts.*', 'users.name as author_name') .get();// Multiple joinsconst postsWithDetails = await Post.query() .join('users', 'posts.user_id', 'users.id') .join('categories', 'posts.category_id', 'categories.id') .select( 'posts.*', 'users.name as author_name', 'categories.name as category_name' ) .get();
When to use joins vs relationships: Use joins when you need specific data from related tables in a single query. Use relationships (covered in the next section) when you want to work with related models as objects.
// Raw where conditionsconst users = await User.query() .whereRaw('age > ? AND credits < ?', [25, 100]) .get();// Raw selectconst users = await User.query() .selectRaw('*, YEAR(created_at) as registration_year') .get();// Raw order byconst users = await User.query() .orderByRaw('CASE WHEN role = "admin" THEN 1 ELSE 2 END') .get();// Completely raw queryconst results = await User.query() .raw('SELECT role, COUNT(*) as count FROM users GROUP BY role');
Security Note: Always use parameter binding (?) in raw queries to prevent SQL injection attacks. Never concatenate user input directly into SQL strings.
Add a computed column from a subquery without a join:
// Add each user's latest post title as a columnconst users = await User.query() .addSelect({ latest_post_title: (sub) => sub .from('posts') .select('title') .whereRaw('posts.user_id = users.id') .orderBy('created_at', 'desc') .limit(1), }) .get();users[0].latest_post_title; // "My latest article"
Sort results by a value computed from another table:
// Order users by the number of posts they haveconst users = await User.query() .orderBySubquery((sub) => { sub.from('posts').count('id').whereRaw('posts.user_id = users.id'); }, 'desc') .get();
When you use a scoped query to create records, withPendingAttributes() lets the scope automatically set default column values on any model created through it:
// In a Post modelstatic scopePublished(query) { return query .where('status', 'published') .withPendingAttributes({ status: 'published' });}// Creating through the scope automatically sets status = 'published'const post = await Post.query().published().new({ title: 'Hello World' });// post.status === 'published' (set by the scope, not the caller)await post.save();
You can also call create() on the scoped query:
const post = await Post.query().published().create({ title: 'Hello World' });// Inserts with status = 'published'
sole() is like firstOrFail() but also throws if more than one record matches. Use it when your query should always return exactly one row:
// Throws ModelNotFoundException if no match, Error if multiple matchesconst config = await AppConfig.query() .where('key', 'site_title') .sole();// Good for unique constraints — catches duplicates earlyconst user = await User.query() .where('email', req.body.email) .sole(); // alerts you if email uniqueness is broken
Use whereHas / doesntHave to filter by whether a named relationship exists. These build a WHERE EXISTS subquery automatically.
The related model must be registered in ModelRegistry (call static { this.register(); } in its class body) so the ORM can resolve its table name at query-build time.
// Users who have at least one postconst authors = await User.query().has('posts').get();// Users who have more than 5 postsconst prolific = await User.query().has('posts', '>', 5).get();// Users who have at least one published post (with a constraint)const publishedAuthors = await User.query() .whereHas('posts', (query) => { query.where('is_published', true); }) .get();// Users with no posts at allconst lurkers = await User.query().doesntHave('posts').get();// Also available as:const lurkers2 = await User.query().whereDoesntHave('posts').get();// Check if any record existsconst hasAdmins = await User.query().where('role', 'admin').exists();const noAdmins = await User.query().where('role', 'admin').doesntExist();
has(relation, operator?, count?) is the simple form — filter by relation existence with an optional count. Use whereHas when you need to add constraints on the related records themselves.
// Manual WHERE EXISTS (use when you need full control over the subquery)const authorsWithPosts = await User.query() .whereExists((query) => { query.select('*') .from('posts') .whereRaw('posts.user_id = users.id'); }) .get();
Make sure your database has indexes on columns you query frequently:
// If you often query by email, make sure there's an indexconst user = await User.findBy('email', 'john@example.com');// If you often filter by role and status, consider a compound indexconst users = await User.query() .where('role', 'admin') .where('is_active', true) .get();
