# CREATE DATABASE The `CREATE DATABASE` command creates a database. ## Syntax ```sql CREATE DATABASE [IF NOT EXISTS] [] [PARTITIONS n] [] [SUB_PARTITIONS n] [{FOR | INTO | ON} ] : WITH {SYNC | ASYNC} DURABILITY | WITH {SYNC | ASYNC} REPLICATION | WITH SYNC DURABILITY SYNC REPLICATION | WITH ASYNC DURABILITY ASYNC REPLICATION : [DEFAULT] CHARACTER SET [=] charset_name : | | : S3 { '' | '' } [CONFIG '',<'{"verify_ssl":false|true}'>] CREDENTIALS '' : AZURE { '' | '' | '' } [CONFIG ''] CREDENTIALS '' : GCS {'' | '' } [CONFIG ''] CREDENTIALS '' -- "schema" is an alias for "database" CREATE SCHEMA [IF NOT EXISTS].. ``` > **šŸ“ Note**: In the SingleStore Free version, this command does not support unlimited storage databases. For more information, see [SingleStore Editions](https://docs.singlestore.com/db/v9.1/introduction/singlestore-editions.md). ## Remarks * `database_name` is the name to assign to this new SingleStore database. Do not use the `_XX` suffix (such as `test_database_01`) while naming a SingleStore database as it may cause a mismatch with the database partition ordinals (sharded databases on each leaf node). * Database names are case-sensitive. Databases named `DB` and `db` are not the same objects and will be treated as such when [granting](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/grant.md) permissions to users. * `CONFIG` and `CREDENTIALS` can be specified in either order (`CONFIG` followed by `CREDENTIALS` or `CREDENTIALS` followed by `CONFIG`). For configuration examples referĀ [BACKUP DATABASE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/operational-commands/backup-database.md) * `CONFIG '{"verify_ssl":false|true}'` allows you to connect to an unverified SSL certificate in S3 when using the option `verify_ssl:false` * To create a local storage database, exclude the `{FOR | INTO | ON} ` option. To create an unlimited storage database use the `{FOR | INTO | ON} ` option. Note that `FOR`, `INTO`, and `ON` are synonyms and their functionality is identical. Prior to creating an unlimited storage database, the `enable_bottomless` engine variable must be set to `ON` (the default value). For more information on local storage and unlimited storage databases, see [Local and Unlimited Database Storage Concepts](https://docs.singlestore.com/db/v9.1/manage-data/local-and-unlimited-database-storage-concepts.md). * Creating an unlimited storage database using the `ON ` clause requires the `SUPER` permission. * `WITH {SYNC | ASYNC} REPLICATION` specifies whether high availability, redundancy-2 replication will be done synchronously or asynchronously. Synchronous replication from the master partitions will complete on all replicas before the commit of the transaction is acknowledged to the client application. If `WITH {SYNC | ASYNC} REPLICATION` is not specified, synchronous replication is used. See [Replication and Durability Concepts](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/high-availability-and-disaster-recovery/replication-and-durability-concepts.md) for more information. * `WITH {SYNC | ASYNC} DURABILITY` specifies whether in-memory database updates you make using [DDL](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl.md) and [DML](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml.md) commands are also saved to the log on disk synchronously or asynchronously. Synchronous updates to the log on disk will complete before the commit of the transaction is acknowledged to the client application. If `WITH {SYNC | ASYNC} DURABILITY` is not specified, async durability is used. > **šŸ“ Note**: `SYNC DURABILITY` cannot be disabled once a database is created. See [Replication and Durability Concepts](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/high-availability-and-disaster-recovery/replication-and-durability-concepts.md) for more information. * This command causes implicit commits. Refer to [COMMIT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/commit.md) for more information. * You cannot specify `WITH SYNC DURABILITY ASYNC REPLICATION`. * `PARTITIONS n` allows you to set the total number of partitions that the data will be split into. By default this is controlled by the `default_partitions_per_leaf` variable. Note that `n` refers to the *total* number of partitions across all leaves. Refer to the [Flexible Parallelism](https://docs.singlestore.com/db/v9.1/introduction/distributed-architecture/flexible-parallelism.md) page for more information. * `SUB_PARTITIONS n` allows you to set the number of sub-partitions for a database per physical partition at the time of database creation. Using this optional clause in the `CREATE DATABASE` command will override the value set by the `sub_to_physical_partition_ratio` variable. The value of `n` must be set at 0 or a power of 2 value of the physical partition count. For example, if the number of partitions is 6, the number of sub-partitions must be 6 \* (a power of 2 value). Refer to the [Flexible Parallelism](https://docs.singlestore.com/db/v9.1/introduction/distributed-architecture/flexible-parallelism.md) page for more information. * The `CREATE DATABASE ... DEFAULTĀ CHARSET=` syntax is accepted by SingleStore for compatibility with MySQL, but it has no effect. The database and its schema objects use the [server character set and collation](https://docs.singlestore.com/db/v9.1/reference/sql-reference/character-encoding.md). Character set and collation defaults can be set during table creation. See [CREATE TABLE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table.md) for more information. * [Node Requirements for SingleStore Commands](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands.md) * The `ceph_mode` flag has been deprecated. * Refer to the [Permissions Matrix](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions. ## Examples ## CREATE DATABASE with Default Options The following example creates a local database that uses all of the default options. ```sql CREATE DATABASE IF NOT EXISTS test; ``` ## CREATE DATABASE Using Sync Durability and Sync Replication The following example creates a local database that uses sync durability and sync replication. The latter is enabled by default. ```sql CREATE DATABASE IF NOT EXISTS test WITH SYNC DURABILITY; ``` ## CREATE DATABASE Unlimited Storage The following example creates an unlimited storage database. ```sql CREATE DATABASE bottomless_db ON S3 "bottomless_db_bucket/bottomless_db_folder" CONFIG '{"region":"us-east-1", "verify_ssl":false}' CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}'; ``` ## CREATE DATABASE SUB\_PARTITIONS The following command returns the default number of sub-partitions: ```sql SELECT @@sub_to_physical_partition_ratio; +-----------------------------------+ | @@sub_to_physical_partition_ratio | +-----------------------------------+ | 4 | +-----------------------------------+ ``` The following example uses the `SUB_PARTITONS n` clause to create a database with 96 sub-partitions. ```sql CREATE DATABASE test1 PARTITIONS 6 SUB_PARTITIONS 96; ``` ```sql SELECT DATABASE_NAME, NUM_PARTITIONS, NUM_SUB_PARTITIONS FROM information_schema.distributed_databases WHERE DATABASE_NAME = 'test1'; ``` ```output +---------------+----------------+--------------------+ | DATABASE_NAME | NUM_PARTITIONS | NUM_SUB_PARTITIONS | +---------------+----------------+--------------------+ | test1 | 6 | 96 | +---------------+----------------+--------------------+ ``` Assuming the default number of physical partitions is 8, the following example uses the `SUB_PARTITONS n` clause to create a database with 64 sub-partitions If n is not a power of 2 times the number of partitions (in this case, 6 \* 16 = 96), an error is generated and the database will not be created. ```sql CREATE DATABASE test2 SUB_PARTITIONS 64; ``` ```sql SELECT DATABASE_NAME, NUM_PARTITIONS, NUM_SUB_PARTITIONS FROM information_schema.distributed_databases WHERE DATABASE_NAME = 'test2'; ``` ```output +---------------+----------------+--------------------+ | DATABASE_NAME | NUM_PARTITIONS | NUM_SUB_PARTITIONS | +---------------+----------------+--------------------+ | test2 | 8 | 64 | +---------------+----------------+--------------------+ ``` If `n` in the `SUB_PARTITIONS n` clause is not a power of 2, an error is generated and the database will not be created. ```sql CREATE DATABASE test3 PARTITIONS 6 SUB_PARTITIONS 36; ERROR 1504 (HY000): Value of sub_partitions (36) needs to be a power-of-2 multiple of the partition count (6) ``` ```sql CREATE DATABASE test4 SUB_PARTITIONS 40; ERROR 1504 (HY000): Value of sub_partitions (40) needs to be a power-of-2 multiple of the partition count (8) ``` *** Modified at: June 18, 2026 Source: [/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-database/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-database/) (An index of the documentation is available at /llms.txt)