How to create an empty SQLite db with command?

Question

Is it possible to create an empty sqlite3 database from the command line (e.g. sqlite3 <someoption> dbname) which would create a database file for me empty of any tables so that I can access it from a different SQL editor?

Currently, when I do sqlite3 dbname, I get a sqlite prompt from which I can do CREATE TABLE ... but if I don't, when I exit, no file is created. So I am looking for a single command which would create an empty database for me without a need to create at least one table within that step.

Bowfin

You mean to create on iPhone? If you are looking for initializing database, take a look at raywenderlich.com/12170/… – Interlunar

sqlite3 a.db "" creates empty file – Inveigh

Answer 1

I don't think there is a way to do that in just one statement.

In Linux I would workaround it this way:

sqlite3 aFile.db "create table aTable(field1 int); drop table aTable;"

This will automatically create the needed file with the table in it and then drop it leaving the database file without tables. That's the closest thing I know.

Anyway, I think most editors will even accept an empty file too. Give that a try.

Corfu

this creates an empty file - thus, all editors will have to accept an empty file – Warbeck

Answer 2

Use the VACUUM command to create a valid empty SQLite database file, including the root database page and the database header.

sqlite3 file.db "VACUUM;"

Fairman

tried this and the selected answer - this is half the size! – Intercostal

That’s a good tip, but is it documented anywhere? – Dur

@Dur Kind of. The official documentation says: The VACUUM command rebuilds the database file. And in this specific case, we're just simply (re)building an empty database file. – Fairman

@Fairman I saw the documentation on VACUUM. I couldn’t find the part where the sqlite3 command could take the extra parameter. – Dur

That's documented here, where it says that "sqlite3 allows a single SQL command to be entered on the command line as a second argument after the database name". – Fairman

Answer 3

The simple way is in bash, use command

touch file.db

This will just create a 0 size file and can be used as an empty sqlite file.

Veratridine

Answer 4

You can also call .databases when you enter the command prompt.

Or:

sqlite3 test.db ".databases"

Godewyn

Answer 5

Just create an empty file.

> test.db

Symbol ">" here means redirection.

Gujarat

This actually works and does the same as all the complex answers above. For future compatibility, you might do ".databases", though... – Warbeck

Answer 6

Creating a blank db like this ( provided by mosty-mostacho ) has an advantage of being recognised as a valid SQLite db; better than an empty file.

$sqlite3 foo.db "create table t(f int); drop table t;"

Verify it by :

$file foo.db 
foo.db: SQLite 3.x database, last written using SQLite version 3024000

Halle

Answer 7

from https://sqlite.org/cli.html

Start the sqlite3 program by typing "sqlite3" at the command prompt, optionally followed by the name the file that holds the SQLite database (or ZIP archive). If the named file does not exist, a new database file with the given name will be created automatically. If no database file is specified on the command-line, a temporary database is created and automatically deleted when the "sqlite3" program exits.

my emphasis

Bipolar

Answer 8

For example, you can create the empty database mydb.sqlite3 with '' or "" as shown below. *My answer explains how to create a SQLite database:

sqlite3 mydb.sqlite3 ''

Or:

sqlite3 mydb.sqlite3 ""

Be careful, you cannot create the empty database mydb.sqlite3 without '' or "" as shown below:

sqlite3 mydb.sqlite3

Castellated