# Views and materialized views

> Logical views with typed parameters, materialized views, parameterized templates and their materializations.

The engine has two kinds of named query:

- A **logical view** stores a canonical plan and no data. Each read runs the plan.
- A **materialized view** is maintained continuously into an engine-owned table, and is read by
  its name.

A parameterized materialized view is a template; `CREATE MATERIALIZATION` turns it into a
concrete, maintained instance.

## Logical views

```text
CREATE [OR REPLACE] VIEW <name> AS SELECT …
DROP VIEW [IF EXISTS] <name>
SHOW CREATE VIEW <name>
```

A view body may declare typed parameters inline with `${name:TYPE}`:

```sql
CREATE VIEW address_balances AS
SELECT token, amount, updated_at
FROM balances
WHERE chain_id = 1 AND address = ${address:STRING};
```

```sql
SHOW CREATE VIEW address_balances;
```

`CREATE OR REPLACE VIEW` installs the next definition revision (the reply carries
`"definition_revision":2`). `SHOW CREATE VIEW` returns one row with a `create_statement` column.

### Reading a view

A view is read like a table, with filters, grouping, ordering and limits of its own. Parameter
values travel in the request's `parameters` list.

```sql
SELECT token, amount
FROM address_balances
ORDER BY amount DESC
LIMIT 5;
```

`STREAM SELECT` over a view follows its result live. A view survives a restart and is read the
same way afterwards.

### Dropping a view

```sql
DROP VIEW address_balances;
```

With `IF EXISTS`, a missing view answers success with `"applied":false`.

## Materialized views

```text
CREATE [OR REPLACE] MATERIALIZED VIEW <name>[(${param:TYPE}, …)] AS SELECT …
DROP MATERIALIZED VIEW [IF EXISTS] <name>
SHOW CREATE MATERIALIZED VIEW <name>
```

```sql
CREATE MATERIALIZED VIEW token_volume AS
SELECT chain_id, token, SUM(amount) AS volume, COUNT(*) AS transfers
FROM transfers
GROUP BY chain_id, token;
```

A materialized view:

- is maintained continuously from its sources through [PSI](/docs/concepts/psi); inserts,
  updates and deletes on a source reach it, and it is exact after a restart;
- is read by its name, like a table (`SELECT * FROM token_volume`);
- is written only by the engine;
- keeps its rows in an engine-owned table.

### Replacing a materialized view

`CREATE OR REPLACE MATERIALIZED VIEW` installs a new revision (the reply carries
`"ddl_kind":8`), and the view's rows are rebuilt from the new definition.

## Parameterized templates and CREATE MATERIALIZATION

A materialized view with a parameter list is a template: it holds no data by itself.
`CREATE MATERIALIZATION` fixes its arguments and creates a maintained instance.

```text
CREATE MATERIALIZATION <instance> OF <template>(<param> => <literal>, …)
DROP MATERIALIZATION [IF EXISTS] <instance>
SHOW CREATE MATERIALIZATION <instance>
```

```sql
CREATE MATERIALIZED VIEW balances_by_chain(${chain:UINT16}) AS
SELECT address, token, amount
FROM balances
WHERE chain_id = ${chain};
```

```sql
CREATE MATERIALIZATION balances_chain_1 OF balances_by_chain(chain => 1);
```

The instance is read by its own name (`SELECT * FROM balances_chain_1`), follows source
changes and is maintained again after a restart.

## Dropping materialized views and instances

Instances of a template are dropped before the template:

```sql
DROP MATERIALIZATION balances_chain_1;
```

```sql
DROP MATERIALIZED VIEW balances_by_chain;
```

After the drop, a read of the name answers HTTP 404.

## Changing a definition

Use `CREATE OR REPLACE VIEW` and `CREATE OR REPLACE MATERIALIZED VIEW` to change a definition
in place.

---

Source: https://nyxdb.io/docs/sql/views/