ALTER DATABASE

Available since: v3.0.0

The ALTER DATABASE statement can be used to modify a database. Without a name, it applies to the current database, which is why an IF EXISTS clause does not exist.

Statement syntax

ALTER DATABASE [ @name ]
    COMPACT |
    COMPATIBILITY 3 | 4

COMPACT

Performs storage compaction on the current database keyspace. To compact other resources, use ALTER SYSTEM to compact the entire datastore, ALTER NAMESPACE to compact the current namespace keyspace, or ALTER TABLE to compact a specific table keyspace.

The storage engine carries out the compaction. In SurrealDB 4.0, SurrealKV supports it: it writes recent changes to disk, merges the data files that hold the keys, and drops deleted data and old versions that no reader can see, which frees their space. The statement returns once the compaction is done, and reads and writes carry on meanwhile. Before 4.0, RocksDB was the only engine that supported compaction requests. The in-memory engine and TiKV do not support them, and return the following error:

'There was a problem with the key-value store: The storage layer does not support compaction requests.'

A successful compaction returns NONE.

ALTER DATABASE COMPACT;

NONE

COMPATIBILITY

Available since: v4.0.0

Sets the compatibility level of a database, 3 or 4. A database that a 3.x release created is at level 3 after an upgrade, and moves to level 4 with:

ALTER DATABASE COMPATIBILITY 4;

Raising a database to level 4 checks every field of its SCHEMAFULL tables in the background. INFO FOR TABLE then lists, under migrations.verification, each field with the number of records whose value does not fit its definition, and the fields whose values all fit under migrations.guaranteed:

INFO FOR TABLE person;

{
	events: {},
	fields: {
		age: 'DEFINE FIELD age ON person TYPE string PERMISSIONS FULL'
	},
	indexes: {},
	lives: {},
	migrations: {
		generation: 3,
		guaranteed: [],
		migrations: [],
		verification: [
			{
				failures: 2,
				field: 'age',
				finished: true
			}
		]
	},
	tables: {}
}

A field with failures is one that was changed at level 3 without its records being updated. A MIGRATE EXISTING ROWS clause on the field's definition converts those records:

DEFINE FIELD OVERWRITE age ON person TYPE string
    MIGRATE EXISTING ROWS AS age_to_string USING <string> $value FALLBACK '';

Raise the level only once every node runs 4.0. A datastore that still writes the 3.x record format, such as a TiKV cluster that has not been switched with SURREAL_FEATURE_LEVEL=4.0, refuses level 4.

See also

• •

  DEFINE DATABASE