Manage SQL views

In SQL, a view is a virtual table based on the result-set of an SQL statement. Views can provide advantages over tables, such as:

  • represent a subset of the data contained in a table (see also segments).

  • join and simplify many tables into a single virtual table.

  • act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data.

Forest Admin natively supports SQL views. If you have already implemented views, simply add the associated models to display them on your interface.

Creating the SQL View

To create a view, we use CREATE VIEW statement.

In the following example, we look for the user's email, the number of orders and the total amount spent.

CREATE VIEW customer_stats AS
count(orders.*) AS nb_orders,
sum(products.price) AS amount_spent,
FROM customers
JOIN orders ON = orders.customer_id
JOIN products ON orders.product_id =

Adding the model

To display the SQL view on your Forest Admin interface, you must add the associated Sequelize model in your application.

'use strict';
module.exports = (sequelize, DataTypes) => {
const CustomerStats = sequelize.define('customer_stats', {
'amount_spent': {
type: DataTypes.INTEGER,
'nb_orders': {
type: DataTypes.STRING,
'email': {
type: DataTypes.STRING,
}, {
tableName: 'customer_stats',
underscored: true,
schema: process.env.DATABASE_SCHEMA,
return CustomerStats;

You must restart your server to see the changes on your interface.

Managing the view

Once your SQL view is implemented, you'll be able to filter, search, export and change the order of your fields.

Most of the time SQL views are used as read-only. If this is the case, we recommend changing the CRUD options in your collection's settings.