Creating a Database in SQL: CREATE DATABASE for MySQL, PostgreSQL, SQL Server and SQLite

Creating a database in SQL means running a CREATE DATABASE statement (or, in SQLite, opening a file). The statement is nearly identical across systems, but the options after the name, the privilege you need and the errors you meet differ by system. This guide sets out what the official documentation of MySQL 8.4, PostgreSQL, SQL Server and SQLite says, so a research group can create a database once, with the right character set, and be able to repeat it.

What creating a database in SQL is used for in research

A research project that keeps records in tables (survey responses, instrument readings, sample inventories, participant identifiers) needs a container to hold them. Creating a database with SQL, rather than clicking through a tool, gives you a statement you can save in a script file and run again on another machine. A saved script is also something you can describe in a data management plan and check into your project files. Naming the columns and their meanings afterwards is a separate task, covered in data dictionaries for research data management; the kinds of table structure you can choose are covered in SQL database types.

Install or access (official source only)

You need a running database system and an account that is allowed to create databases. The permission rule is different in each system, and it is the most common reason the statement fails:

• MySQL: “To use this statement, you need the CREATE privilege for the database.” (MySQL 8.4 Reference Manual, CREATE DATABASE.)
• PostgreSQL: “To create a database, you must be a superuser or have the special CREATEDB privilege.” (PostgreSQL documentation, CREATE DATABASE.)
• SQL Server: requires CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission, and Microsoft notes that permission to create databases is typically limited to a few logins. In Azure SQL Database the creator must be a server-level principal login, the Microsoft Entra admin, or a member of the dbmanager role. (Microsoft Learn, CREATE DATABASE (Transact-SQL).)
• SQLite: no account is involved. The sqlite3 command-line shell creates the database file when you name one that does not yet exist.

If you are using a shared institutional server, ask the administrator for the privilege above or for the database to be created for you. Installers and downloads are published by each project on its official documentation site, listed under Sources.

Step-by-step: creating a database with SQL

MySQL

1. Connect with an account that holds the CREATE privilege.
2. Run the statement, using the documented syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ..., for example CREATE DATABASE IF NOT EXISTS survey2026; (the name is an example; add a CHARACTER SET or COLLATE option after checking the available values in step 3).
3. To see valid values for the options, run SHOW CHARACTER SET and SHOW COLLATION, which the manual names as the lists of available values.

The manual states that CREATE SCHEMA is a synonym for CREATE DATABASE. The create options are [DEFAULT] CHARACTER SET, COLLATE and ENCRYPTION ('Y' or 'N'); if you leave ENCRYPTION out, the default comes from the default_table_encryption setting.

PostgreSQL

1. Connect as a superuser or a role with CREATEDB.
2. Run CREATE DATABASE lusiadas; (an example from the PostgreSQL documentation), or add options such as CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;.
3. For a non-default locale, the documentation example is CREATE DATABASE music LOCALE 'sv_SE.utf8' TEMPLATE template0;.

By default the new database is created by cloning the standard system database template1. Naming TEMPLATE template0 gives a pristine copy, and the documentation says encoding and locale must match the template unless template0 is used. The command-line program createdb is a wrapper around this statement.

SQL Server

1. Connect with a login that has one of the permissions above.
2. Run the example from Microsoft Learn, which drops a previous test database and creates a fresh one (shown below).

USE master;
GO
IF DB_ID(N'mytest') IS NOT NULL
    DROP DATABASE mytest;
GO
CREATE DATABASE mytest;
GO

The base syntax is CREATE DATABASE database_name. All user-defined objects in the model database are copied to every newly created database. Without file specifications, the primary file takes the size of the model database’s primary file, and the log file is the larger of 512 KB or 25% of the primary file.

SQLite

1. Start the shell with a filename: sqlite3 ex1.db. The SQLite documentation says that if the named file does not exist, a new database file with that name is created automatically.
2. To switch to another file from inside the shell, use .open FILE, which closes the previous connection and opens a new one.
3. Start sqlite3 with no filename and a transient in-memory database is used; it is deleted when the program exits.

The CLI documentation describes a database as a file that is created on opening, and it does not present a CREATE DATABASE statement; use the filename route above.

Side-by-side summary

System	How you create it	Permission	Options the documentation names
MySQL 8.4	CREATE DATABASE or CREATE SCHEMA	CREATE privilege for the database	IF NOT EXISTS, CHARACTER SET, COLLATE, ENCRYPTION
PostgreSQL	CREATE DATABASE or the createdb program	Superuser or CREATEDB	OWNER, TEMPLATE, ENCODING, LOCALE, TABLESPACE, ALLOW_CONNECTIONS, CONNECTION LIMIT, IS_TEMPLATE
SQL Server	CREATE DATABASE database_name	CREATE DATABASE, CREATE ANY DATABASE or ALTER ANY DATABASE	Copies the model database
SQLite	Open a new filename in sqlite3	None (file access)	In-memory when no filename is given

Output and how to read it

A successful CREATE DATABASE returns no result set; you confirm it by listing databases. In SQL Server, Microsoft Learn’s example checks the new database’s files with SELECT name, size, size * 1.0 / 128 AS [Size in MBs] FROM sys.master_files WHERE name = N'mytest';. In SQLite, .databases lists the databases in the connection (main first, and temp always) and .tables lists tables; .quit exits the shell.

Troubleshooting

“Database already exists” when creating a database in MySQL

The manual states that an error occurs if the database exists and you did not specify IF NOT EXISTS. Add the clause so a setup script can be re-run safely. The PostgreSQL synopsis in the documentation does not show an IF NOT EXISTS clause, so do not assume it there.

Permission denied

Compare your account against the permission list in the Install or access section. In PostgreSQL that means superuser or CREATEDB; in MySQL, the CREATE privilege for that database name.

PostgreSQL: cannot run inside a transaction block

The documentation states that CREATE DATABASE cannot be executed inside a transaction block. Run it on its own, outside BEGIN ... COMMIT, or use the createdb program.

MySQL: statement refused during LOCK TABLES

The manual states that CREATE DATABASE is not permitted in a session that has an active LOCK TABLES statement. Release the locks first.

PostgreSQL: encoding or locale mismatch

Encoding and locale must match the template database except when template0 is used, so name TEMPLATE template0 when you need a different locale. Database-level settings (ALTER DATABASE) and grants are not copied from the template.

Free alternatives

The systems below each document a way to create a database. No prices are stated here; check each project’s own site for licence terms.

System	Create route in its documentation	Notes from the documentation
MySQL	CREATE DATABASE [IF NOT EXISTS] db_name	Character set and collation set per database
PostgreSQL	CREATE DATABASE name or createdb	Clones template1 by default
SQLite	sqlite3 filename.db	One file per database; in-memory option

If you are choosing between two of these for a project, see MySQL vs PostgreSQL, and for the relational-or-not decision see SQL vs NoSQL.

FAQ

What is the SQL statement for creating a database?

CREATE DATABASE name; is the basic form in MySQL, PostgreSQL and SQL Server. SQLite creates a database file when you open a new filename in its shell.

How do I create a database in SQL without an error if it already exists?

In MySQL, write CREATE DATABASE IF NOT EXISTS db_name;. The PostgreSQL synopsis shows no such clause.

Is SCHEMA the same as DATABASE?

In MySQL, yes: CREATE SCHEMA is documented as a synonym for CREATE DATABASE. Do not assume this for other systems.

Can I choose the character set when creating a database with SQL?

Yes. MySQL takes CHARACTER SET and COLLATE options; PostgreSQL takes ENCODING and LOCALE.

Do I need special rights to create a database?

Yes, in each server system covered above; see the permission list in the Install or access section.

Sources (official documentation)

All pages below were checked on 2026-10-29.

• MySQL 8.4 Reference Manual, CREATE DATABASE Statement: https://dev.mysql.com/doc/refman/8.4/en/create-database.html
• PostgreSQL documentation, CREATE DATABASE: https://www.postgresql.org/docs/current/sql-createdatabase.html
• Microsoft Learn, CREATE DATABASE (Transact-SQL), SQL Server (page dated 2026-02-06): https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=sql-server-ver17
• SQLite, Command Line Shell For SQLite: https://www.sqlite.org/cli.html