SQL guide · Tables, views & databases (DDL)

SQL CREATE DATABASE: How to Create a New Database

The CREATE DATABASE statement creates a new, empty database on a database server. A database is the top-level container that holds your tables, views, indexes, procedures and permissions. You typically create one per application or per environment, so that a project called shop has its own isolated set of objects separate from, say, an analytics database on the same server.

Right after creation the database contains no user tables. You then connect to it, or switch to it, and run CREATE TABLE statements to build the schema. Many systems also let you specify options at creation time, most commonly the character set and collation, which control how text is stored and compared. Choosing these correctly up front matters, because changing the encoding of a database full of data later can be painful.

Creating a database needs elevated privileges. On a managed cloud service you may not be allowed to run the statement at all and must use a console or API instead. Names are also restricted: avoid spaces and reserved words, and be aware that some systems treat the name as case sensitive depending on the operating system. Treat the statement as a one-time setup step, normally run from a migration or provisioning script.

Basic syntax

CREATE DATABASE database_name;

-- Common options (dialect specific)
CREATE DATABASE database_name
  [CHARACTER SET charset_name]
  [COLLATE collation_name];

Only the database name is required. The optional clauses vary between systems, so the PostgreSQL form uses ENCODING and OWNER while MySQL uses CHARACTER SET and COLLATE. Run SHOW DATABASES in MySQL or \l in psql to confirm the result.

SQL CREATE DATABASE examples

Create a simple database

The shortest form creates an empty database with server defaults for encoding and collation. This is fine for experiments, and you can then connect to the new database and begin creating tables.

CREATE DATABASE shop;

Create a MySQL database with a character set

This sets the character set to utf8mb4 so that all Unicode text, including emoji, is stored correctly, and picks a matching collation. The IF NOT EXISTS guard makes the script safe to run twice.

CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

Create a PostgreSQL database with an owner

Here the database is created with UTF8 encoding and assigned to a specific role. Using a template database is how PostgreSQL clones an existing setup, and template0 is the clean template you need when choosing a non-default encoding.

CREATE DATABASE shop
  OWNER = app_user
  ENCODING = 'UTF8'
  TEMPLATE = template0;

Create a database and then use it in SQL Server

In SQL Server you create the database and then switch the session into it with USE before creating tables. The GO separator is a client tool batch marker, not part of the SQL language itself.

CREATE DATABASE shop;
GO

USE shop;
GO

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

Where SQL dialects differ

PostgreSQL: Supports OWNER, ENCODING, LC_COLLATE, LC_CTYPE and TEMPLATE. There is no IF NOT EXISTS for CREATE DATABASE, so scripts often check pg_database first. The statement cannot run inside a transaction block.

MySQL: CREATE SCHEMA is a synonym for CREATE DATABASE. IF NOT EXISTS, CHARACTER SET and COLLATE are supported. In MySQL 8.0 the default character set is utf8mb4.

SQL Server: Supports file placement options such as ON PRIMARY and LOG ON, plus COLLATE. Without options the new database copies settings from the model system database. Use USE database_name to switch into it.

SQLite: There is no CREATE DATABASE statement. A database is a single file that is created when you open or connect to a new filename, or when you attach one with ATTACH DATABASE.

Oracle: A full CREATE DATABASE is an administrator task used when building an instance. Application developers more often create a user, which acts as a schema, with CREATE USER, or a pluggable database in multitenant setups.

Common mistakes

  • Creating the database without thinking about encoding and collation, then storing multilingual text that comes back garbled. Choose a Unicode encoding such as UTF8 or utf8mb4 from the start.
  • Running the statement without the needed privilege, or on a managed service that forbids it. Check your role first and use the provider console if required.
  • Forgetting to switch into the new database before creating tables, so the tables land in a different database such as the default one.
  • Re-running a setup script that already created the database and getting an error. Use IF NOT EXISTS where available or test for existence first.
  • Using spaces, hyphens or reserved words in the name, which forces quoting everywhere. Stick to lowercase letters, digits and underscores.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator