CODEWITHSUNDEEP
sqldatabasemacossqliteterminal
Joe Flip
Joe Flip

Reputation: 1195

SQLite: Dump only schema into .sql file from command line

I'm trying to dump the schema for test.db only (i.e. no data) into a file called schema.sql from the command line in OS X without launching sqlite3.

I know I can do:

sqlite3
.open test.db
.output schema.sql
.schema
.quit

But I don't want to launch sqlite 3. This...

echo '.output schema.sql' | sqlite3 test.db

creates the empty file but this...

echo '.schema' | sqlite3 test.db

only prints the schema. How can I write it to that file from Terminal?

Thanks!

Upvotes: 34

Views: 22176

Answers (4)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

For example, you can export only the schema of apple.db to backup.sql with .schema as shown below. *backup.sql is created if it doesn't exist and my answer explains how to export schema and data:

sqlite3 apple.db .schema > backup.sql

And, you can export only the schema of apple.db to backup.sql with the commands below. *.output creates and selects or only selects a file depending on the file existence and you must exit to close the file (e.g., .exit or .quit) otherwise the results of SQLite commands are output to the file:

sqlite3 apple.db
sqlite> .output backup.sql
sqlite> .schema
sqlite> .exit

And, you can export only the schema of the specific tables in apple.db to backup.sql with the commands below. *.schema exports the schema of only one specific table so you should run .schema multiple times if you want to export the schema of the specific multiple tables:

sqlite3 apple.db
sqlite> .output backup.sql
sqlite> .schema person
sqlite> .schema animal
sqlite> .exit

Upvotes: 1

Sacha
Sacha

Reputation: 21

FYI you could also have done

( echo .output schema.sql ; echo .schema ) | sqlite3 test.db

This is running two echo commands in a subshell and piping its output.

Or

sqlite3 test.db <<EOF
.output schema.sql
.schema
EOF

See How does "cat << EOF" work in bash? for what this is doing.

Upvotes: 2

CL.
CL.

Reputation: 180010

The shell allows redirection, and sqlite3 can get the command as a parameter:

sqlite3 test.db .schema > schema.sql

Upvotes: 69

Joe Flip
Joe Flip

Reputation: 1195

Figured it out! I just needed to escape the text in the echo statement:

echo -e '.output schema.sql\n.schema' | sqlite3 test.db

Upvotes: 5

Related Questions