Remix Studio User Guide
QB2 is a set of significant changes compared to QB1, at both the builder and the DB Engine levels. In particular:
Some QB 1.0 nodes can be migrated to QB 2.0 - when this is possible you will see a button to this effect. A migration can be reverted until you leave the module.
In the short run you can continue to create QB 1.0 nodes from the “New” menu - this is primarily for people that experience issues with 2.0.
Support for 1.0 will not be removed any time soon.
Every Remix runtime has a set of one or more associated databases, which can be accessed from within Mix, and for server environments, over HTTP. This page discusses database queries generally, with more detail available in several related pages:
db module to interact with databases in Mixquery module to construct query ASTAt a high level, a Remix database consists of a linear record store and an associated index. Records are untyped and can include any "serializable" Mix data (the Mix data type). This includes the usual JSON types, as well as database references and case types like dates and times from the calendar module. The indexing is dynamic and automatic: there is no user control over which fields are indexed. Every field that can be indexed is (more on this below). In addition to selecting specific records (a "filter"), a query can include functions that modify the record stream ("projections"). For example, a typical projection selects out a few fields and provides default values.
A parsed query passes through several layers of execution:
In Mix, the final result is a stream of (potentially modified) database records (type map(data)). Over HTTP, the result is an array of encoded records; by default the encoding is JSON, but MessagePack is also supported.
Each Remix runtime includes a DB engine, which implements query operations in terms of a query AST. Either in Mix or over HTTP, you can specify this AST directly as JSON. Registered queries in the agent server are specified only with the AST. Mix and HTTP queries both also support a string syntax that can be parsed to AST. For simple queries this is often simplest, but it adds unnecessary parsing overhead, and should never be used with user-provided input, to avoid quoting/injection issues. Finally, in Mix there is also a "query rewriting" feature, where the compiler attempts to turn normal Mix functions into query elements. With this approach you can write queries in Mix almost identically to how they would be expressed as filters/projections of plain data.
Here is a simple example, executed several different ways: querying for records with entity of person, extracting their first_name and last_name fields, and adding a name field that joins them. The corresponding query string is "entity" == "person" | { first_name, last_name, name: first_name + " " + last_name}.
In Mix, you can execute this query and get the results as an array like this:
> db.query(db.mainDB, "\\\\"entity\\\\" == \\\\"person\\\\" | { first_name, last_name, name: first_name + \\\\" \\\\" + last_name }") |> db.toArray
$value = [ { first_name: "Vijay", last_name: "Chakravarthy", name: "Vijay Chakravarthy" }, { first_name: "Didier", last_name: "Prophete", name: "Didier Prophete" } ]
The first argument to db.query is the database to query; see th relevant section of the db module documentation for more. db.toArray takes an unevaluated query, executes it, and materializes the full result array. db.toStream returns a lazily-evaluated stream that can be passed to a stream consumer.
You can also take the same query and execute it over HTTP:
$> curl -H "Authorization: Bearer $AMP_TOKEN" <https://example.remixlabs.com/a/myapp/query> --url-query 'query="entity" == "person" | { first_name, last_name, name: first_name + " " + last_name }'
[{"first_name":"Vijay","last_name":"Chakravarthy","name":"Vijay Chakravarthy"},{"first_name":"Didier","last_name":"Prophete","name":"Didier Prophete"}]
(Passing the raw output to jq is often helpful.)
The AST corresponding to this query is:
[
["comparison", "==", "entity", "person"],
[
"object",
["assignMember", "first_name", ["token", "first_name"]],
["assignMember", "last_name", ["token", "last_name"]],
["assignMember", "name", ["calculation", ["arithmetic", "+", ["calculation", ["arithmetic", "+", ["token", "first_name"], " "]], ["token", "last_name"]]]]
]
]
The literal value of the AST can be passed in Mix like:
db.head() |> db.processPipeline([["comparison", "==", "entity", "person"], ["object", ...]]) |> db.toArray
Or you can construct it with methods from the the query module:
db.head() |> db.processPipeline([query.eq("entity", "person"), query.object([query.assignMember("first_name", query.objectMember(null, "first_name")), query.assignMember("first_name", query.objectMember(null, "first_name")), ["calculation", ...]])) |> db.toArray
(Note that the calculation AST is not implemented in query yet.)
Finally, there is another way to express this query in Mix: via the "rewriting" feature. An unevaluated query object can have additional filters or projections applied to it via db.filter and db.map:
> db.query(db.mainDB, "") |> db.filter(r -> r.entity == "person") |> db.map(r -> {first_name: r.first_name, last_name: r.last_name, name: r.first_name + " " + r.last_name}) |> db.toArray
$value = [ { first_name: "Vijay", last_name: "Chakravarthy", name: "Vijay Chakravarthy" }, { first_name: "Didier", last_name: "Prophete", name: "Didier Prophete" } ]
This is described as "rewriting" because the Mix compiler takes Mix code and rewrites it as DB query AST -- the runtime code that is executed is the same as the first example.
A powerful extension of the rewriter is being able to include arbitrary Mix expressions that are not supported by the index or DB engine, but can be executed as Mix code. In this case, the DB engine does as much of the query as it can, then streams the results into the Mix VM, where further filtering/mapping can be done. To take advantage of this, use db.filterWithFallback and db.mapWithFallback; the non-fallback versions will give a compilation error if an expression is passed that cannot be handled by the DB engine.
The HTTP query API is fairly straightforward, but the Mix experience has more subtleties. Whereas an HTTP query is tied to a particular database by the URL, a Mix query can access multiple databases, as described in the kinds of databases section of the db wiki page. Mix execution takes place in the context of a particular "app", which corresponds to the database the executable was loaded from, and that saves and queries default to using. db.query(null, query_string) queries this database (also called the "main db", db.mainDB and null are both aliases for it). You can also access other apps' databases (with permission), local or global in-memory database, or remote databases over HTTP: see the db wiki page for details.
When constructing a Mix query, the basic pattern is source |> processor |> processor |> output_fn. A "source" is a function that returns a db.query value, a processor adds another filter or transformation to the query, and finally an output function causes the final query to be evaluated.
Sources are:
db.head(dbConfig): an empty query returning all (current) records. db.head() is equivalent to db.head(null), i.e., the main DBdb.all(dbConfig): same as db.head but also includes old versionsdb.query(dbConfig, queryStr): start with an arbitrary string query; "" is an empty query matching everything, so db.query(null, "") is equivalent to db.head()There are many kinds of processors, which can be divided by whether or not they can be processed by the database engine. Processors that can't be handled at the database layer are executed in Mix. Whether certain expressions can be processed by the database layer may not be known until runtime, in which case both versions can be provided.
The most direct way to express processors is by passing an AST element to db.process:
db.head() |> db.process(query.eq("entity", "person")) |> db.first
Multiple elements can be added this way:
db.head() |> db.process(query.eq("entity", "person")) |> db.process(query.eq("first_name", "Vijay")) |> db.process(query.object([query.assignMember("first", query.objectMember(null, "first_name"))])) |> db.first
Arbitrary Mix post-processing can be added:
db.head() |> db.process(query.eq("entity", "person")) |> db.postprocess(stream.map(r -> {first_name: string.lowercase(r.first_name)})) |> db.first
Note that once a post-processing step has been added, only post-processing elements are allowed, so that ordering can be maintained.
If it is uncertain whether an AST expression can be handled by the DB, e.g., comparison to a value coming from user input or a binding of type data, there is db.processWithFallback:
db.head() |> db.processWithFallback(query.eq("first_name", some_connected_value), stream.filter(r -> r.first_name === some_connected_value)) |> db.processWithFallback(some_projection) |> db.first
Note that only post-processing, or processWithFallback, is allowed once a fallback path has been provided.
Writing out AST can be unwieldy, so for user-written queries, a "query rewriting" feature of the compiler allows the direct application of Mix functions in the query pipeline:
db.head() |> db.filter(r -> r.entity == "person") |> db.map(r -> {first_name: r.first_name, last_name: r.last_name, name: r.first_name + " " + r.last_name}) |> db.toArray
In addition to db.filter and db.map, there are db.filterWithFallback and db.mapWithFallback. Unlike db.processWithFallback, they still only take a single Mix-function argument, with the compiler generating both paths.
db.head() |> db.filterWithFallback(r -> r.entity == some_connected_value) |> db.mapWithFallback(r -> {first_name: r.first_name, last_name: r.last_name, name: r.first_name + " " + r.last_name}) |> db.toArray
Finally, note that you can simplify a bit more because filter and map also accept "record expressions" where the function is implied and field access is represented by a leading period:
db.head() |> db.filter(.entity == "person") |> db.map({first_name: .first_name, last_name: .last_name, name: .first_name + " " + .last_name}) |> db.toArray
The simplest thing to do with a query result is turn it into an array:
db.head() |> db.filter(r -> r.entity == "person") |> db.toArray
But you can also convert to a stream with db.toStream, which lazily materializes individual results. This can give better performance if the next step in processing may not consume all the results, or if it only processes them into an aggregated result so that streaming one at a time reduces memory consumption.
db.first returns just the first result (if there are no result, it raises a runtime error), and db.length gives the overall length of the result stream.
Query processing steps can be performed in three ways, in increasing order of performance cost:
db.postprocess or filter/map with fallback)==/!=/</<=/>/>=) on strings, refs, booleans, and null, plus certain standard library types with indexing defined, like the types in the calendar module (specifically, case types whose argument is a map with an _rmx_index field)string.hasPrefix("foo", .field))string.contains("text", .field))in/all in array comparisons for indexed elementsundefined).friend.neighbor.name == "fred" (this works for either nested maps in a single record, or for references across multiple records)Note that all the string operations are case-sensitive.
not and or with a non-indexable part (ands can be split into an indexable part and a non-indexable part)Everything else.
The Remix database includes the full history of every record. There are two kinds of records that don't normally show up in queries: old versions (which are "superseded" by newer ones with the same _rmx_id), and "tombstones" marking the deletion of a particular _rmx_id. A tombstone record isn't superseded (it's the current version of that record), but it only includes metadata fields like _rmx_id and _rmx_last_modified_at.
In an HTTP query, the query param includeHistory=true includes old versions and includeDeleted=true includes tombstones. In Mix, you can include old versions with the processor db.includeSuperseded(true) or by using db.all() as the source of the query, and you can include tombstones with db.includeDeleted(true).