Using CodeIgniter 2 with SQLite 3 databases

Note: If you haven’t updated to CodeIgniter 2.1.1 – do so. There is a bug in 2.1 preventing it from working with SQLite databases properly, see the changelog.

The process of connecting to a SQLite 3 database with CodeIgniter isn’t straightforward. You have to use the (built-in) PDO driver and put the path to the database, with protocol prefix, in the hostname parameter, here’s an example:

/application/config/database.php

...
$db['default']['hostname'] = 'sqlite:'.APPPATH.'db/your_database.sqlite';
$db['default']['username'] = '';
$db['default']['password'] = '';
$db['default']['database'] = '';
$db['default']['dbdriver'] = 'pdo';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;
...

This assumes you have your database under:

/application/db/your_database.sqlite

If the file does not exist, an empty database will be created at that location.

After you have connected, you can use the Active Record class normally to query the db, for example:

$query = $this->db->get('users');
foreach ($query->result() as $row)
{
    echo $row->id . '<br/>';
}

Remember that you need to build your table structures first with a tool such as phpLiteAdmin.


Why does the syntax differ when conecting to a SQLite 3 database?

The reason you have to put the database path in the hostname field is the way CI instantiates the PDO object:

system/database/drivers/pdo/pdo_driver.php

...
function db_connect()
{
    $this->options['PDO::ATTR_ERRMODE'] = PDO::ERRMODE_SILENT;
    return new PDO($this->hostname, $this->username, $this->password, $this->options);
}
...
Advertisements
Explore posts in the same categories: Computers, Development, Technical solutions

Tags: , , ,

You can comment below, or link to this permanent URL from your own site.

11 Comments on “Using CodeIgniter 2 with SQLite 3 databases”


  1. […] a Stanislav Khromov por dar con la solución. Categories: CodeIgniter, Linux, PHP, SQLite Tags: codeigniter, php, sqlite Comments (0) […]

  2. Brian Shim Says:

    Stanislav, thank you for this! It solved my problem. The CI documentation does not correctly describe how to use SQLite.

    – Brian

  3. Carl Says:

    Thanks man, the documents about this problem at ci.com sucks…

  4. Vinay Says:

    Hey! Thanks for this useful post.

  5. slobaum Says:

    Seriously, thank you very much. Saved me from pulling my hair out.

  6. mohamed Says:

    this saved my day , god bliss you 🙂

  7. Sipho Mfungi Says:

    Thanks for the wonderful tip Stanislav.

  8. Arielle Cruz Says:

    Thank you for this. I would also like to point out to my fellow Ubuntu users that may find themselves here that they need to:

    sudo apt-get install php5-sqlite

    Otherwise, the PDO driver will throw an exception. Cheers!

  9. Curzon Says:

    i can connect to the database but i can not retrieve data. what to do ?

  10. Arun Says:

    how can i use pdo predefined functions i codeigniter


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s