The SQL file (.sql)

A .sql file holds SQL statements as plain text. Nothing happens until a database client reads the file and sends the statements to a database server (or, for SQLite, to a database file). It's how database structure, data and changes travel between people, servers and version control.

• Schema scripts create tables, indexes and views for a new database.
• Migrations are small numbered scripts that change an existing database step by step, applied in order by a tool.
• Dumps are backups: a script that recreates a whole database, data included, written by pg_dump, mysqldump or SQLite's .dump.
• Seed data and reports: inserts for test data, or saved queries someone runs by hand.

Three things a SQL file is not:

• Not a database. The data lives in the database server or in a database file (.db or .sqlite for SQLite, .mdf for SQL Server). A .sql file is instructions, though a dump can contain every row as INSERT or COPY statements.
• Not one language. The ISO standard is the common core, and every database extends it differently. A script written for MySQL often fails on PostgreSQL, and the other way round.
• Not safe to run blindly. It can delete tables as easily as create them. Read a script, and try it on a copy, before running it against data you care about.

Each database has a command-line client that reads a script from a file. Open a terminal (Command Prompt or PowerShell on Windows, Terminal on macOS and Linux) and run:

# PostgreSQL: stop at the first error; -1 runs it all in one transaction
psql -d shop -v ON_ERROR_STOP=1 -f script.sql
psql -d shop -v ON_ERROR_STOP=1 -1 -f script.sql

# MySQL or MariaDB: stops at the first error unless you add --force
mysql --default-character-set=utf8mb4 shop < script.sql

# SQLite: creates shop.db if it doesn't exist; -bail stops at the first error
sqlite3 -bail shop.db < script.sql

# SQL Server: put ":On Error exit" at the top of the script to stop at the first error
sqlcmd -S localhost -d shop -i script.sql

• Graphical tools: in SSMS (SQL Server Management Studio), DBeaver, pgAdmin or MySQL Workbench, open the file and click Execute or Run. On Windows, double-clicking a .sql file usually opens it in SSMS or an editor, depending on what's installed; it doesn't run it.
• The database must exist first, unless the script creates it. createdb shop (PostgreSQL), CREATE DATABASE shop (MySQL) or simply naming a new file (SQLite) does that.
• The output of SELECT statements goes to the terminal. Add -o file.txt to psql or redirect with > out.txt to keep it.

RFC 6922, which registered application/sql, says it plainly: although a standard exists, vendor implementations vary significantly. The basics in the anatomy above work almost everywhere; these are the places where they differ:

• Unquoted names change case. PostgreSQL folds them to lowercase, so CREATE TABLE Users makes users, and SELECT * FROM "Users" then fails. The SQL standard folds to uppercase instead. Either always quote a name or never quote it.
• Types are looser in SQLite. It gives a DECIMAL(10,2) column numeric affinity and ignores the precision, so 19.00 comes back as 19 and a sum as 34.0.
• Client commands aren't SQL. Lines like \set, DELIMITER // or GO are read by one particular client and never reach the server. Run the script in another tool and they're syntax errors.

-- The same idea in Transact-SQL (SQL Server), run with sqlcmd or SSMS
CREATE TABLE dbo.books (
    id    INT IDENTITY(1,1) PRIMARY KEY,
    title NVARCHAR(200) NOT NULL
);
GO

BEGIN TRANSACTION;
INSERT INTO dbo.books (title) VALUES (N'Example Tales');
COMMIT TRANSACTION;
GO

SELECT TOP (5) id, title FROM dbo.books ORDER BY id;
GO

• Restore a PostgreSQL dump into a new, empty database with psql -X -d newdb -f dump.sql. A plain-text dump loads its data with COPY … FROM stdin blocks, which only psql understands. Dumps in pg_dump's custom format (-Fc) aren't SQL at all and need pg_restore.
• Restore a MySQL dump with mysql newdb < dump.sql. A dump made with --databases or --all-databases contains its own CREATE DATABASE and USE statements.
• Recent pg_dump versions start the file with a \restrict line and a random key, and end it with \unrestrict. In between, psql refuses all other backslash commands, a fix for CVE-2025-8714, in which a malicious superuser of the source server could plant commands in a dump to run on the computer restoring it.

When a script fails halfway, the statements before the error have already run. Without a transaction, you're left with half a change: a table created but not filled, money taken from one account but not added to the other.

• Wrap related changes in BEGIN; … COMMIT; (START TRANSACTION in MySQL, BEGIN TRANSACTION in SQL Server).
• In psql, combine --single-transaction with ON_ERROR_STOP. psql then sends BEGIN first and COMMIT at the end, or ROLLBACK if anything fails. PostgreSQL's documentation suggests both options for restoring dumps.
• In MySQL, DDL ends the transaction. CREATE TABLE, ALTER TABLE and DROP TABLE commit whatever was pending before they run, and can't be rolled back. sample-mysql.sql below shows a row surviving a ROLLBACK for this reason.
• Make scripts safe to re-run with IF EXISTS and IF NOT EXISTS, so running one twice doesn't fail or duplicate data.

A SQL file doesn't say which encoding it's in, and RFC 6922 warns against guessing. The client reads the bytes in its own client encoding and the server converts them to the database's encoding. If the client guesses wrong, the database stores the wrong characters, and the damage is permanent until someone fixes the data.

• Save scripts as UTF-8. In our tests, psql 16, the MySQL 8.0 client and sqlite3 3.45 all accepted a UTF-8 byte order mark and CRLF line endings, but older tools may not.
• MySQL: use utf8mb4. MySQL's older utf8 (now called utf8mb3) stores at most 3 bytes per character, so it can't hold emoji. The MySQL manual recommends utf8mb4 for everything new.
• MySQL: name the client character set. The mysql client picks its character set from the system locale. On a server with no UTF-8 locale, that can be latin1, and UTF-8 text gets stored double-encoded. Run mysql --default-character-set=utf8mb4 or start the script with SET NAMES utf8mb4;.
• PostgreSQL: the client encoding depends on how psql is started and on the PGCLIENTENCODING variable. SET client_encoding = 'UTF8'; at the top of a script removes the doubt, and pg_dump writes that line for you.

Any text editor opens a .sql file. VS Code, JetBrains DataGrip and DBeaver add syntax highlighting and can run the statements against a connection; formatters such as pg_format and sqlfluff tidy and check them. Opening the file runs nothing.

• Running a script is running code. It can drop tables, change permissions or, in some databases, read server files. Run scripts from others as a database user with only the rights the script needs, and against a copy first.
• Dumps contain everything. Customer names, email addresses, password hashes and anything else in the database ends up in the file in readable text. Treat a dump like the database itself: encrypt it, limit who has it, and delete old ones. Under the GDPR, personal data in a backup is still personal data.
• Dumps can carry client commands. psql backslash commands such as \!, which runs a shell command, are why recent pg_dump versions add \restrict. Only restore dumps from sources you trust.

SQL injection is the best-known database attack, and it happens in application code, not in .sql files. It occurs when a program builds a query by pasting user input into SQL text: if the input contains a quote, the rest of it is read as SQL, and the attacker can change what the query does, for example to return every row instead of one. The fix, as the OWASP cheat sheet sets out, is parameterised queries (prepared statements): the query is sent with placeholders, and the values travel separately, so they are never read as SQL. Escaping input by hand is unreliable, and least-privilege database accounts limit the damage if something slips through.