How to create a database in SQLite.

SQLite does not use the CREATE DATABASE statement like in other database management systems, such as MySQL, SQL Server, etc.

SQLite gives you the option of creating a new database (or opening an existing one) every time you start the command-line utility. When you use sqlite3 to start the command-line utility, you can optionally append a database file name. If a database exists of that file name, it will be opened. Otherwise it will be created.

So in other words, to create a new database in SQLite, simply enter sqlite3 followed by the name of the file that you wish to use for the database.

The following code creates a database file called music.db:

sqlite3 music.db;

The above code creates the database file in the current directory. To create it in a different directory, simply use the full path.

For example:

sqlite3 /Users/barney/music.db;

SQLite commands end with a semi-colon (;). This tells SQLite that your command is complete and should be run.

If you don't include the semi-colon, you will see a continuation prompt, like this ...> which means that SQLite is waiting for you to enter more stuff. Simply add the semi-colon and press enter to run the command.

Alternatively, you can spread your command across multiple lines and use the semi-colon on the last line.

Check that the Database was Created

You can check that the database was created by using the .databases command:

sqlite> .databasesseq  name             file---  ---------------  ----------------------------------------------------------0    main             /Users/quackit/sqlite/music.db

You can also navigate to the directory to see the file on the file system if you wish.

You'll notice that the above example begins with sqlite>. This is simply the SQLite command prompt. The actual command entered was .databases.

You'll also notice that there is no semi-colon at the end of the command. This is because "dot-commands" (such as .databases) don't require the semi-colon at the end. Dot-commands are interpreted by the command-line utility and not by SQLite itself.

The database file is just a blank file right now. Next, we'll add a table to the database.

Attach a Database

You can also use the ATTACH DATABASE command to create a database from within the SQLite3 command utility.

When you use this command, provide a file name for the database, as well as an alias for the database. If the file already exists, it will attach that database file. Otherwise a file will be created.

Here's an example of using ATTACH DATABASE to create a database:

ATTACH DATABASE 'movies.db' AS Movies;

Then we can review our list of databases using the .databases command:

sqlite> .databasesseq  name             file---  ---------------  ----------------------------------------------------------0    main             /Users/quackit/sqlite/music.db2    Movies           /Users/quackit/sqlite/movies.db

The ATTACH DATABASE command attaches the database to the current connection.

If your .database command doesn't return a database that you know exists, it probably just needs to be attached to the current connection.