Query Data

Initialize SDK

import cloudbase from "@cloudbase/node-sdk";

const app = cloudbase.init({
  env: "your-env-id", // Replace with your environment id
});

const db = app.rdb();

// Or specify instance and database
// const db = app.rdb({
//   instance: "<instance>",
//   database: "<database>"
// });

Basic Query

Query table data using the select() method, which supports conditional filtering, join queries, and more.

db.from(tableName).select(columns, options)

• tableName: Table name
• columns: Columns to retrieve, separated by commas
• options: Query options configuration

Parameters

Parameter	Type	Required	Description
columns	string	No	Columns to retrieve, separated by commas. Supports using aliasName:foreignKey(columns) syntax to rename foreign key related fields columns set to * queries all fields
options	object	No	Query options configuration

Options Parameters

Parameter	Type	Required	Description
count	string	No	Counting algorithm, available value: "exact" - executes COUNT(*) under the hood
head	boolean	No	When set to true, does not return data, only useful when counting is needed

Code Examples

Query All Data

// Query all data from the articles table
const { data, error } = await db.from("articles").select();

console.log('Query result:', data);

Query Specific Columns

// Only query article title and creation time
const { data, error } = await db.from("articles").select("id, title, created_at");

console.log('Query result:', data);

Query Foreign Key Related Data

// Use alias 'category' instead of default 'categories' field name
const { data, error } = await db.from("articles").select(`
    id, 
    title,
    created_at,
    category:categories(name)
  `);

console.log('Query result:', data);

Return Result

{
  data: [
    {
      id: 1,
      title: "Article Title",
      created_at: "2023-01-01T00:00:00Z",
      category: {
        name: "Frontend Development"
      }
    },
    // ... other records
  ],
  error: null
}

Join Table Query

Join queries allow you to retrieve data from multiple tables simultaneously, supporting one-to-one, one-to-many, and other relationship types.

Example Table Structure

To better understand join queries, let's first look at the table structure used in the examples:

Table Relationship Description:

• articles table establishes a many-to-one relationship with categories table through category_id
• articles table establishes a many-to-one relationship with users table through created_by (creator)
• articles table establishes a many-to-one relationship with users table through updated_by (updater)

View complete SQL table creation statements

-- Users table
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(100)
);

-- Categories table
CREATE TABLE categories (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100)
);

-- Articles table
CREATE TABLE articles (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(256),
  content TEXT,
  category_id BIGINT,
  created_by BIGINT,
  updated_by BIGINT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id),
  CONSTRAINT articles_created_by_fkey FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT articles_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES users(id)
);

Basic Join Query

// Query article data while retrieving related category information
// Based on the relationship articles.category_id = categories.id
const { data, error } = await db.from("articles").select(`
    title,
    categories(name)
  `);

console.log('Query result:', data);

View return result example

[
  {
    title: "JavaScript Tutorial for Beginners",
    categories: {
      name: "Frontend Development"
    }
  },
  {
    title: "Node.js in Action",
    categories: {
      name: "Backend Development"
    }
  }
]

Multiple Join Query

// Query articles while retrieving creator and updater information
// Use aliases to distinguish different user relationships, making returned data more understandable
const { data, error } = await db.from("articles").select(`
    title,
    creator:users!articles_created_by_fkey(name),
    updater:users!articles_updated_by_fkey(name)
  `);

console.log('Query result:', data);

View return result example

[
  {
    title: "JavaScript Tutorial for Beginners",
    creator: {  // Use alias 'creator' to represent the creator
      name: "Zhang San"
    },
    updater: {  // Use alias 'updater' to represent the updater
      name: "Li Si"
    }
  }
]

💡 Note: When the same table is related multiple times through different foreign keys, you must use the foreign key constraint name (e.g., !articles_created_by_fkey) to distinguish different relationships. It's recommended to use alias syntax to make the returned data fields more semantic.

Nested Join Query

// Query categories and all articles under them, along with article author information
// This is a three-level nested join query: categories -> articles -> users
const { data, error } = await db.from("categories").select(`
    name,
    articles (
      title,
      users (
        name
      )
    )
  `);

console.log('Query result:', data);

View return result example

[
  {
    name: "Frontend Development",
    articles: [
      {
        title: "JavaScript Tutorial for Beginners",
        users: {
          name: "Zhang San"
        }
      },
      {
        title: "Vue.js in Action",
        users: {
          name: "Li Si"
        }
      }
    ]
  },
  {
    name: "Backend Development",
    articles: [
      {
        title: "Node.js in Action",
        users: {
          name: "Wang Wu"
        }
      }
    ]
  }
]

Advanced Query

Conditional Filtering Query

// Query all articles under a specific category
// Filter by category name to get all articles under that category
const { data, error } = await db
  .from("articles")
  .select("title, categories(*)")
  .eq("categories.name", "Technical Articles");

console.log('Query result:', data);

View return result example

[
  {
    title: "Advanced JavaScript Features",
    categories: {
      id: 1,
      name: "Technical Articles"
    }
  },
  {
    title: "Database Optimization Tips",
    categories: {
      id: 1,
      name: "Technical Articles"
    }
  }
]

Count Query

// Get each category and the number of articles it contains
// count will count how many articles are under each category
const { data, error } = await db
  .from("categories")
  .select(`*, articles(count)`);

console.log('Query result:', data);

View return result example

[
  {
    id: 1,
    name: "Frontend Development",
    articles: [
      { count: 5 }  // This category has 5 articles
    ]
  },
  {
    id: 2,
    name: "Backend Development",
    articles: [
      { count: 3 }  // This category has 3 articles
    ]
  }
]

Get Total Count Only

// Only get the total number of articles without returning specific data
const { count, error } = await db
  .from("articles")
  .select("*", { count: "exact", head: true });

console.log('Total count:', count);

Inner Join Query

// Only get articles with categories, use inner join to ensure category exists
// !inner ensures only articles with matching records in the categories table are returned
const { data, error } = await db
  .from("articles")
  .select("title, categories!inner(name)")
  .eq("categories.name", "Tutorial")
  .limit(10);

console.log('Query result:', data);

View return result example

[
  {
    title: "JavaScript Basics Tutorial",
    categories: {
      name: "Tutorial"
    }
  },
  {
    title: "React Getting Started Tutorial",
    categories: {
      name: "Tutorial"
    }
  }
]

💡 Note: !inner represents an inner join, returning only data that has matching records in the related table. Without !inner, even if an article's category_id is null or points to a non-existent category, the article will still be returned, but the categories field will be null.

Related Documentation

• Filters - Learn how to use filter conditions
• Modifiers - Learn how to use query modifiers