SQLite - Create a Database
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:
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:
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> .databases seq 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:
Then we can review our list of databases using the .databases
command:
sqlite> .databases seq name file --- --------------- ---------------------------------------------------------- 0 main /Users/quackit/sqlite/music.db 2 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.