Creating and Running .sql Files

Write SQL statements in a text editor and save the file with the .sql extension. Save the file in UTF-8 encoding.

sample_create_users.sql

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    age INTEGER
);

INSERT INTO users (name, email, age) VALUES ('Okabe Rintaro', 'okabe_rintaro@wp-p.info', 18);
INSERT INTO users (name, email, age) VALUES ('Makise Kurisu', 'makise_kurisu@wp-p.info', 18);

SELECT * FROM users;

As shown above, you can write multiple SQL statements in a single .sql file. Each SQL statement must end with a semicolon (;). Note that SQL keywords such as SELECT and CREATE TABLE work regardless of case, but this site follows the convention of writing them in uppercase. For details, see the "SQL Writing Style (Uppercase and Lowercase)" section in Setup.

You can write comments (notes) in a .sql file. Comments are ignored by the database, so they are useful for leaving explanations or notes about your SQL.

-- Creates the members table.
CREATE TABLE members (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

/*
  The following inserts initial data.
  Three records of test data will be added.
*/
INSERT INTO members (name) VALUES ('Okabe Rintaro');
INSERT INTO members (name) VALUES ('Makise Kurisu');
INSERT INTO members (name) VALUES ('Shiina Mayuri');

MySQL and MariaDB share nearly identical syntax. MariaDB is a fork of MySQL and is compatible with it. In the examples below, the connection is made to the database mydb. You need to create the database in advance — if you haven't done so yet, refer to Setup.

Loading a File from Interactive Mode

You can execute a .sql file from within interactive mode using the source command.

mysql -u root -p mydb

source /path/to/create_users.sql

\. is a shorthand for source and works the same way.

\. /path/to/create_users.sql

Running Directly from the Command Line

You can execute a .sql file directly from the terminal without entering interactive mode. Use redirection (<) to pipe the file contents into MySQL.

mysql -u root -p mydb < create_users.sql

To verify the results, enter interactive mode and check.

mysql -u root -p mydb

SELECT * FROM users;
+----+---------------+--------------------+------+
| id | name          | email              | age  |
+----+---------------+--------------------+------+
|  1 | Okabe Rintaro | okabe_rintaro@wp-p.info  |   18 |
|  2 | Makise Kurisu | makise_kurisu@wp-p.info |   18 |
+----+---------------+--------------------+------+
2 rows in set (0.00 sec)

In the examples below, the connection is made to the database mydb. You need to create the database in advance — if you haven't done so yet, refer to Setup.

Loading a File from Interactive Mode

You can execute a .sql file from within interactive mode using the \i command.

psql -U postgres -d mydb

\i /path/to/create_users.sql

Running Directly from the Command Line

You can execute a .sql file directly from the terminal without entering interactive mode. Use the -f option to specify the file.

psql -U postgres -d mydb -f create_users.sql

To verify the results, enter interactive mode and check.

psql -U postgres -d mydb

SELECT * FROM users;
 id |     name      | email              | age
----+---------------+--------------------+-----
  1 | Okabe Rintaro | okabe_rintaro@wp-p.info  |  18
  2 | Makise Kurisu | makise_kurisu@wp-p.info |  18
(2 rows)

Loading a File from Interactive Mode

You can execute a .sql file from within interactive mode using the .read command.

sqlite3 mydb.db

.read /path/to/create_users.sql

Running Directly from the Command Line

You can execute a .sql file directly from the terminal without entering interactive mode. Use redirection (<) to pipe the file contents into SQLite.

sqlite3 mydb.db < create_users.sql

To verify the results, enter interactive mode and check.

sqlite3 mydb.db

.headers on
SELECT * FROM users;
id|name|email|age
1|Okabe Rintaro|okabe_rintaro@wp-p.info|18
2|Makise Kurisu|makise_kurisu@wp-p.info|18

SQLite does not display header rows by default. Running .headers on will show the column names.

A .sql file is simply a plain text file. You can create one by writing SQL statements in a text editor and saving it with the .sql extension. No special tools are required.

By writing multiple SQL statements together in a file, you can execute operations such as creating tables, inserting initial data, and restoring backups with a single command — which helps improve efficiency and prevent mistakes.

The method for running SQL files differs depending on the database. In particular, the commands for loading from interactive mode differ: MySQL uses source, PostgreSQL uses \i, and SQLite uses .read, so be sure to use the correct one.

For recommended editors, refer to Setup.