cameronapak/bknd-crud-read

CRUD Read

Query and retrieve data from your Bknd database using the SDK or REST API.

Prerequisites

• Bknd project running (local or deployed)
• Entity exists with data (use bknd-create-entity, bknd-crud-create)
• SDK configured or API endpoint known

When to Use UI Mode

• Browsing data manually
• Quick lookups during development
• Verifying data after operations

UI steps: Admin Panel > Data > Select Entity > Browse/search records

When to Use Code Mode

• Application data display
• Search/filter functionality
• Building lists, tables, detail pages
• API integrations

Code Approach

Step 1: Set Up SDK Client

import { Api } from "bknd";

const api = new Api({
  host: "http://localhost:7654",
});

// If auth required:
api.updateToken("your-jwt-token");

Step 2: Read Single Record by ID

Use readOne(entity, id, query?):

const { ok, data, error } = await api.data.readOne("posts", 1);

if (ok) {
  console.log("Post:", data.title);
} else {
  console.error("Not found or error:", error.message);
}

Step 3: Read Single Record by Query

Use readOneBy(entity, query) to find by field value:

const { data } = await api.data.readOneBy("users", {
  where: { email: { $eq: "[email protected]" } },
});

if (data) {
  console.log("Found user:", data.id);
}

Step 4: Read Multiple Records

Use readMany(entity, query?):

const { ok, data, meta } = await api.data.readMany("posts", {
  where: { status: { $eq: "published" } },
  sort: { created_at: "desc" },
  limit: 20,
  offset: 0,
});

console.log(`Found ${meta.total} total, showing ${data.length}`);

Step 5: Handle Response

The response object structure:

type ReadResponse = {
  ok: boolean;
  data?: T | T[];         // Single object or array
  meta?: {                // For readMany
    total: number;        // Total matching records
    limit: number;        // Current page size
    offset: number;       // Current offset
  };
  error?: {
    message: string;
    code: string;
  };
};

Filtering (Where Clause)

Comparison Operators

// Equality (implicit or explicit)
{ where: { status: "published" } }
{ where: { status: { $eq: "published" } } }

// Not equal
{ where: { status: { $ne: "deleted" } } }

// Numeric comparisons
{ where: { age: { $gt: 18 } } }    // Greater than
{ where: { age: { $gte: 18 } } }   // Greater or equal
{ where: { price: { $lt: 100 } } } // Less than
{ where: { price: { $lte: 100 } } } // Less or equal

String Operators

// LIKE patterns (% = wildcard)
{ where: { title: { $like: "%hello%" } } }      // Contains (case-sensitive)
{ where: { title: { $ilike: "%hello%" } } }     // Contains (case-insensitive)

// Convenience methods
{ where: { name: { $startswith: "John" } } }
{ where: { email: { $endswith: "@gmail.com" } } }
{ where: { bio: { $contains: "developer" } } }

Array Operators

// In array
{ where: { id: { $in: [1, 2, 3] } } }

// Not in array
{ where: { type: { $nin: ["archived", "deleted"] } } }

Null Checks

// Is NULL
{ where: { deleted_at: { $isnull: true } } }

// Is NOT NULL
{ where: { published_at: { $isnull: false } } }

Logical Operators

// AND (implicit - multiple fields)
{
  where: {
    status: { $eq: "published" },
    category: { $eq: "news" },
  }
}

// OR
{
  where: {
    $or: [
      { status: { $eq: "published" } },
      { featured: { $eq: true } },
    ]
  }
}

// Combined AND/OR
{
  where: {
    category: { $eq: "news" },
    $or: [
      { status: { $eq: "published" } },
      { author_id: { $eq: currentUserId } },
    ]
  }
}

Sorting

// Object syntax (preferred)
{ sort: { created_at: "desc" } }
{ sort: { name: "asc", created_at: "desc" } }  // Multi-sort

// String syntax (- prefix = descending)
{ sort: "-created_at" }
{ sort: "name,-created_at" }

Field Selection

Reduce payload by selecting specific fields:

const { data } = await api.data.readMany("users", {
  select: ["id", "email", "name"],
});
// data[0] only has id, email, name

Loading Relations

With Clause (Separate Queries)

// Simple - load relations
{ with: "author" }
{ with: ["author", "comments"] }
{ with: "author,comments" }

// Nested with subquery options
{
  with: {
    author: {
      select: ["id", "name", "avatar"],
    },
    comments: {
      where: { approved: { $eq: true } },
      sort: { created_at: "desc" },
      limit: 10,
      with: ["user"],  // Nested loading
    },
  }
}

Result structure:

const { data } = await api.data.readOne("posts", 1, {
  with: ["author", "comments"],
});

console.log(data.author.name);      // Nested object
console.log(data.comments[0].text); // Nested array

Join Clause (SQL JOIN)

Use join to filter by related fields:

const { data } = await api.data.readMany("posts", {
  join: ["author"],
  where: {
    "author.role": { $eq: "admin" },  // Filter by joined field
  },
  sort: "-author.created_at",         // Sort by joined field
});

With vs Join

| Feature | with | join | |---------|--------|--------| | Query method | Separate queries | SQL JOIN | | Return structure | Nested objects | Flat (unless also with) | | Use case | Load related data | Filter by related fields | | Performance | Multiple queries | Single query |

Pagination

// Page 1 (records 0-19)
{ limit: 20, offset: 0 }

// Page 2 (records 20-39)
{ limit: 20, offset: 20 }

// Generic page formula
{ limit: pageSize, offset: (page - 1) * pageSize }

Default limit is 10 if not specified.

Pagination Helper

async function paginate<T>(
  entity: string,
  page: number,
  pageSize: number,
  query: object = {}
) {
  const { data, meta } = await api.data.readMany(entity, {
    ...query,
    limit: pageSize,
    offset: (page - 1) * pageSize,
  });

  return {
    data,
    page,
    pageSize,
    total: meta.total,
    totalPages: Math.ceil(meta.total / pageSize),
    hasNext: page * pageSize < meta.total,
    hasPrev: page > 1,
  };
}

REST API Approach

Read Many

# Basic
curl http://localhost:7654/api/data/posts

# With query params
curl "http://localhost:7654/api/data/posts?limit=20&offset=0&sort=-created_at"

# With where clause
curl "http://localhost:7654/api/data/posts?where=%7B%22status%22%3A%22published%22%7D"

Read One by ID

curl http://localhost:7654/api/data/posts/1

Complex Query (POST)

For complex queries, use POST to /api/data/:entity/query:

curl -X POST http://localhost:7654/api/data/posts/query \
  -H "Content-Type: application/json" \
  -d '{
    "where": {"status": {"$eq": "published"}},
    "sort": {"created_at": "desc"},
    "limit": 20,
    "with": ["author"]
  }'

Read Related Records

# Get user's posts
curl http://localhost:7654/api/data/users/1/posts

React Integration

Basic List

import { useApp } from "bknd/react";
import { useEffect, useState } from "react";

function PostsList() {
  const { api } = useApp();
  const [posts, setPosts] = useState([]);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    api.data.readMany("posts", {
      where: { status: { $eq: "published" } },
      sort: { created_at: "desc" },
      limit: 20,
    }).then(({ data }) => {
      setPosts(data);
      setLoading(false);
    });
  }, []);

  if (loading) return <div>Loading...</div>;

  return (
    <ul>
      {posts.map((post) => (
        <li key={post.id}>{post.title}</li>
      ))}
    </ul>
  );
}

With SWR

import { useApp } from "bknd/react";
import useSWR from "swr";

function PostsList() {
  const { api } = useApp();

  const { data: posts, isLoading, error } = useSWR(
    "posts-published",
    () => api.data.readMany("posts", {
      where: { status: { $eq: "published" } },
      sort: { created_at: "desc" },
    }).then((r) => r.data)
  );

  if (isLoading) return <div>Loading...</div>;
  if (error) return <div>Error loading posts</div>;

  return (
    <ul>
      {posts?.map((post) => (
        <li key={post.id}>{post.title}</li>
      ))}
    </ul>
  );
}

Detail Page

function PostDetail({ postId }: { postId: number }) {
  const { api } = useApp();

  const { data: post, isLoading } = useSWR(
    `post-${postId}`,
    () => api.data.readOne("posts", postId, {
      with: ["author", "comments"],
    }).then((r) => r.data)
  );

  if (isLoading) return <div>Loading...</div>;
  if (!post) return <div>Post not found</div>;

  return (
    <article>
      <h1>{post.title}</h1>
      <p>By {post.author?.name}</p>
      <div>{post.content}</div>
      <h2>Comments ({post.comments?.length})</h2>
    </article>
  );
}

Search with Debounce

import { useState, useMemo } from "react";
import { useApp } from "bknd/react";
import useSWR from "swr";
import { useDebouncedValue } from "@mantine/hooks"; // or custom hook

function SearchPosts() {
  const { api } = useApp();
  const [search, setSearch] = useState("");
  const [debouncedSearch] = useDebouncedValue(search, 300);

  const { data: results, isLoading } = useSWR(
    debouncedSearch ? `search-${debouncedSearch}` : null,
    () => api.data.readMany("posts", {
      where: { title: { $ilike: `%${debouncedSearch}%` } },
      limit: 10,
    }).then((r) => r.data)
  );

  return (
    <div>
      <input
        value={search}
        onChange={(e) => setSearch(e.target.value)}
        placeholder="Search posts..."
      />
      {isLoading && <p>Searching...</p>}
      <ul>
        {results?.map((post) => (
          <li key={post.id}>{post.title}</li>
        ))}
      </ul>
    </div>
  );
}

Full Example

import { Api } from "bknd";

const api = new Api({ host: "http://localhost:7654" });

// Get single post with relations
const { data: post } = await api.data.readOne("posts", 1, {
  with: {
    author: { select: ["id", "name"] },
    tags: true,
  },
});
console.log(post.title, "by", post.author.name);

// Find user by email
const { data: user } = await api.data.readOneBy("users", {
  where: { email: { $eq: "[email protected]" } },
});

// List published posts with pagination
const { data: posts, meta } = await api.data.readMany("posts", {
  where: {
    status: { $eq: "published" },
    deleted_at: { $isnull: true },
  },
  sort: { created_at: "desc" },
  limit: 10,
  offset: 0,
  with: ["author"],
});
console.log(`Page 1 of ${Math.ceil(meta.total / 10)}`);

// Complex query: posts by admin authors in category
const { data: adminPosts } = await api.data.readMany("posts", {
  join: ["author"],
  where: {
    "author.role": { $eq: "admin" },
    category: { $eq: "announcements" },
    $or: [
      { status: { $eq: "published" } },
      { featured: { $eq: true } },
    ],
  },
  select: ["id", "title", "created_at"],
  sort: "-created_at",
});

Common Patterns

Count Records

const { data } = await api.data.count("posts", {
  status: { $eq: "published" },
});
console.log(`${data.count} published posts`);

Check Existence

const { data } = await api.data.exists("users", {
  email: { $eq: "[email protected]" },
});
if (data.exists) {
  console.log("Email already registered");
}

Soft Delete Filter

// Always exclude soft-deleted
const { data } = await api.data.readMany("posts", {
  where: { deleted_at: { $isnull: true } },
});

Get User's Related Data

// Using readManyByReference
const { data: userPosts } = await api.data.readManyByReference(
  "users", userId, "posts",
  { sort: { created_at: "desc" }, limit: 10 }
);

Common Pitfalls

Not Checking Response

Problem: Assuming data exists.

Fix: Always check ok or handle undefined:

// Wrong
const { data } = await api.data.readOne("posts", 999);
console.log(data.title);  // Error if not found!

// Correct
const { ok, data } = await api.data.readOne("posts", 999);
if (!ok || !data) {
  console.log("Post not found");
  return;
}
console.log(data.title);

Wrong Operator Syntax

Problem: Using operators incorrectly.

Fix: Wrap values in operator object:

// Wrong
{ where: { age: ">18" } }

// Correct
{ where: { age: { $gt: 18 } } }

Missing Join for Related Filter

Problem: Filtering by related field without join.

Fix: Add join clause:

// Wrong - won't work
{ where: { "author.role": { $eq: "admin" } } }

// Correct - add join
{
  join: ["author"],
  where: { "author.role": { $eq: "admin" } }
}

N+1 Query Problem

Problem: Loading relations in a loop.

Fix: Use with to load relations in batch:

// Wrong - N+1 queries
const { data: posts } = await api.data.readMany("posts");
for (const post of posts) {
  const { data: author } = await api.data.readOne("users", post.author_id);
}

// Correct - single batch query
const { data: posts } = await api.data.readMany("posts", {
  with: ["author"],
});
posts.forEach(p => console.log(p.author.name));

Case-Sensitive Search

Problem: $like is case-sensitive.

Fix: Use $ilike for case-insensitive:

// Case-sensitive (may miss results)
{ where: { title: { $like: "%React%" } } }

// Case-insensitive
{ where: { title: { $ilike: "%react%" } } }

Verification

Test queries in admin panel first:

1. Admin Panel > Data > Select Entity
2. Use filters/search UI
3. Check returned data matches expectations

Or log response in code:

const response = await api.data.readMany("posts", query);
console.log("Response:", JSON.stringify(response, null, 2));

DOs and DON'Ts

DO:

• Check ok before accessing data
• Use with for loading relations
• Use join when filtering by related fields
• Use $ilike for case-insensitive text search
• Use pagination for large datasets
• Use select to reduce payload size

DON'T:

• Assume queries always return data
• Load relations in a loop (N+1 problem)
• Forget join when filtering by relation fields
• Use $like when case-insensitive needed
• Request all fields when only few needed
• Skip pagination for potentially large datasets

Related Skills

• bknd-crud-create - Insert records to query
• bknd-crud-update - Modify queried records
• bknd-crud-delete - Remove queried records
• bknd-query-filter - Advanced filtering techniques
• bknd-pagination - Pagination strategies
• bknd-define-relationship - Set up relations to load with with