Today we've released query builders for Rust and TypeScript modules. The purpose of the query builder API is so that you can write views that will take advantage of the unique performance guarantees of SpacetimeDB's query engine, particularly for realtime subscription updates.

The query builder also now allows you to iterate or scan a table in a view, something that previously wasn't possible using only the index accessors exposed by ViewContext and AnonymousViewContext.

The query builder exposes the following query operators:

  • .where() Used for filtering. Equivalent to a WHERE condition in SQL.
  • .leftSemijoin() Equivalent to an inner join in sql where a row is return from the lhs only if it matches with a row on the rhs.
  • .rightSemijoin() Equivalent to an inner join in sql where a row is return from the rhs only if it matches with a row on the lhs.

use spacetimedb::{Identity, Query, Table, ViewContext, AnonymousViewContext};

#[spacetimedb::table(name = player_state)]
pub struct PlayerState {
    #[unique]
    player_id: u64,
    online: bool,
}

#[spacetimedb::table(name = player_internal)]
pub struct Player {
    #[auto_inc]
    #[primary_key]
    id: u64,
    #[unique]
    identity: Identity,
    name: String,
    #[index(btree)]
    age: u8,
}

#[spacetimedb::table(name = moderator_internal)]
pub struct Moderator {
    #[unique]
    player_id: u64,
}

/// Returns all players.
/// Equivalent to `SELECT * FROM player_internal`.
#[spacetimedb::view(name = player, public)]
fn player(ctx: &AnonymousViewContext) -> Query<Player> {
    ctx.from.player_internal().build()
}

/// Returns the caller's player.
/// Equivalent to `SELECT * FROM player_internal WHERE "identity" = :sender`.
#[spacetimedb::view(name = my_player, public)]
fn my_player(ctx: &ViewContext) -> Query<Player> {
    ctx.from.player_internal().r#where(|p| p.identity.eq(ctx.sender)).build()
}

/// Returns only online players.
/// Equivalent to:
/// ```sql
/// SELECT q.*
/// FROM player_state p JOIN player_internal q ON p.player_id = q.id
/// WHERE p.online
/// ```
#[spacetimedb::view(name = online_player, public)]
fn online_player(ctx: &AnonymousViewContext) -> Query<Player> {
    ctx.from
        .player_state()
        .r#where(|p| p.online.eq(true))
        .right_semijoin(ctx.from.player_internal(), |(p, q)| p.player_id.eq(q.id))
        .build()
}

/// Returns only the caller's player if online.
/// Equivalent to:
/// ```sql
/// SELECT q.*
/// FROM player_state p JOIN player_internal q ON p.player_id = q.id
/// WHERE p.online AND q.identity = :sender
/// ```
#[spacetimedb::view(name = my_online_player, public)]
fn my_online_player(ctx: &ViewContext) -> Query<Player> {
    ctx.from
        .player_state()
        .r#where(|p| p.online.eq(true))
        .right_semijoin(ctx.from.player_internal(), |(p, q)| p.player_id.eq(q.id))
        .r#where(|p| p.identity.eq(ctx.sender))
        .build()
}

/// Returns the moderators.
/// Equivalent to:
/// ```sql
/// SELECT p.* FROM player_internal p JOIN moderator_internal m ON p.id = m.player_id
/// ```
#[spacetimedb::view(name = moderator, public)]
fn moderator(ctx: &AnonymousViewContext) -> Query<Player> {
    ctx.from
        .player_internal()
        .left_semijoin(ctx.from.moderator_internal(), |(p, m)| p.id.eq(m.player_id))
        .build()
} 

import { schema, table, t, type RowObj } from 'spacetimedb/server';

const playerState = table('playerState', {
  playerId: t.u64().unique(),
  online: t.bool(),
});

const playerInternal = table('playerInternal', {
  id: t.u64().primaryKey().autoInc(),
  identity: t.identity().unique(),
  name: t.string(),
  age: t.u8().index('btree'),
});

const spacetimedb = schema(playerState, playerInternal);

spacetimedb.view(
  { name: 'my_online_player', public: true },
  t.array(playerInternal.row()),
  ctx => {
    return ctx.from.playerState
      .where(p => p.online.eq(true))
      .rightSemijoin(ctx.from.playerInternal, (p, q) => p.playerId.eq(q.id))
      .where(p => p.identity.eq(ctx.sender))
      .build();
  }
);

  • Fixes an issue with the --delete-data=on-conflict flag of spacetimedb publish
  • Fixes an issue where databases were returning 400/500 errors after publishing with -c
  • Fixes an issue where on_insert and on_delete were not firing correctly for per-client (ViewContext) views

Full Changelog: https://github.com/clockworklabs/SpacetimeDB/compare/v1.10.0...v1.11.0