Creating virtual views

A virtual view is a subset of a database and can be created through Custom Database Keys. When creating them you're able to who can access what in your database, with column-level and row-level granularity. This way, users with these keys will access just the database subset defined (virtual view).



Creating a virtual view via Control Panel

To define a virtual view you need to create a Custom Database Key where you'll be able to allow inserting and querying only over specific columns from a database (column-level granularity) and defining filters to be applied in all queries using this key (row-level granularity).

A custom database key can be created through the Control Panel during the Custom Database Keys step on a database creation or edition on the Databases section. Click on Create new Custom Key to configure it.

Creating a custom key during database creation

Now you should configure your Custom Database Key parameters to define the database view you want to create for the users who will be using this custom key.

Custom key creation wizard

Field Mandatory Description
Database Key Name Yes The name of your custom database key. Can be edited at any time.
Database Key Permission Level Yes Permission levels for this custom key:
  • Read: Allows querying and editing saved queries that use only the allowed columns, always applying the defined filters on the result.
  • Write: Allows inserting data only to the allowed columns.
  • Read & Write: Allows to insert and query data, as well as create, execute, update and delete saved queries that use only the allowed columns. Filters are applied automatically.
Database Key Description No The description of the custom key that you'll create. Up to 1,000 characters.
Columns whitelist No Restrict any operation done with this key to the selected columns. If this field is empty, any column of the database will be accessible to this key.
IP Addresses No Restricts any operation done with this key to defined IPs. If this field is empty, any IP addresses will be able to make operations with this key.
Filter No Defines a filter for each dimension you have that will be applied to all queries executed using this key.

After that you'll have a Custom Database Key created that can be retrieved on the Databases section. Now, people with this key will execute Read or Write operations (according to the selected key permission level) just on the allowed columns and will just see the data filtered by the defined filter.
These users now have a virtual view of the database, just writing and reading the results that they are allowed to.


Creating a virtual view via API

To create a custom database key using API, you'll need to send a POST request to the /database/custom_key API Endpoint, like the following example:

curl -X POST https://api.slicingdice.com/v1/database/custom_key \
	-H 'Authorization: TEAM_API_KEY' \
	-H 'Content-Type: application/json' \
	-d '{
		  "database-name":"UsersDB",
		  "custom-database-key":{
			"key-name":"financial-key",
			"key-type":"read-only",
			"description":"Key to access just the financial information.",
			"columns-whitelist":{
			  "stores":[
				"address",
				"sales",
				"country"
			  ]
			},
			"filter":{
			  "stores":[{
				"country":{
				  "equals":"USA"
				}
			  }]
			},
			"addresses-whitelist":[
			  "182.504.12.123",
			]
		  }
		}'

You can check details and possible configuration about each parameters by accessing the custom database key creation API reference.


Filters

The Custom Database Keys filters are responsible to define the row-level granularity in your view. So you basically create a filter that will be applied to any query that executes using this key. When a query is executed the filter will be applied over your result to guarantee that only the allowed data is shown.

When creating a Custom Key through API the filter can be defined using the normal JSON query syntax that you can found in the API Reference.
When creating the Custom Key through Control Panel you should use the interactive feature that helps you build it. The allowed query operations are:

Equals Not Equals Between Range
Greater than Greater than or equal Less than Less than or equal
Starts with Ends with Minfreq