Database management

This section explains how to use Cypher to manage databases in Neo4j DBMS: creating, modifying, deleting, starting, and stopping individual databases within a single server.

Neo4j supports the management of multiple databases within the same DBMS. The metadata for these databases, including the associated security model, is maintained in a special database called the system database. All multi-database administrative commands must be run against the system database. These administrative commands are automatically routed to the system database when connected to the DBMS over Bolt.

The syntax of the database management commands is as follows:

The syntax descriptions use the style from access control.

Table 1. Database management command syntax
Command	Syntax
`SHOW DATABASE`	`SHOW { DATABASE[S] name \| DATABASE[S] \| DEFAULT DATABASE \| HOME DATABASE } [WHERE expression]` `SHOW { DATABASE[S] name \| DATABASE[S] \| DEFAULT DATABASE \| HOME DATABASE } YIELD { * \| field[, ...] } [ORDER BY field[, ...]] [SKIP n] [LIMIT n] [WHERE expression] [RETURN field[, ...] [ORDER BY field[, ...]] [SKIP n] [LIMIT n]]`
`CREATE DATABASE`	`CREATE DATABASE name [IF NOT EXISTS] [TOPOLOGY n PRIMAR{Y\|IES} [m SECONDAR{Y\|IES}]] [OPTIONS "{" option: value[, ...] "}"] [WAIT [n [SEC[OND[S]]]]\|NOWAIT]` `CREATE OR REPLACE DATABASE name [TOPOLOGY n PRIMAR{Y\|IES} [m SECONDAR{Y\|IES}]] [OPTIONS "{" option: value[, ...] "}"] [WAIT [n [SEC[OND[S]]]]\|NOWAIT]`
`CREATE COMPOSITE DATABASE`	`CREATE COMPOSITE DATABASE name [IF NOT EXISTS] [OPTIONS "{" "}"] [WAIT [n [SEC[OND[S]]]]\|NOWAIT]` `CREATE OR REPLACE COMPOSITE DATABASE name [OPTIONS "{" "}"] [WAIT [n [SEC[OND[S]]]]\|NOWAIT]`
`ALTER DATABASE`	`ALTER DATABASE name [IF EXISTS] { SET ACCESS {READ ONLY \| READ WRITE} \| SET TOPOLOGY n PRIMAR{Y\|IES} [m SECONDAR{Y\|IES}] }`
`STOP DATABASE`	`STOP DATABASE name [WAIT [n [SEC[OND[S]]]]\|NOWAIT]`
`START DATABASE`	`START DATABASE name [WAIT [n [SEC[OND[S]]]]\|NOWAIT]`
`DROP DATABASE`	`DROP [COMPOSITE] DATABASE name [IF EXISTS] [{DUMP\|DESTROY} [DATA]] [WAIT [n [SEC[OND[S]]]]\|NOWAIT]`

Listing databases

There are four different commands for listing databases:

Listing all databases.
Listing a particular database.
Listing the default database.
Listing the home database.

These commands return the following columns:

Column Description Type

name

The name of the database. Default Output

STRING

type

The type of the database: system, standard, or composite. Default Output

STRING

aliases

The names of any aliases the database may have. Default Output

LIST OF STRING

access

The database access mode, either read-write or read-only. Default Output

A database may be described as read-only when using ALTER DATABASE … SET ACCESS READ ONLY.

STRING

databaseID

The database unique ID.

STRING

serverID

The server instance ID.

STRING

address

Instance address in a clustered DBMS. The default for a standalone database is neo4j://localhost:7687. Default Output

STRING

role

The current role of the database (primary, secondary, unknown). Default Output

STRING

writer

true for the database node that accepts writes (this node is the leader for this database in a cluster or this is a standalone instance). Default Output

BOOLEAN

requestedStatus

The expected status of the database. Default Output

STRING

currentStatus

The actual status of the database. Default Output

STRING

statusMessage

A message explaining the status of the database, often explaining why it is not in the correct state. Default Output

STRING

default

Show if this is the default database for the DBMS. Default Output

Not returned by SHOW HOME DATABASE or SHOW DEFAULT DATABASE.

BOOLEAN

home

Shown if this is the home database for the current user. Default Output

Not returned by SHOW HOME DATABASE or SHOW DEFAULT DATABASE.

BOOLEAN

currentPrimariesCount

Number of primaries for this database reported as running currently. It is the same as the number of rows where role=primary and name=this database.

INTEGER

currentSecondariesCount

Number of secondaries for this database reported as running currently. It is the same as the number of rows where role=secondary and name=this database.

INTEGER

requestedPrimariesCount

The requested number of primaries for this database. May be lower than current if the DBMS is currently reducing the number of copies of the database, or higher if it is currently increasing the number of copies.

INTEGER

requestedSecondariesCount

The requested number of secondaries for this database. May be lower than current if the DBMS is currently reducing the number of copies of the database, or higher if it is currently increasing the number of copies.

INTEGER

creationTime

The date and time at which the database was created.

DATETIME

lastStartTime

The date and time at which the database was last started.

DATETIME

lastStopTime

The date and time at which the database was last stopped.

DATETIME

store

Information about the storage engine and the store format.

The value is a string formatted as:

{storage engine}-{store format}-{major version}.{minor version}

STRING

lastCommittedTxn

The ID of the last transaction received.

INTEGER

replicationLag

Number of transactions the current database is behind compared to the database on the primary instance. The lag is expressed in negative integers. In standalone environments, the value is always 0.

INTEGER

constituents

The names of any constituents the database may have. Default Output

LIST OF STRING

Example 1. SHOW DATABASES

A summary of all available databases can be displayed using the command SHOW DATABASES.

Query

SHOW DATABASES

Table 3. Result
name	type	aliases	access	address	role	writer	requestedStatus	currentStatus	statusMessage	default	home	constituents
`"movies"`	`standard`	`["films","motion pictures"]`	`"read-write"`	`"localhost:7687"`	`"primary"`	`true`	`"online"`	`"online"`	`""`	`false`	`false`	`[]`
`"neo4j"`	`"standard"`	`[]`	`"read-write"`	`"localhost:7687"`	`"primary"`	`true`	`"online"`	`"online"`	`""`	`false`	`false`	`[]`
`"system"`	`"standard"`	`[]`	`"read-write"`	`"localhost:7687"`	`"primary"`	`true`	`"online"`	`"online"`	`""`	`false`	`false`	`[]`
Rows: 3

The results of this command are filtered according to the ACCESS privileges of the user. However, some privileges enable users to see additional databases regardless of their ACCESS privileges:

Users with CREATE/DROP/ALTER DATABASE or SET DATABASE ACCESS privileges can see all standard databases.
Users with CREATE/DROP COMPOSITE DATABASE or COMPOSITE DATABASE MANAGEMENT privileges can see all composite databases.
Users with DATABASE MANAGEMENT privilege can see all databases.

If a user has not been granted ACCESS privilege to any databases nor any of the above special cases, the command can still be executed but will only return the system database, which is always visible.

Databases hosted on servers that are offline are also returned by the SHOW DATABASES command. For such databases, the address column displays NULL, the currentStatus column displays unknown, and the statusMessage displays Server is unavailable.

Example 2. SHOW DATABASES

In this example, the detailed information for a particular database can be displayed using the command SHOW DATABASE name YIELD *. When a YIELD clause is provided, the full set of columns is returned.

Query

SHOW DATABASE movies YIELD *

Table 4. Result
name	aliases	access	databaseID	serverID	address	...
`"movies"`	`["films","motion pictures"]`	`"read-write"`	`"367221F9021C00CEBFCA25C8E2101F1DCF45C7DB9BF7D7A0949B87745E760EDD"`	`"adc0a7bc-d9a6-4cc8-b394-91635fbb1137"`	`"localhost:7687"`	`...`
Rows: 1

Example 3. SHOW DATABASES

The number of databases can be seen using a count() aggregation with YIELD and RETURN.

Query

SHOW DATABASES YIELD *
RETURN count(*) AS count

Table 5. Result
count
`3`
Rows: 1

Example 4. SHOW DEFAULT DATABASE

The default database can be seen using the command SHOW DEFAULT DATABASE.

Query

SHOW DEFAULT DATABASE

Table 6. Result
name	type	aliases	access	address	role	writer	requestedStatus	currentStatus	statusMessage	constituents
`"neo4j"`	`"standard"`	`[]`	`"read-write"`	`"localhost:7687"`	`"primary"`	`true`	`"online"`	`"online"`	`""`	`[]`
Rows: 1

Example 5. SHOW HOME DATABASE

The home database for the current user can be seen using the command SHOW HOME DATABASE.

Query

SHOW HOME DATABASE

Table 7. Result
name	type	aliases	access	address	role	writer	requestedStatus	currentStatus	statusMessage	constituents
`"neo4j"`	`"standard"`	`[]`	`"read-write"`	`"localhost:7687"`	`"primary"`	`true`	`"online"`	`"online"`	`""`	`[]`
Rows: 1

Example 6. SHOW DATABASES

It is also possible to filter and sort the results by using YIELD, ORDER BY, and WHERE.

Query

SHOW DATABASES YIELD name, currentStatus, requestedStatus
ORDER BY currentStatus
WHERE name CONTAINS 'o'

In this example:

The number of columns returned has been reduced with the YIELD clause.
The order of the returned columns has been changed.
The results have been filtered to only show database names containing 'o'.
The results are ordered by the currentStatus column using ORDER BY.

It is also possible to use SKIP and LIMIT to paginate the results.

Table 8. Result
name	currentStatus	requestedStatus
`"movies"`	`"online"`	`"online"`
`"neo4j"`	`"online"`	`"online"`
Rows: 2

Note that for failed databases, the currentStatus and requestedStatus are different. This often implies an error, but does not always. For example, a database may take a while to transition from offline to online due to performing recovery. Or, during normal operation a database’s currentStatus may be transiently different from its requestedStatus due to a necessary automatic process, such as one Neo4j instance copying store files from another. The possible statuses are initial, online, offline, store copying and unknown.

For composite databases the constituents column is particularly interesting as it lists the aliases that make up the composite database:

Query

SHOW DATABASE library YIELD name, constituents

Table 9. Result
name	constituents
`"library"`	`["library.sci-fi","library.romance"]`
Rows: 1

Creating databases

Databases can be created using CREATE DATABASE.

Database names are subject to the standard Cypher restrictions on valid identifiers.

The following naming rules apply:

Database name length must be between 3 and 63 characters.
The first character must be an ASCII alphabetic character.
Subsequent characters can be ASCII alphabetic (mydatabase), numeric characters (mydatabase2), dots (main.db), and dashes (enclosed within backticks, e.g., CREATE DATABASE `main-db`). Using database names with dots without enclosing them in backticks is deprecated.
Names cannot end with dots or dashes.
Names that begin with an underscore or with the prefix system are reserved for internal use.

Having dots (.) in the database names is not recommended. This is due to the difficulty of determining if a dot is part of the database name or a delimiter for a database alias in a composite database.

Example 7. CREATE DATABASE

Query

CREATE DATABASE customers

Result

System updates: 1
Rows: 0

Example 8. SHOW DATABASES

When a database has been created, it will show up in the listing provided by the command SHOW DATABASES.

Query

SHOW DATABASES YIELD name

Table 10. Result
name
`"customers"`
`"library"`
`"movies"`
`"neo4j"`
`"romance"`
`"sci-fi"`
`"system"`
Rows: 7

Cluster topology

In a cluster environment, it may be desirable to control the number of servers used to host a database. The number of primary and secondary servers can be specified using the following command.

Query

CREATE DATABASE `topology-example` TOPOLOGY 1 PRIMARY 0 SECONDARIES

For more details on primary and secondary server roles, see Cluster overview.

TOPOLOGY is only available for standard databases and not composite databases. Composite databases are always available on all servers.

Creating composite databases

Composite databases do not contain data, but they reference to other databases that can be queried together through their constituent aliases. For more information about composite databases, see Operations Manual → Composite database introduction.

Composite databases can be created using CREATE COMPOSITE DATABASE.

Composite database names are subject to the same rules as standard databases. One difference is however that the deprecated syntax using dots without enclosing the name in backticks is not available. Both dots and dashes need to be enclosed within backticks when using composite databases.

Having dots (.) in the composite database names is not recommended. This is due to the difficulty of determining if a dot is part of the composite database name or a delimiter for a database alias in a composite database.

Query

CREATE COMPOSITE DATABASE inventory

0 rows, System updates: 1

Composite database names are subject to the same rules as standard databases. One difference is however that the deprecated syntax using dots without enclosing the name in backticks is not available. Both dots and dashes needs to be enclosed within backticks when using composite databases.

When a composite database has been created, it will show up in the listing provided by the command SHOW DATABASES.

Query

SHOW DATABASES YIELD name, type, access, role, writer, constituents

Table 11. Result
name	type	access	role	writer	constituents
`"customers"`	`"standard"`	`"read-write"`	`"primary"`	`true`	`[]`
`"inventory"`	`"composite"`	`"read-only"`	`<null>`	`false`	`[]`
`"library"`	`"composite"`	`"read-only"`	`<null>`	`false`	`["library.sci-fi","library.romance"]`
`"movies"`	`"standard"`	`"read-write"`	`"primary"`	`true`	`[]`
`"neo4j"`	`"standard"`	`"read-write"`	`"primary"`	`true`	`[]`
`"sci-fi-books"`	`"standard"`	`"read-write"`	`"primary"`	`true`	`[]`
`"system"`	`"system"`	`"read-write"`	`"primary"`	`true`	`[]`
`"topology-example"`	`"standard"`	`"read-write"`	`"primary"`	`true`	`[]`
Rows: 8

In order to create database aliases in the composite database, give the composite database as namespace for the alias. For information about creating aliases in composite databases, see here.

Handling Existing Databases

These commands are optionally idempotent, with the default behavior to fail with an error if the database already exists. Appending IF NOT EXISTS to the command ensures that no error is returned and nothing happens should the database already exist. Adding OR REPLACE to the command will result in any existing database being deleted and a new one created.

These behavior flags apply to both standard and composite databases (e.g. a composite database may replace a standard one or another composite.)

Example 9. CREATE DATABASE

Query

CREATE COMPOSITE DATABASE customers IF NOT EXISTS

Example 10. CREATE OR REPLACE DATABASE

Query

CREATE OR REPLACE DATABASE customers

This is equivalent to running DROP DATABASE customers IF EXISTS followed by CREATE DATABASE customers.

The IF NOT EXISTS and OR REPLACE parts of these commands cannot be used together.

Options

The CREATE DATABASE command can have a map of options, e.g. OPTIONS {key: 'value'}.

There are no available OPTIONS values for composite databases.

Key Value Description

Key	Value	Description
`existingData`	`use`	Controls how the system handles existing data on disk when creating the database. Currently this is only supported with `existingDataSeedInstance` and must be set to `use` which indicates the existing data files should be used for the new database.
`existingDataSeedInstance`	instance ID of the cluster node	Defines which instance is used for seeding the data of the created database. The instance id can be taken from the id column of the `dbms.cluster.overview()` procedure. Can only be used in clusters.
`seedURI`	URI to a backup or a dump from an existing database.	Defines an identical seed from an external source which will be used to seed all servers.
`seedConfig`	comma separated list of configuration values.	Defines additional configuration specified by comma separated `name=value` pairs that might be required by certain seed providers.
`seedCredentials`	credentials	Defines credentials that needs to be passed into certain seed providers.

existingData

use

Controls how the system handles existing data on disk when creating the database. Currently this is only supported with existingDataSeedInstance and must be set to use which indicates the existing data files should be used for the new database.

existingDataSeedInstance

instance ID of the cluster node

Defines which instance is used for seeding the data of the created database. The instance id can be taken from the id column of the dbms.cluster.overview() procedure. Can only be used in clusters.

seedURI

URI to a backup or a dump from an existing database.

Defines an identical seed from an external source which will be used to seed all servers.

seedConfig

comma separated list of configuration values.

Defines additional configuration specified by comma separated name=value pairs that might be required by certain seed providers.

seedCredentials

credentials

Defines credentials that needs to be passed into certain seed providers.

The existingData, existingDataSeedInstance, seedURI, seedConfig and seedCredentials options cannot be combined with the OR REPLACE part of this command. For details about the use of these seeding options, see Operations Manual → Seed a cluster.

Altering databases

Standard databases can be modified using the command ALTER DATABASE.

Access

By default, a database has read-write access mode on creation. The database can be limited to read-only mode on creation using the configuration parameters dbms.databases.default_to_read_only, dbms.databases.read_only, and dbms.database.writable. For details, see Configuration parameters.

A database that was created with read-write access mode can be changed to read-only. To change it to read-only, you can use the ALTER DATABASE command with the sub-clause SET ACCESS READ ONLY. Subsequently, the database access mode can be switched back to read-write using the sub-clause SET ACCESS READ WRITE. Altering the database access mode is allowed at all times, whether a database is online or offline.

If conflicting modes are set by the ALTER DATABASE command and the configuration parameters, i.e. one says read-write and the other read-only, the database will be read-only and prevent write queries.

Modifying access mode is only available to standard databases and not composite databases.

Example 11. ALTER DATABASE

Query

ALTER DATABASE customers SET ACCESS READ ONLY

Result

System updates: 1
Rows: 0

Example 12. SHOW DATABASES

The database access mode can be seen in the access output column of the command SHOW DATABASES.

Query

SHOW DATABASES yield name, access

Table 12. Result
name	access
`"customers"`	`"read-only"`
`"inventory"`	`"read-only"`
`"library"`	`"read-only"`
`"movies"`	`"read-write"`
`"neo4j"`	`"read-write"`
`"romance"`	`"read-write"`
`"sci-fi"`	`"read-write"`
`"system"`	`"read-write"`
`"topology-example"`	`"read-write"`
Rows: 9

Example 13. ALTER DATABASE

ALTER DATABASE commands are optionally idempotent, with the default behavior to fail with an error if the database does not exist. Appending IF EXISTS to the command ensures that no error is returned and nothing happens should the database not exist.

Query

ALTER DATABASE nonExisting IF EXISTS
SET ACCESS READ WRITE

Topology

In a cluster environment, it may be desirable to change the number of servers used to host a database. The number of primary and secondary servers can be specified using the following command:

Example 14. ALTER DATABASE

Query

ALTER DATABASE `topology-example`
SET TOPOLOGY 3 PRIMARY 0 SECONDARIES

It is not possible to automatically transition from a topology with multiple primary hosts to a topology with a single primary host, but it is possible to increase the number of primaries from one to more. See the Operations Manual → Alter topology for more information.

Example 15. SHOW DATABASE

Query

SHOW DATABASES yield name, currentPrimariesCount, currentSecondariesCount, requestedPrimariesCount, requestedSecondariesCount

For more details on primary and secondary server roles, see Operations Manual → Clustering overview.

Modifying database topology is only available to standard databases and not composite databases.

Query

ALTER DATABASE nonExisting IF EXISTS SET TOPOLOGY 1 PRIMARY 0 SECONDARY

0 rows

Stopping databases

Databases can be stopped using the command STOP DATABASE.

Example 16. STOP DATABASE

Query

STOP DATABASE customers

Result

System updates: 1
Rows: 0

Both standard databases and composite databases can be stopped using this command.

Example 17. SHOW DATABASE

The status of the stopped database can be seen using the command SHOW DATABASE name.

Query

SHOW DATABASE customers YIELD name, requestedStatus, currentStatus

Table 13. Result
name	requestedStatus	currentStatus
`"customers"`	`"offline"`	`"offline"`
Rows: 1

Starting databases

Databases can be started using the command START DATABASE.

Example 18. START DATABASE

Query

START DATABASE customers

Result

System updates: 1
Rows: 0

Both standard databases and composite databases can be stopped using this command.

Example 19. SHOW DATABASE

The status of the started database can be seen using the command SHOW DATABASE name.

Query

SHOW DATABASE customers YIELD name, requestedStatus, currentStatus

Table 14. Result
name	requestedStatus	currentStatus
`"customers"`	`"online"`	`"online"`
Rows: 1

Deleting databases

Standard and composite databases can be deleted by using the command DROP DATABASE.

Example 20. DROP DATABASE

Query

DROP DATABASE customers

Result

System updates: 1
Rows: 0

It is also possible to ensure that only composite databases are dropped. A DROP COMPOSITE request would then fail if the targeted database is a standard database.

Example 21. SHOW DATABASES

When a database has been deleted, it will no longer show up in the listing provided by the command SHOW DATABASES.

Query

SHOW DATABASES YIELD name

Table 15. Result
name
`"inventory"`
`"library"`
`"movies"`
`"neo4j"`
`"romance"`
`"sci-fi"`
`"system"`
`"topology-example"`

Example 22. DROP DATABASE

This command is optionally idempotent, with the default behavior to fail with an error if the database does not exist. Appending IF EXISTS to the command ensures that no error is returned and nothing happens should the database not exist. It will always return an error, if there is an existing alias that targets the database. In that case, the alias needs to be dropped before dropping the database.

Query

DROP DATABASE customers IF EXISTS

The DROP DATABASE command will remove a database entirely.

Example 23. DROP DATABASE

You can request that a dump of the store files is produced first, and stored in the path configured using the dbms.directories.dumps.root setting (by default <neo4j-home>/data/dumps). This can be achieved by appending DUMP DATA to the command (or DESTROY DATA to explicitly request the default behavior). These dumps are equivalent to those produced by neo4j-admin dump and can be similarly restored using neo4j-admin load.

Query

DROP DATABASE `topology-example` DUMP DATA

The options IF EXISTS and DUMP DATA/ DESTROY DATA can also be combined. An example could look like this:

Query

DROP DATABASE customers IF EXISTS DUMP DATA

It is also possible to ensure that only composite databases are dropped. A DROP COMPOSITE request would then fail if the targeted database is a standard database.

Example 24. DROP COMPOSITE DATABASE

Query

DROP COMPOSITE DATABASE inventory

0 rows, System updates: 1

To ensure the database to be dropped is standard and not composite, the user first needs to check the type column of SHOW DATABASES manually.

Wait options

Aside from SHOW DATABASES and ALTER DATABASE, all database management commands accept an optional WAIT/NOWAIT clause. The WAIT/NOWAIT clause allows you to specify a time limit in which the command must complete and return.

The options are:

WAIT n SECONDS - Return once completed or when the specified time limit of n seconds is up.
WAIT - Return once completed or when the default time limit of 300 seconds is up.
NOWAIT - Return immediately.

A command using a WAIT clause will automatically commit the current transaction when it executes successfully, as the command needs to run immediately for it to be possible to WAIT for it to complete. Any subsequent commands executed will therefore be performed in a new transaction. This is different to the usual transactional behavior, and for this reason it is recommended that these commands be run in their own transaction. The default behavior is NOWAIT, so if no clause is specified the transaction will behave normally and the action is performed in the background post-commit.

A command with a WAIT clause may be interrupted whilst it is waiting to complete. In this event the command will continue to execute in the background and will not be aborted.

Example 25. CREATE DATABASE

Query

CREATE DATABASE slow WAIT 5 SECONDS

Table 16. Result
address	state	message	success
`"localhost:7687"`	`"CaughtUp"`	`"caught up"`	`true`
Rows: 1

The success column provides an aggregate status of whether or not the command is considered successful and thus every row will have the same value. The intention of this column is to make it easy to determine, for example in a script, whether or not the command completed successfully without timing out.

A command with a WAIT clause may be interrupted whilst it is waiting to complete. In this event the command will continue to execute in the background and will not be aborted.

Was this page helpful?