SQLite - CREATE Database

SQLite - CREATE Database

In SQLite, you create a database by simply specifying a filename for it. The sqlite3 command-line tool is used for this purpose. No special server process or permissions are required.

Syntax

The basic syntax to create a database is:

$ sqlite3 DatabaseName.db

If the file DatabaseName.db does not exist, SQLite will create it. If it already exists, SQLite will open it.

Example

Let’s create a new database named company.db.

$ sqlite3 company.db
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite>

This command creates a file named company.db in the current directory and opens the SQLite command-line shell, indicated by the sqlite> prompt. This file is your database.

Inside the shell, you can use the .databases command to see all attached databases. The main database is the one you just opened.

sqlite> .databases
seq  name             file
---  ---------------  --------------------------
0    main             /path/to/your/company.db

To exit the SQLite shell, use the .quit command:

sqlite> .quit
$

Important Note: When a web application (like one written in PHP) needs to create or write to a database, the web server’s user (e.g., www-data on Debian/Ubuntu, apache on CentOS) must have write permissions on the directory containing the database file.

Backing Up with the .dump Command

A powerful feature of the sqlite3 tool is the .dump command. It exports the entire database schema and data as a plain text SQL script. This is an excellent way to create human-readable backups.

$ sqlite3 company.db .dump > company_backup.sql

This command creates a text file named company_backup.sql containing all the CREATE TABLE and INSERT statements needed to rebuild the database.

You can easily restore the database from this backup file:

$ sqlite3 new_company.db < company_backup.sql

This technique is invaluable for migrations, backups, and version controlling your database schema.