Cubes

Parameters

title

Use title to change the display name of the cube. By default, Cube.js will humanize the cube's name, so for instance, UsersOrder would become Users Orders. If default humanizing doesn't work in your case, please use the title parameter. It is highly recommended to give human readable names to your cubes. It will help everyone on a team better understand the data structure and will help maintain a consistent set of definitions across an organization.

cube(`Orders`, {
  sql: `SELECT * FROM orders`,

  title: `Product Orders`,
});

description

Use a description in your cubes to allow your team to better understand what this cube is about. It is a very simple and yet useful tool that gives a hint to everyone and makes sure data is interpreted correctly by users.

cube(`Orders`, {
  sql: `SELECT * FROM orders`,

  title: `Product Orders`,
  description: `All orders related information`,
});

extends

You can extend cubes in order to reuse all declared members of a cube. In the example below, ExtendedOrderFacts will reuse the sql and count measures from OrderFacts:

cube(`OrderFacts`, {
  sql: `SELECT * FROM orders`

  measures: {
    count: {
      type: `count`,
      sql: `id`
    }
  }
});

cube(`ExtendedOrderFacts`, {
  extends: OrderFacts,

  measures: {
    doubleCount: {
      type: `number`,
      sql: `${count} * 2`
    }
  }
});

You can also omit the cube name while defining it. This way Cube.js doesn't register this cube globally but instead it returns reference to it which you can use while combining cubes. It makes sense to use it for dynamic schema generation and reusing with extends. Previous example without defining OrderFacts cube globally:

const OrderFacts = cube({
  sql: `SELECT * FROM orders`

  measures: {
    count: {
      type: `count`,
      sql: `id`
    }
  }
});

cube(`ExtendedOrderFacts`, {
  extends: OrderFacts,

  measures: {
    doubleCount: {
      type: `number`,
      sql: `${count} * 2`
    }
  }
});

refreshKey

Cube.js caching layer uses refreshKey queries to get the current version of content for a specific cube. If a query result changes, Cube.js will invalidate all queries that rely on that cube. If the refreshKey is not set, Cube.js will use the default strategy:

1. Check used pre-aggregations for query and use pre-aggregations refreshKey, if none pre-aggregations are used…
2. Check the max of time dimensions with updated in the name, if none exist…
3. Check the max of any existing time dimension, if none exist…
4. Check the row count for this cube.

The result of the default refreshKey query itself is cached for 10 seconds for RDBMS backends and for 2 minutes for big data backends by default.

Refresh key of a query is a concatenation of all cubes refresh keys involved in query. For rollup queries pre-aggregation table name is used as a refresh key.

You can use an existing timestamp from your tables. Make sure to select max timestamp in that case.

cube(`OrderFacts`, {
  sql: `SELECT * FROM orders`,

  refreshKey: {
    sql: `SELECT MAX(created_at) FROM orders`
  }
});

You can use interval based refreshKey. For example:

cube(`OrderFacts`, {
  sql: `SELECT * FROM orders`,

  refreshKey: {
    every: `1 hour`
  }
});

Available interval granularities are: second, minute, hour, day and week. Such refreshKey is just a syntactic sugar over refreshKey SQL. It's guaranteed that refreshKey change it's value at least once during every interval. It will be converted to appropriate SQL select which value will change over time based on interval value. Values of interval based refreshKey are tried to be checked ten times within defined interval but not more than once per 1 second and not less than once per 5 minute. For example if interval is 10 minute it's refreshKeyRenewalThreshold will be 60 seconds and generated refreshKey SQL (Postgres) would be:

SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) / 600)

For 5 second interval refreshKeyRenewalThreshold will be just 1 second and SQL will be

SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) / 5)

dataSource

Each cube in schema can have it's own dataSource name to support scenarios where data should be fetched from multiple databases. Value of dataSource parameter will be passed to dbType and driverFactory functions as part of context param. By default each cube has a default value for it's dataSource. To override it you can use:

cube(`OrderFacts`, {
  sql: `SELECT * FROM orders`,
  
  dataSource: `prod_db`
});

Context Variables

User Context

USER_CONTEXT is a user security object that is passed by the Cube.js Client.

User context is suitable for the row level security implementation. For example, if you have an orders table that contains an email field you can restrict all queries to render results that belong only to the current user as follows:

cube(`Orders`, {
  sql: `SELECT * FROM orders WHERE ${USER_CONTEXT.email.filter('email')}`,

  dimensions: {
    date: {
      sql: `date`,
      type: `time`
    }
  }
});

To ensure filter value presents for all requests requiredFilter can be used:

cube(`Orders`, {
  sql: `SELECT * FROM orders WHERE ${USER_CONTEXT.email.requiredFilter('email')}`,

  dimensions: {
    date: {
      sql: `date`,
      type: `time`
    }
  }
});

Unsafe Value

NOTE: Use of this feature entails SQL injection security risk. Use it with caution.

You can access values of context variables directly in javascript in order to use it during your SQL generation. For example:

cube(`Orders`, {
  sql: `SELECT * FROM ${USER_CONTEXT.type.unsafeValue() === 'employee' ? 'employee' : 'public'}.orders`,

  dimensions: {
    date: {
      sql: `date`,
      type: `time`
    }
  }
});

SQL Utils

convertTz

In case you need to convert your timestamp to user request timezone in cube or member SQL you can use SQL_UTILS.convertTz() method. Note that Cube.js will automatically convert timezones for timeDimensions fields in queries. Dimensions that use SQL_UTILS.convertTz() should not be used as timeDimensions in queries. Doing so will apply the conversion multiple times and yield wrong results. In case the same database field needs to be queried in dimensions and timeDimensions, create dedicated dimensions in the cube definition for the respective use:

cube(`visitors`, {
  // ...

  dimensions: {
    createdAtConverted: { // do not use in timeDimensions query property
      type: 'time',
      sql: SQL_UTILS.convertTz(`created_at`)
    },
    createdAt: { // use in timeDimensions query property
      type: 'time',
      sql: `created_at`
    },
  }
})

Compile context

There's global COMPILE_CONTEXT that captured as RequestContext at the time of schema compilation. It contains authInfo and any other variables provided by extendContext.

NOTE: While authInfo defined in COMPILE_CONTEXT it doesn't change it's value for different users. It may change however for different tenants.

const { authInfo: { deploymentId } } = COMPILE_CONTEXT;

const schemaName = `user_${deploymentId}`;

cube(`Users`, {
  sql: `select * from ${schemaName}.users`,
  
  // ...
});