SQLite Forum

Creating an emty database
Login

Creating an emty database

(1) By oneeyeman on 2020-12-26 00:44:17 [source]

Hi, ALL,

Is there a way to create an empty database on the disk?

I know that when you just start the CLI interface the empty "in-memory database" will be created. But is there a way to save this database on the disk?

Thank you.

(2) By Stephan Beal (stephan) on 2020-12-26 00:57:55 in reply to 1 [link] [source]

Is there a way to create an empty database on the disk?

$ ./sqlite3 foo.db
SQLite version 3.34.0 2020-08-28 11:19:49
Enter ".help" for usage hints.
sqlite> .exit
$ l foo.db
ls: cannot access 'foo.db': No such file or directory

Apparently you have to perform some write operation first:

$ ./sqlite3 foo.db
SQLite version 3.34.0 2020-08-28 11:19:49
Enter ".help" for usage hints.
sqlite> create table t(a); drop table t;
sqlite> .exit
$ l foo.db
-rw-r--r-- 1 stephan stephan 8192 Dec 26 01:56 foo.db

(3) By Keith Medcalf (kmedcalf) on 2020-12-26 01:11:39 in reply to 1 [link] [source]

See the .save command.

SQLite version 3.35.0 2020-12-25 00:58:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .save test.db
sqlite> .help save
.save FILE               Write in-memory database into FILE
sqlite> .exit

>dir test.db
2020-12-25  18:06             4,096 test.db

and actually, no database is created or opened until you access it. Note also that .save works to save whatever database is currently open with the schema name "main", it does not have to be an in-memory database.

(4) By Richard Damon (RichardDamon) on 2020-12-26 01:16:00 in reply to 3 [link] [source]

An alternative to using .save is do something that actually will access the tables, like .tables

(5) By luuk on 2020-12-27 08:11:46 in reply to 4 [link] [source]

.tables will create a zero-length file:

D:\TEMP>sqlite3 foo.sqlite
-- Loading resources from C:\Users\Luuk/.sqliterc
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .tables
sqlite> .quit

D:\TEMP>dir foo.sqlite
 Volume in drive D is HDD
 Volume Serial Number is D46B-804B

 Directory of D:\TEMP

27-12-2020  09:10                 0 foo.sqlite
               1 File(s)              0 bytes
               0 Dir(s)  672.552.960.000 bytes free

D:\TEMP>

(6) By anonymous on 2020-12-29 07:21:50 in reply to 2 [link] [source]

VACUUM is sufficient (according to my testing); you do not need to create and drop a table.