Tables and Queries#

Every application needs a way to store and query collections of things. Rather than making you integrate an external database and deal with connection pooling, ORM mapping, and schema migrations, I just... put a database in the language. Tables in Adama give you CRUD operations and a LINQ-style query syntax, all reactive, all type-checked at compile time.

Is it a full SQL database? No. But for the kinds of applications Adama is designed for — games, collaborative tools, real-time apps — it's exactly what you need.

Defining Tables#

A table is a collection of records of a specific type. First, define a record type, then declare a table that holds records of that type.

Basic Table Definition#

record Item {
  public int id;
  public string name;
  public int quantity;
  public double price;
}

table<Item> items;

Naming Convention: Tables are typically prefixed with an underscore (e.g., _items) because they are never directly visible to clients. Only query results exposed through formulas or bubbles are visible. I do this consistently in my own code and I'd recommend it — it makes it obvious at a glance what's a table versus a field.

The id Field#

Every record in a table automatically gets an id field if you declare one as an integer. This serves as the primary key and is auto-generated when records are inserted.

record Player {
  public int id;      // Auto-generated primary key
  public string name;
  public int score;
}

table<Player> _players;

CRUD Operations#

Create (Insert)#

Use the ingestion operator (<-) to insert records into a table.

Insert from an anonymous object:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

@construct {
  _players <- {name: "Dave", score: 0};
}

Insert from a message:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

message NewPlayer {
  string name;
  int score;
}

channel addPlayer(NewPlayer np) {
  _players <- np;
}

Capture the generated id:

Use the as keyword to capture the auto-generated id after insertion:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

@construct {
  _players <- {name: "Eve", score: 50} as newId;
  // newId now contains the id of the newly inserted record
}

This is useful when you need to reference the new record immediately after creation.

Read (Query)#

Reading from tables uses the iterate keyword combined with query operators. More on this below — there's a lot to cover.

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

// Get all players
public formula allPlayers = iterate _players;

// Get players with high scores
public formula topPlayers = iterate _players where score > 100;

Update#

To update records, query for them and then assign to their fields. This is called bulk assignment and it's one of those features that feels weird at first but then you realize how powerful it is.

Update a single record by id:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

@construct {
  (iterate _players where id == 5).score = 200;
}

Update multiple records:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

@construct {
  // Give all players 10 bonus points
  (iterate _players).score += 10;
}

Update using a counter:

record Player {
  public int id;
  public string name;
  public int score;
  public int ranking;
}

table<Player> _players;

@construct {
  int order = 0;
  (iterate _players shuffle).ranking = order++;
}

Delete#

Every record has an implicit delete() method that removes it from its table.

Delete a single record:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

@construct {
  (iterate _players where id == 5).delete();
}

Delete multiple records:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

@construct {
  // Remove all players with zero score
  (iterate _players where score == 0).delete();
}

Delete with limit:

record ChatEntry {
  public int id;
  public string text;
  public datetime when;
}

table<ChatEntry> _chat;

@construct {
  // Remove the oldest 10 chat messages
  (iterate _chat order by when asc limit 10).delete();
}

Size#

Get the number of records in a table using .size():

record Player {
  public int id;
  public string name;
  public int score;
  public bool active;
}

table<Player> _players;

int playerCount = _players.size();

// Size of a query result
int activeCount = (iterate _players where active).size();

The iterate Keyword#

The iterate keyword converts a table into a queryable list. It's the starting point for all query operations.

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

public formula all = iterate _players;

By itself, iterate returns records in their canonical ordering (by id, which reflects insertion order). The result is a list<T> that can be further refined with query operators.

Lazy Evaluation: The list is lazily constructed. Query operations are optimized and only materialized when consumed by a formula, loop, or other operation. I put a fair amount of work into making this efficient.

Query Operators#

Adama has a set of LINQ-style query operators that chain together. If you've used C# LINQ or Java streams, this will feel natural.

where (Filter)#

Filter records based on a boolean condition:

record Player {
  public int id;
  public string name;
  public int score;
  public int age;
  public bool active;
}

table<Player> _players;

public formula youngPlayers = iterate _players where age < 18;

public formula activeHighScorers =
  iterate _players
  where active && score > 100;

The where clause has access to all fields of the record being tested.

where_as (Scoped Filter)#

When you need to avoid naming conflicts between the record fields and external variables, use where_as to bind the record to a named variable:

record Player {
  public int id;
  public string name;
  public int score;
  public int age;
}

table<Player> _players;

view int age;

bubble playersYoungerThanViewer =
  iterate _players
  where_as p: p.age < @viewer.age;

This is especially useful in bubbles where @viewer fields might share names with record fields. Without where_as, the name age would be ambiguous — is it the record's age or the viewer's age? The where_as pattern resolves that.

order (Simple Ordering)#

Order results by a field in ascending order:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

public formula playersByScore = iterate _players order score;

order by (Explicit Direction)#

Specify the sort direction explicitly with asc (ascending) or desc (descending):

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

public formula topScorers =
  iterate _players
  order by score desc;

public formula alphabetical =
  iterate _players
  order by name asc;

Multi-Field Ordering#

Order by multiple fields by separating them with commas:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

public formula ranked =
  iterate _players
  order by score desc, name asc;

Records are first sorted by the first field, then records with equal values are sorted by the second field, and so on.

order_dyn (Dynamic Ordering)#

Allow runtime control of ordering with a string expression:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

view string sortBy;

bubble sortedPlayers =
  iterate _players
  order_dyn @viewer.sortBy;

The string format supports:

• "score" - sort by score ascending
• "-score" - sort by score descending
• "score,name" - sort by score, then by name
• "-score,name" - sort by score descending, then name ascending

I added this because every real application needs client-controlled sorting, and baking it into the query language was easier than making everyone write switch statements.

shuffle (Random Order)#

Randomize the order of results:

record Player {
  public int id;
  public string name;
  public int score;
  public bool active;
}

table<Player> _players;

public formula randomPlayers = iterate _players shuffle;

public formula randomActivePlayer =
  iterate _players
  where active
  shuffle
  limit 1;

Note: shuffle materializes the list at that point in the query chain. It has to — you can't lazily randomize.

limit (Restrict Count)#

Limit the number of results returned:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

public formula topThree =
  iterate _players
  order by score desc
  limit 3;

offset (Skip Records)#

Skip a number of records before returning results. Used with limit for pagination:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

// Skip the first player, get the next one
public formula secondPlace =
  iterate _players
  order by score desc
  offset 1
  limit 1;

// Page 3 of results (10 items per page)
public formula page3 =
  iterate _players
  order by name asc
  offset 20
  limit 10;

Query Operator Reference#

Aggregations#

Size of Results#

Get the count of records matching a query:

record Player {
  public int id;
  public string name;
  public int score;
  public bool active;
}

table<Player> _players;

int activeCount = (iterate _players where active).size();

public formula hasPlayers = _players.size() > 0;

Sum#

Sum a numeric field across all records in a list:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

// Sum all player scores
auto totalScore = (iterate _players).score.sum();

// Sum returns a maybe<T>, use getOrDefaultTo for a concrete value
long total = (iterate _players).score.sum().getOrDefaultTo(0);

Average#

Calculate the average of a numeric field:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

auto avgScore = (iterate _players).score.average();

double average = (iterate _players).score.average().getOrDefaultTo(0.0);

Accessing Specific Elements#

Use array indexing to access specific elements. Indexing returns a maybe<T> since the element might not exist:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

string currentChampion;

@construct {
  // Get the first player (maybe empty if no players)
  maybe<Player> firstPlayer = (iterate _players)[0];

  // Get the top scorer
  if ((iterate _players order by score desc)[0] as winner) {
    // winner is available here
    currentChampion = winner.name;
  }
}

Pattern for safe access:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

procedure updatePlayer(int targetId) {
  // Using if-as to safely unwrap
  if ((iterate _players where id == targetId)[0] as player) {
    // player exists, work with it
    player.score += 10;
  } else {
    // no player found with that id
  }
}

This if-as pattern for table lookups is something you'll write a hundred times. It looks verbose the first time, but it becomes second nature.

Converting to Array#

Convert a list to an array for indexed access in loops:

record Player {
  public int id;
  public string name;
  public int score;
  public int rank;
}

table<Player> _players;

@construct {
  Player[] orderedPlayers = (iterate _players order by score desc).toArray();

  for (int i = 0; i < orderedPlayers.size(); i++) {
    if (orderedPlayers[i] as p) {
      p.rank = i + 1;
    }
  }
}

Formulas with Queries#

Queries and reactive formulas fit together naturally. This is where the magic happens — you write a query once, and connected clients automatically see updated results whenever the underlying data changes.

Public Formulas#

Expose query results to all connected clients:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

public formula leaderboard =
  iterate _players
  order by score desc
  limit 10;

public formula totalPlayers = _players.size();

public formula gameInProgress = _players.size() >= 2;

Bubbles (Per-Viewer Queries)#

Create personalized views that depend on who is viewing:

enum Suit { Clubs, Hearts, Spades, Diamonds }
enum Rank { Two:2, Three:3, Four:4, Five:5, Six:6, Seven:7,
            Eight:8, Nine:9, Ten:10, Jack:11, Queen:12, King:13, Ace:14 }

record Card {
  public int id;
  public Suit suit;
  public Rank rank;
  private principal owner;
}

table<Card> _deck;

record Item {
  public int id;
  public string name;
  public string category;
}

table<Item> _items;

bubble myCards =
  iterate _deck
  where owner == @who
  order by suit asc, rank asc;

view string category;
bubble filteredItems =
  iterate _items
  where category == @viewer.category || @viewer.category == "";

Computed Fields in Records#

Use auto for computed fields that reference other fields:

record Comment {
  public int id;
  public string text;
}

record Submission {
  public int id;
  public string title;
  table<Comment> _comments;

  public formula numComments = _comments.size();
}

Bulk Operations#

Bulk Field Assignment#

Assign a value to a field on all matching records:

record Player {
  public int id;
  public string name;
  public int score;
  public int ranking;
}

table<Player> _players;

@construct {
  // Reset all scores to zero
  (iterate _players).score = 0;

  // Assign rankings based on shuffled order
  int rank = 1;
  (iterate _players shuffle).ranking = rank++;
}

Bulk Method Execution#

Call a method on every matching record:

record Card {
  int ordering;
  principal owner;

  method reset() {
    ordering = Random.genInt();
    owner = @no_one;
  }
}

table<Card> _deck;

procedure shuffleDeck() {
  (iterate _deck).reset();
}

Indexing for Performance#

For tables with many records, indexing specific fields dramatically improves query performance. Without an index, every where clause scans the entire table. With an index, equality lookups are fast.

Creating an Index#

Use the index keyword inside a record definition:

record User {
  public int id;
  public string email;
  public int organizationId;
  public bool active;

  index organizationId;
  index active;
}

table<User> _users;

How Indexes Work#

When you query with an equality condition on an indexed field, Adama uses the index to find matching records directly instead of scanning the entire table:

record User {
  public int id;
  public string email;
  public int organizationId;
  public bool active;

  index organizationId;
  index active;
}

table<User> _users;

// Fast: uses the organizationId index
public formula orgUsers =
  iterate _users
  where organizationId == 42;

// Fast: uses the active index
public formula activeUsers =
  iterate _users
  where active == true;

// Fast: uses both indexes
public formula activeOrgUsers =
  iterate _users
  where organizationId == 42 && active == true;

When to Index: Index fields that you frequently filter on with equality conditions (==). Range conditions (<, >, <=, >=) do not benefit from indexes. And don't index everything — indexes consume memory and slow down inserts. Only index what you actually query.

Map (Transformation)#

The map operator transforms each record through a function or lambda:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

message PlayerView {
  int id;
  string name;
}

// Map with a named function
procedure make_simple_view(Player p) -> PlayerView readonly {
  return @convert<PlayerView>(p);
}

public formula simple_players = iterate _players map make_simple_view;

// Map with an inline lambda (single expression)
public formula player_names = iterate _players map @lambda x: x.name;

// Map with lambda for computed values
public formula player_summaries =
  iterate _players map @lambda x: x.name + " (" + x.score + ")";

The @lambda x: expr syntax creates an inline transformation where x is bound to each record.

Fuzzy Search with =?#

The =? operator performs a fuzzy/contains match — useful for search functionality:

record Player {
  public int id;
  public string name;
  public int score;
  public string searchable;
}

table<Player> _players;

view string search;

bubble search_results =
  iterate _players
  where_as x: @viewer.search =? x.searchable
  order by name asc;

The left operand is the search term and the right operand is the field to search in. Typically combined with view variables for client-controlled search.

Field Projection and Join#

You can project a single field from query results and join them into a string:

record Player {
  public int id;
  public string name;
  public int score;
}

table<Player> _players;

// Get all player names as a comma-separated string
public formula all_names = (iterate _players).name.join(", ");

// Project scores into a list
public formula all_scores = (iterate _players).score;

Reduce (Grouping)#

The reduce operator groups records by a field and applies a function to each group:

record Submission {
  public int id;
  public string category;
  public int votes;
}

table<Submission> _submissions;

// Group submissions by category, collecting items into groups
public auto grouped =
  iterate _submissions
  reduce on category via @lambda x: x;

// Group and count
public auto categoryCounts =
  iterate _submissions
  reduce on category via @lambda x: x.size();

The reduce expression:

1. Groups records by the specified field (category)
2. Sends each group (as a list) to the lambda function
3. Returns a map from field values to lambda results

Complete Example: Card Game#

Here's a complete example showing tables and queries working together in a card game. This is the kind of thing Adama was born to do.

enum Suit { Clubs, Hearts, Spades, Diamonds }
enum Rank { Two:2, Three:3, Four:4, Five:5, Six:6, Seven:7,
            Eight:8, Nine:9, Ten:10, Jack:11, Queen:12, King:13, Ace:14 }

record Card {
  public int id;
  public Suit suit;
  public Rank rank;
  private principal owner;
  private int ordering;

  method reset() {
    ordering = Random.genInt();
    owner = @no_one;
  }
}

table<Card> _deck;

// Each player sees only their own cards
bubble myHand =
  iterate _deck
  where owner == @who
  order by suit asc, rank asc;

// Deal cards to a player
procedure dealCards(principal player, int count) {
  (iterate _deck
    where owner == @no_one
    order by ordering asc
    limit count
  ).owner = player;
}

// Shuffle the deck
procedure shuffleDeck() {
  (iterate _deck).reset();
}

// Find who has a specific card
procedure findCardOwner(Suit s, Rank r) -> maybe<principal> {
  if ((iterate _deck where suit == s && rank == r)[0] as card) {
    return card.owner;
  }
  return @no_one;
}