How to create a new SQLite database?

Question

I am working on Mac OS X, I want to create a new SQLite DB, and I am using http://www.sqlite.org/quickstart.html as a reference.

I am entering: sqlite3 test.db and getting the response:

SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

Why is this happening?

Thay

Answer 1

Because that is the administrative shell prompt. The shell created test.db and is now waiting for you to do something with it thus the sqlite> prompt you see.

You may want to create table ... at this point.

Cranny

Also, use .exit to exit. Very useful to know! – Bollen

@JustCurious, test.db is stored in the folder where you issued the command. You can specify the full path of the database file as well, eg, sqlite3 /tmp/test.db. – Why

Actually, it will not create a db for you, until you create some table in it. You'll have to type sqlite3 test.db "". And, btw, it's just an empty file - there is nothing special in it. You can create it any way you want. – Mcfarlane

@Keeprock you are correct, over five years after I wrote that answer, version 3.8.11.1 2015-07-29 of sqlite3 test.db alone will not create an empty test.db. Thanks for the update. – Cranny

Answer 2

It worked. Quit out and test - the database has been created.

You are now in the SQLite shell, and it is ready to receive commands like CREATE TABLE..., INSERT INTO..., etc.

If you would prefer not to use the interactive shell, you can build your schema straight from the command line:

sqlite3 test.db "CREATE TABLE ..."

Or just create an empty database with no schema:

sqlite3 test.db ""

But that's the same as just creating an empty file, anyway:

touch test.db

Mime

Answer 3

so it is fine. you are prompted to enter commands. create table, insert, select. have fun!

Loran

Answer 4

You're being taken to the SQLite prompt (where you can create tables, add data, etc.). Nothing appears out of the ordinary based on what you've posted.

Braxton

Answer 5

For example, running SQLite3 with the new database name mydb.sqlite3 and creating the table person as shown below can create the new database mydb.sqlite3:

sqlite3 mydb.sqlite3
...
sqlite> CREATE TABLE person(id integer, name text);

And, you can do the above with one line of command as shown below:

sqlite3 mydb.sqlite3 'CREATE TABLE person(id integer, name text)'

Or:

sqlite3 mydb.sqlite3 "CREATE TABLE person(id integer, name text)"

Be careful, only running SQLite3 with the new database name mydb.sqlite3 as shown below cannot create the new database mydb.sqlite3:

sqlite3 mydb.sqlite3

In addition, you can create the empty database mydb.sqlite3 with '' or "" as shown below:

sqlite3 mydb.sqlite3 ''

Or:

sqlite3 mydb.sqlite3 ""

Em