What is a SQL file?

SQL is a plain-text format you can open in any editor. It is used for moving data between programs and archiving.

The extension is .sql, and the full name is SQL Insert Statements. Both matter less than what the file can hold, which is what the rest of this page is about.

It dates from 1986. The specification is ISO/IEC 9075.

A format that has been readable for that long is a format worth trusting with something you want back in ten years.

It is published in full, so anyone can implement it from the document rather than by inspection, which is why it turns up in so many programs and why files written twenty years ago still open. A published specification is not the same thing as a royalty-free one: where a format wraps a codec, the patent licensing is a separate question the standard does not settle.

SQL has a comment syntax, which is the difference between a file a person maintains and one a program writes. Comments are the first thing lost converting to a format without them, and nothing warns you.

PostgreSQL, MySQL and DBeaver read it, and so do most programs of the same kind.

If a file will not open, the format is rarely the problem — it is more often that the program predates it. Converting to something older is the reliable way past that, and it is what the rest of this site is for.

No browser reads it.

That is the single most common reason to convert it: not that the format is bad, but that the place you want to show the file cannot read it.

SQL is meant to be opened and changed. Keep the file in this format for as long as the work is going on, and export from it whenever a finished copy is needed.

SQL is a query language standardised since 1986. A .sql file is a text file with statements in it, and that is the whole of the format — there is no header, no structure, no way to tell from the extension what any particular file does. It might create tables, insert a million rows, alter a schema, or simply hold three queries somebody wanted to keep.

In practice the ones people exchange fall into two groups. A dump is a database or a table written out as statements that recreate it elsewhere. A migration is a script that changes a schema in a controlled way. Both are text, both open in any editor, and confusing the two is how a script intended to add a column ends up dropping a table.

Usually three things in sequence. A statement dropping the table if it exists, which is why running a dump against a live database is not a reversible act. A CREATE TABLE statement defining the columns and their types. Then the rows, as INSERT statements — sometimes one per row, sometimes a few hundred rows batched into one statement, which is dramatically faster to load and much harder to read.

Around them a real dump adds housekeeping: character-set declarations, settings that suspend index rebuilding while the data loads, and a transaction wrapper so that a failure halfway through leaves nothing behind. That housekeeping is database-specific, and it is the main reason a dump from one database will not load into another.

The standard specifies a core, and every database extends it. Identifier quoting differs — backticks in MySQL, double quotes in PostgreSQL, square brackets in SQL Server. Auto-incrementing keys are spelled three different ways. Date functions, string functions, boolean handling and the type names themselves all diverge.

So a MySQL dump does not load into PostgreSQL, and the error it produces is usually a syntax complaint on line four rather than anything explaining why. Converting between dialects is real work and mostly mechanical; if you are moving data rather than a schema, going through CSV is very often quicker and more reliable than translating the SQL.

On the command line each database has its own client and the invocation is short: psql for PostgreSQL, the mysql client for MySQL and MariaDB, sqlite3 for SQLite, all taking the file as input. This is the fastest route for a large dump by a wide margin — a graphical tool that reads the file into memory first will struggle with anything past a few hundred megabytes.

Two things to do before running it. Read the top of the file to see whether it drops anything. And create the target database first if the dump does not create it itself, because a dump written for one database name will not silently invent another.

Usually one of four. A packet or statement size limit, hit by a batched INSERT holding several thousand rows — the server’s limit, not the file’s. A timeout in a graphical client that has no equivalent on the command line. A character-set mismatch, which shows up as mangled text rather than an error and is worth catching early. Or a foreign key constraint rejecting rows that arrive before the table they reference.

That last one is the most common and the least obvious. Dumps generally handle it by disabling constraint checking around the load, and a dump edited by hand — a few tables lifted out of a larger file — loses that protection. Loading in dependency order, or restoring the wrapper, is the fix.

A dump is for recreating a database. If what you want is the data — to analyse it, load it into a spreadsheet, hand it to somebody who does not run a database — CSV is the better container, and Parquet is better still for anything large enough that the difference is noticeable.

The reverse is worth knowing too. Turning a spreadsheet into INSERT statements is the standard way of getting a table into a database when you have no direct connection to it, which is a common situation with managed hosting: no port open, but a query console that will happily run a file you paste into it.

It is text, so any editor opens it. An editor that understands SQL syntax is worth using for anything you intend to run, because it will colour the statements and make an unbalanced quotation mark visible before the database finds it.

A dump can be very large, and the sensible way to inspect a multi-gigabyte one is to look at the first and last few dozen lines rather than opening the whole thing. The first lines tell you the dialect, the character set and whether anything gets dropped; the last tell you whether the dump finished, which is the single most useful thing to know about a file somebody sent you.