SQLite PHP: Connecting to SQLite Databases

Summary: in this tutorial, you will learn how to connect to an SQLite database from PHP using PDO.

PHP includes the SQLite extension by default, so you don’t need to configure it to work with SQLite.

Creating a new SQLite database

Step 1. Open your terminal and create a new project directory in your web server:

mkdir php-sqlite
cd php-sqliteCode language: PHP (php)

Step 2. Create a new directory called database to store the SQLite database file:

mkdir databaseCode language: PHP (php)

Step 3. Create a new file config.php to store the path to the SQLite database file:

<?php

$db = './database/my.db';Code language: PHP (php)

In the config.php file, we define a variable $db that stores the path to the SQLite file in the database directory.

Step 4. Create the connect.php file with the following code:

<?php

require_once 'config.php';

$dsn = "sqlite:$db";

try {
    $pdo = new \PDO($dsn);
    echo 'Connected to the SQLite database successfully!';
} catch (\PDOException $e) {
    echo $e->getMessage();
}Code language: PHP (php)

How it works.

First, load the config.php file using the require_once construct:

require_once 'config.php';Code language: PHP (php)

The require_once will run the config.php that brings the $db variable into the connect.php file.

Second, define a variable $dsn that stores the data source name of the SQLite database file:

$dsn = "sqlite:$db";Code language: PHP (php)

SQLite dsn consists of the following elements:

• Prefix with sqlite:
• Path to the database file for example: "./database/my.db"

The dsn for the SQLite database file located at "./database/my.db" will be:

sqlite:./database/my.dbCode language: PHP (php)

To create this dsn, we leverage the string interpolation by placing the $db variable in the double-quoted string:

"sqlite:$db";Code language: JavaScript (javascript)

At runtime, the dsn will start with sqlite: and the path to the SQLite file.

If you want to create an SQLite database in memory, you can use the literal string ':memory:' as the dsn:

$dsn = ':memory:';Code language: PHP (php)

Third, create a new instance of the PDO class:

$pdo = new \PDO($dsn);Code language: PHP (php)

Since the my.db database file does not exist, PHP will create it. Otherwise, PHP will open a connection to the SQLite database file.

An error may occur during the connection process. To handle it properly, you can use a try-catch statement:

try {
   $pdo = new \PDO($dsn);
   echo 'Connected to the SQLite database successfully!';
} catch (\PDOException $e) {
   echo $e->getMessage();
}Code language: PHP (php)

If any error occurs in the try block, the execution stops and immediately jumps to catch block.

The PDOException instance $e will store the error detail including the error message. In this example, we display the error message. In practice, you can log it to a file and display a user-friendly message to the users.

Step 5. Open the connect.php file in your web browser, you’ll likely see a success message:

Connected to the SQLite database successfully!Code language: PHP (php)

If you examine the database directory, you’ll see the my.db database file created by the script.

Opening a new database connection

If the SQLite database file already exists, PHP will open a new connection instead of creating a new one.

If you already have an SQLite database file, you can do these steps:

Step 1. Copy your SQLite database file to the database directory.

Step 2. Change the $db variable in the config.php file to the path to the SQLite database file.

Step 3. Run the connect.php file.

Summary

• SQLite data source name (dsn) includes sqlite: prefix and a path to the SQLite database file.
• Create an instance of the PDO class to open a connection to an existing SQLite database file or create a new database file if it does not exist.