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!
The shell allows redirection, and sqlite3 can get the command as a parameter:
sqlite3 test.db .schema > schema.sql
Figured it out! I just needed to escape the text in the echo statement:
echo -e '.output schema.sql\n.schema' | sqlite3 test.db
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.
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
