How to Create a SQLite In-Memory Database Using PHP PDO?

To create a SQLite in-memory database using PHP PDO, you must first and foremost provide the DSN (Data Source Name) to the PHP PDO object constructor as "sqlite::memory", for example, like so:

new PDO('sqlite::memory');

Following that, you may use the PDO object as you normally would. For example, to create a table (with some initial dummy data), you could do the following:

$db = new PDO('sqlite::memory');

$schema =<<<SQL
    CREATE TABLE `customer` (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name VARCHAR(25) NOT NULL,
        date_birth DATE NOT NULL
    );

    INSERT INTO customer(`name`, `date_birth`)
    VALUES
        ('John', '1978-01-01'),
        ('Wayne', '2015-08-01');
SQL;

$db->exec($schema);

And to run queries after, you could, for example, do the following:

$sth = $db->prepare('SELECT * FROM `customer`');
$sth->execute();
$result = $sth->fetchAll();

var_dump($result);
/* output: [
    [
        'id' => 1,
        'name' => 'John',
        'date_birth' => '1978-01-01'
    ],
    [
        'id' => 2,
        'name' => 'Wayne',
        'date_birth' => '2015-08-01'
    ]
] */

This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.