Summary: in this tutorial, we will show you how to establish a connection to an SQLite database from PHP using PDO.
By default, PHP includes the SQLite extension so you don’t need to perform any configuration in PHP to make it work with the SQLite.
Setup PHP project structure with Composer
First, create project folder named phpsqliteconnect and another subfolder name app. The app folder is used to store all classes that deals with application logic and database.
Next, create a new composer.json file with the following code:
1 2 3 4 5 6 7 | { "autoload": { "psr-4": { "App\\": "app/" } } } |
If you are not familiar with Composer, you can check it out here. In PHP, we use Composer as a tool for dependency management. Composer allows us to declare the library that we use in our project and manage the update automatically.
In the composer file, we map the App namespace with the /app folder.
Then, create another subfolder name db to store the SQLite database file.
After that, open the command tool, navigate to the phpsqliteconnect, and type the following command:
1 | >composer update |
The following message will display:
1 2 3 4 5 | >composer update Loading composer repositories with package information Updating dependencies (including require-dev) Nothing to install or update Generating autoload files |
In addition, Composer also creates the new folder named vendor as shown in the screenshot below:
Finally, create a file named index.php in the root folder, and add the following code:
1 2 | <?php require 'vendor/autoload.php'; |
From now on, if we want to use any class in the app folder, we just need to declare and use it.
Establish database connection to an SQLite database
First, create a new file Config.php inside the app folder and add a new class named Config as follows:
1 2 3 4 5 6 7 8 9 10 11 | <?php namespace App; class Config { /** * path to the sqlite file */ const PATH_TO_SQLITE_FILE = 'db/phpsqlite.db'; } |
The constant PATH_TO_SQLITE_FILE is used to store the path to the sqlite database file which resides in the db folder.
Second, create a new SQLiteConnection.php file and add the SQLiteConnection class as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | <?php namespace App; /** * SQLite connnection */ class SQLiteConnection { /** * PDO instance * @var type */ private $pdo; /** * return in instance of the PDO object that connects to the SQLite database * @return \PDO */ public function connect() { if ($this->pdo == null) { $this->pdo = new \PDO("sqlite:" . Config::PATH_TO_SQLITE_FILE); } return $this->pdo; } } |
To establish a database connection to an SQLite database, we need to create a new instance of the PDO class and pass a connection string to the constructor of the PDO object.
Suppose the SQLite database file is in the db folder, we use the following connection string:
1 | sqlite:db/phpsqlite.db |
Because we store the path to the sqlite database file in the Config class, we just use it to construct the connection string.
Notice that when PHP connects to an SQLite database, if it does not exist, PHP will create a new SQLite database file.
The $pdo is used to store the instance of the PDO object. In the connect() method, we check if a database connection has been established. If not, we create a new instance of the PDO object otherwise, we return the PDO object.
After having all classes in places, you use the following command to generate the autoload file:
1 | >composer dump-autoload -o |
To establish the connection to the SQLite database, we use the following code in the index.php file:
1 2 3 4 5 6 7 8 9 10 11 | <?php require 'vendor/autoload.php'; use App\SQLiteConnection; $pdo = (new SQLiteConnection())->connect(); if ($pdo != null) echo 'Connected to the SQLite database successfully!'; else echo 'Whoops, could not connect to the SQLite database!'; |
If you check the db folder, you will see a file with the name phpsqlite.db created.
When you create a new instance of the PDO, it will always throw a PDOException if the connection fails.
To handle the exception you can use the try catch block as follows:
1 2 3 4 5 | try { $this->pdo = new \PDO("sqlite:" . Config::PATH_TO_SQLITE_FILE); } catch (\PDOException $e) { // handle the exception here } |
In this tutorial, we have shown you how to setup the PHP project structure and establish a database connection to an SQLite database.