Database management

This chapter explains how to use Cypher to manage Neo4j databases: creating, 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:

Table 1. Database management command syntax
Command Syntax

Show Database

SHOW { DATABASE name | DATABASES | DEFAULT DATABASE | HOME DATABASE }
[WHERE expression]
SHOW { DATABASE name | DATABASES | 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]
[OPTIONS "{" option: value[, ...] "}"]
[WAIT [n [SEC[OND[S]]]]|NOWAIT]
CREATE OR REPLACE DATABASE name [WAIT [n [SEC[OND[S]]]]|NOWAIT]

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 DATABASE name [IF EXISTS] [{DUMP|DESTROY} [DATA]] [WAIT [n [SEC[OND[S]]]]|NOWAIT]

1. Listing databases

There are three different commands for listing databases. Listing all databases, listing a particular database or listing the default database.

All available databases can be seen using the command SHOW DATABASES.

Query
SHOW DATABASES
Table 2. Result
name address role requestedStatus currentStatus error default home

"movies"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"neo4j"

"localhost:7687"

"standalone"

"online"

"online"

""

true

true

"northwind-graph"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"system"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

Rows: 4

Note that the results of this command are filtered according to the ACCESS privileges the user has. However, a user with CREATE/DROP DATABASE or DATABASE MANAGEMENT privileges can see all databases regardless of their ACCESS privileges. If a user has not been granted ACCESS privilege to any databases, the command can still be executed but will only return the system database, which is always visible.

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

Query
SHOW DATABASES YIELD * RETURN count(*) as count
Table 3. Result
count

4

Rows: 1

A particular database can be seen using the command SHOW DATABASE name.

Query
SHOW DATABASE system
Table 4. Result
name address role requestedStatus currentStatus error default home

"system"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

Rows: 1

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

Query
SHOW DEFAULT DATABASE
Table 5. Result
name address role requestedStatus currentStatus error

"neo4j"

"localhost:7687"

"standalone"

"online"

"online"

""

Rows: 1

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

Query
SHOW HOME DATABASE
Table 6. Result
name address role requestedStatus currentStatus error

"neo4j"

"localhost:7687"

"standalone"

"online"

"online"

""

Rows: 1

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 'e'

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 'e'.

  • 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 7. Result
name currentStatus requestedStatus

"neo4j"

"online"

"online"

"system"

"online"

"online"

"movies"

"online"

"online"

Rows: 3

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.

2. Creating databases

Databases can be created using CREATE DATABASE.

Query
CREATE DATABASE customers

0 rows, System updates: 1

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`).

  • Names cannot end with dots or dashes.

  • Names that begin with an underscore or with the prefix system are reserved for internal use.

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

Query
SHOW DATABASES
Table 8. Result
name address role requestedStatus currentStatus error default home

"customers"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"movies"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"neo4j"

"localhost:7687"

"standalone"

"online"

"online"

""

true

true

"northwind-graph"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"system"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

Rows: 5

2.1. Handling Existing Databases

This command is optionally idempotent, with the default behavior to throw an exception if the database already exists. Appending IF NOT EXISTS to the command will ensure that no exception is thrown 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.

Query
CREATE DATABASE customers IF NOT EXISTS
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 this command cannot be used together.

2.2. Options

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

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.

3. Stopping databases

Databases can be stopped using the command STOP DATABASE.

Query
STOP DATABASE customers

0 rows, System updates: 1

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

Query
SHOW DATABASE customers
Table 9. Result
name address role requestedStatus currentStatus error default home

"customers"

"localhost:7687"

"standalone"

"offline"

"offline"

""

false

false

Rows: 1

4. Starting databases

Databases can be started using the command START DATABASE.

Query
START DATABASE customers

0 rows, System updates: 1

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

Query
SHOW DATABASE customers
Table 10. Result
name address role requestedStatus currentStatus error default home

"customers"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

Rows: 1

5. Deleting databases

Databases can be deleted using the command DROP DATABASE.

Query
DROP DATABASE customers

0 rows, System updates: 1

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

Query
SHOW DATABASES
Table 11. Result
name address role requestedStatus currentStatus error default home

"movies"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"neo4j"

"localhost:7687"

"standalone"

"online"

"online"

""

true

true

"northwind-graph"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

"system"

"localhost:7687"

"standalone"

"online"

"online"

""

false

false

Rows: 4

This command is optionally idempotent, with the default behavior to throw an exception if the database does not exists. Appending IF EXISTS to the command will ensure that no exception is thrown and nothing happens should the database not exist.

Query
DROP DATABASE customers IF EXISTS

The DROP DATABASE command will remove a database entirely. However, 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 customers 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

6. Wait options

Aside from SHOW DATABASES, the database management commands all accept an optional WAIT/NOWAIT clause. The WAIT/NOWAIT clause allows a user to specify whether to wait for the command to complete before returning. The options are:

  • WAIT n SECONDS - Wait the specified number of seconds (n) for the command to complete before returning.

  • WAIT - Wait for the default period for the command to complete before returning.

  • 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.

Query
CREATE DATABASE slow WAIT 5 SECONDS
Table 12. 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.