68

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.

Improve this question
2
  • You mean to create on iPhone? If you are looking for initializing database, take a look at raywenderlich.com/12170/… Commented Nov 22, 2013 at 22:38
  • 3
    sqlite3 a.db "" creates empty file Commented Sep 3, 2018 at 22:26

8 Answers 8

Reset to default
113

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;"
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

tried this and the selected answer - this is half the size!
That’s a good tip, but is it documented anywhere?
@Manngo 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.
@emkey08 I saw the documentation on VACUUM. I couldn’t find the part where the sqlite3 command could take the extra parameter.
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".
44

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.

Improve this answer

1 Comment

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

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.

Improve this answer

Comments

28

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

Or:

sqlite3 test.db ".databases"
Improve this answer

Comments

21

Just create an empty file.

> test.db

Symbol ">" here means redirection.

Improve this answer

1 Comment

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

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
Improve this answer

Comments

0

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

Improve this answer

Comments

0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.