SQL CREATE DATABASE

Every table, view, and index you write in SQL has to live somewhere — that somewhere is a database, and CREATE DATABASE is the statement that brings one into existence. It’s the very first step in setting up a new project: before you can run a single CREATE TABLE, the database that will hold it must already exist. This lesson covers the standard syntax across major systems, what actually happens on disk when you run it, and how SQLite — which has no CREATE DATABASE statement at all — handles the same job differently.

Overview: How CREATE DATABASE Works

CREATE DATABASE is a Data Definition Language (DDL) statement. On client-server systems like MySQL, PostgreSQL, and SQL Server, it talks directly to the engine’s system catalog — the internal set of tables the engine uses to track its own metadata. Running it does several things at once: the engine checks that the requested name isn’t already taken, allocates the on-disk storage structures the new database will need, registers a catalog entry so every future connection can see the database, and creates a default schema/namespace inside it so you have somewhere to immediately start creating tables.

The exact storage mechanics differ by engine. MySQL (with the default InnoDB engine) creates a new directory on disk, one per database, and stores each table’s data and metadata inside it. PostgreSQL instead creates a new entry in the shared pg_database catalog and clones a fresh set of system catalogs for it — a PostgreSQL “database” is a fully isolated set of catalogs, not just a folder. SQL Server allocates both a primary data file (.mdf) and a transaction log file (.ldf) for the new database. In every case, creating the database is a privileged operation — by default only administrators or roles explicitly granted the right can run it, because it consumes server-level resources shared by everyone connected to that instance.

SQLite is different. SQLite has no client-server process and no CREATE DATABASE statement in its grammar — a SQLite “database” is simply a single file on disk (or an in-memory database). You “create” one just by opening a connection to a file path that doesn’t exist yet; SQLite creates the file automatically the first time you write to it. If you need to work with more than one database file at once, SQLite gives you ATTACH DATABASE, which brings an additional file into the current connection under an alias. We’ll use that as the practical, runnable stand-in for CREATE DATABASE in the examples below.

Syntax

The general form on server-based engines is:

CREATE DATABASE [IF NOT EXISTS] database_name
[WITH]
    [OWNER = owner_name]
    [ENCODING = 'encoding']
    [CHARACTER SET charset_name]
    [COLLATE collation_name];

Part	Meaning
database_name	The identifier for the new database. Must be unique on that server instance.
IF NOT EXISTS	Skips the operation (instead of raising an error) if a database with that name already exists. Supported by MySQL and PostgreSQL 9.1+; not standard on SQL Server.
OWNER	(PostgreSQL) The role that owns the new database and its default privileges.
CHARACTER SET / ENCODING	The default text encoding for data stored in the database (e.g. utf8mb4, UTF8).
COLLATE	The default collation — the rules used to sort and compare text.

Dialect	Equivalent concept
MySQL / MariaDB	CREATE DATABASE db_name; — creates a data directory.
PostgreSQL	CREATE DATABASE db_name; — creates isolated catalogs.
SQL Server	CREATE DATABASE DbName; — creates .mdf/.ldf files.
SQLite	No statement — a file is created on first write, or use ATTACH DATABASE 'file' AS alias; to add one to a connection.

Examples

Example 1: Basic CREATE DATABASE (MySQL)

CREATE DATABASE company_db;

This is the simplest possible form. On a MySQL server it creates a brand-new, empty database named company_db using the server’s default character set and collation. It contains no tables yet — you would follow it with USE company_db; and then CREATE TABLE statements. This exact statement is MySQL/SQL-Server-style syntax and does not run on SQLite, which has no CREATE DATABASE keyword at all.

Example 2: CREATE DATABASE with options (PostgreSQL)

CREATE DATABASE company_db
    WITH OWNER = admin
    ENCODING = 'UTF8';

Here the new database is explicitly assigned an owner role (admin) and a text encoding (UTF8). PostgreSQL will refuse this if the admin role doesn’t already exist, and refuses the whole statement if a database named company_db is already present (add IF NOT EXISTS-style guarding at the application level, since PostgreSQL’s own CREATE DATABASE does not support an IF NOT EXISTS clause before version 9.1’s workaround patterns — many teams instead check pg_database first).

Example 3: SQLite’s equivalent — ATTACH DATABASE

ATTACH DATABASE ':memory:' AS store_db;

CREATE TABLE store_db.products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO store_db.products (product_id, product_name, price) VALUES
    (1, 'Keyboard', 49.99),
    (2, 'Mouse', 19.99),
    (3, 'Monitor', 199.99);

SELECT product_id, product_name, price
FROM store_db.products
ORDER BY product_id;

Result:

product_id	product_name	price
1	Keyboard	49.99
2	Mouse	19.99
3	Monitor	199.99

ATTACH DATABASE is the closest real, runnable equivalent SQLite offers: it brings a new database (here an in-memory one, but it works identically with a file path like 'store.db') into the current connection under the alias store_db, and from then on you qualify objects in it with store_db.table_name, exactly like a schema-qualified name on a server engine.

How It Works Step by Step (Under the Hood)

On a server engine, running CREATE DATABASE triggers roughly this sequence:

• 1. Permission check — the engine verifies the connecting user has the CREATE DATABASE (or equivalent superuser/role) privilege.
• 2. Name uniqueness check — the catalog is scanned to ensure no database with that name already exists (unless IF NOT EXISTS is present, in which case a match causes a silent no-op).
• 3. Storage allocation — MySQL creates a directory; PostgreSQL clones template catalogs; SQL Server allocates .mdf/.ldf files.
• 4. Catalog registration — a row is written into the server’s system-level catalog (e.g. information_schema.SCHEMATA, pg_database, sys.databases) so every future connection can see and connect to it.
• 5. Commit — the new database becomes visible server-wide (in most engines this statement auto-commits and cannot be rolled back inside a transaction).

SQLite skips all of this: there’s no catalog to register with, because a SQLite database is the file. The closest thing to steps 3–4 is what ATTACH DATABASE does — it opens (or creates) a file and adds it to the current connection’s in-memory list of attached databases. You can inspect that list at any time:

ATTACH DATABASE ':memory:' AS reporting_db;

CREATE TABLE reporting_db.sales (
    sale_id INTEGER PRIMARY KEY,
    amount REAL
);

PRAGMA database_list;

Result:

seq	name	file
0	main	(empty — in-memory)
1	temp	(empty)
2	reporting_db	(empty — in-memory)

PRAGMA database_list always lists the built-in main and temp databases plus anything you’ve attached, showing the alias and the underlying file path (blank for in-memory databases). When you’re done with an attached database you can remove it from the connection with DETACH DATABASE:

ATTACH DATABASE ':memory:' AS temp_db;

CREATE TABLE temp_db.logs (
    id INTEGER PRIMARY KEY,
    message TEXT
);

INSERT INTO temp_db.logs (message) VALUES ('created'), ('tested');

SELECT COUNT(*) AS log_count FROM temp_db.logs;

DETACH DATABASE temp_db;

Result (before DETACH):

log_count
2

The SELECT returns a single row confirming two log entries exist, then DETACH DATABASE removes temp_db from the connection entirely — its data is gone once the underlying in-memory or temp file is released (a file-backed attached database, by contrast, simply becomes inaccessible until re-attached; its data stays safely on disk).

Common Mistakes

Mistake 1: Creating the same database twice without a guard

CREATE DATABASE company_db;
CREATE DATABASE company_db;

On MySQL or SQL Server this raises an error on the second statement (“database exists”), which will crash an unguarded setup script the second time it’s run. Fix it with IF NOT EXISTS, which turns the second call into a safe no-op:

CREATE DATABASE IF NOT EXISTS company_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Mistake 2: Assuming SQLite supports CREATE DATABASE

Because CREATE DATABASE is so common on other engines, developers new to SQLite often try to run it and are confused when the driver throws a syntax error. SQLite has no such statement — a database is created implicitly by connecting to a new file path, or explicitly added to a connection with ATTACH DATABASE 'path/to/file.db' AS alias;. There is nothing to “fix” syntactically; the correct move is to use the file-based model instead.

Mistake 3: Forgetting to select the new database before creating tables (MySQL)

CREATE DATABASE company_db;
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100));

On MySQL, creating a database does not automatically make it the active one for the session. Without a following USE company_db;, the CREATE TABLE statement runs against whatever database the connection was already pointed at — often not the one you just made. Always follow CREATE DATABASE with an explicit USE (or fully qualify table names as company_db.employees).

Best Practices

• Always include IF NOT EXISTS in setup/migration scripts so re-running them doesn’t error out.
• Use lowercase, underscore-separated names (sales_db, not SalesDB) for cross-platform consistency, since some engines are case-sensitive about identifiers and others aren’t.
• Set an explicit character set and collation (e.g. utf8mb4 on MySQL) instead of relying on server defaults, which can vary between environments and cause subtle sorting/encoding bugs later.
• Restrict the CREATE DATABASE privilege to administrators on shared or production servers — it’s a resource-allocating operation, not a routine one.
• Manage database creation through migration tooling (Flyway, Liquibase, Alembic, etc.) in real projects rather than running ad-hoc statements by hand, so environments stay reproducible.
• Know which engine you’re targeting before writing the statement — the keyword is the same everywhere, but options and defaults genuinely differ.
• In SQLite, treat the file path itself as your “database name,” and use ATTACH DATABASE only when you deliberately need more than one file in a single connection.

Practice Exercises

• Exercise 1: Write the MySQL statement that creates a database named bookstore only if it doesn’t already exist, using the utf8mb4 character set and the utf8mb4_unicode_ci collation.
• Exercise 2: In SQLite, write a self-contained script that attaches an in-memory database aliased inventory_db, creates a table items with columns item_id INTEGER PRIMARY KEY and item_name TEXT, inserts two rows of your choosing, and selects them back ordered by item_id.
• Exercise 3 (no SQL required): Explain in your own words why running CREATE DATABASE shop; twice in a row on PostgreSQL without IF NOT EXISTS fails on the second run, and name the clause that would prevent the failure.

Summary

• CREATE DATABASE is the DDL statement that creates a new, empty database container on server-based engines like MySQL, PostgreSQL, and SQL Server.
• Running it allocates storage (a directory, catalog entries, or data/log files depending on the engine) and registers the new database so all future connections can see it.
• Common options include IF NOT EXISTS, CHARACTER SET/ENCODING, COLLATE, and (PostgreSQL) OWNER.
• SQLite has no CREATE DATABASE statement — a database is just a file, created automatically on first write, or added to a connection with ATTACH DATABASE ... AS alias;.
• PRAGMA database_list shows every database currently attached to a SQLite connection, and DETACH DATABASE removes one.
• Always guard creation scripts with IF NOT EXISTS and remember to select/use the new database before creating tables in it.