How to use sqlite database on symfony2 project?

Question

In a Symfony2 project, you can configure the databases connections at the app/config/parameters.ini file. Documentation states that you can use, among others, sqlite3 PDO driver.

But configuring sqlite doesn't works well:

[parameters]
    database_driver   = pdo_sqlite
    database_host     = localhost
    database_port     =
    database_name     = test_project.db
    database_user     = root
    database_password = 

Using app/console doctrine:database:create, successfully creates a test_project.db file at the project root directory.

But after creating some entities, then running app/console doctrine:schema:update --force should create the tables on the database file, but it doesn't, file appears empty, with O bytes size.

Note that using any other PDO driver works well, but not with SQLite...

I've also tried to use the full path for the db file in the database_name parameter, but to no avail, database still doesn't gets updated.

For reference, here's the doctrine dbal section of the config.yml file:

doctrine:
    dbal:
        driver:   %database_driver%
        host:     %database_host%
        port:     %database_port%
        dbname:   %database_name%
        user:     %database_user%
        password: %database_password%
        charset:  UTF8

Is there a way around this? configurations missing? something not stated on the official doc of symfony2 project?

Answer 1

According to Doctrine the elements used for sqlite DBAL configuration are:

• user (string): Username to use when connecting to the database.
• password (string): Password to use when connecting to the database.
• path (string): The filesystem path to the database file. Mutually exclusive with memory. path takes precedence.
• memory (boolean): True if the SQLite database should be in-memory (non-persistent). Mutually exclusive with path. path takes precedence.

This is also listed in the full reference for Doctrine configuration in Symfony2, although not elaborated on.

So you need to switch up your config params to match whats appropriate for sqlite.

Answer 2

Here is what I needed to get SQLite to work, just after doing symfony new myapp :

in app/config.yml :

# Doctrine Configuration
doctrine:
    dbal:
        driver:   pdo_sqlite
        path:     "%database_path%"

In app/config/parameters.yml:

parameters:
    database_path: "%kernel.root_dir%/db/myapp_%kernel.environment%.db3"
    ...

Next I could do a composer install, create a new entity and it just worked.

Answer 3

I've found that if I add a path line pointing at the database_name to my config.yml, sqlite seems to pick that up, and MySQL doesn't seem to complain.

doctrine:
    dbal:
        driver:   %database_driver%
        host:     %database_host%
        port:     %database_port%
        dbname:   %database_name%
        path:     %database_name%
        user:     %database_user%
        password: %database_password%
        charset:  UTF8

This means you can still keep all database information in the parameters file, you don't need separate configs depending on which database you are using.

Answer 4

Mainly the file path or the file path permisssion will have issue.

In config.yml, set path to full path like

/home/{name}/NB/PHP/Symfony/test/src/Database/data.db3

Dont give %database_path% or what ever. Try this it will work.

If it works you can give as

%kernel.root_dir%/../src/Database/%database_path%

Also check sqlite is ok by

phpinfo(INFO_MODULES);

In view/output you can see pdo_sqlite and its version.

Answer 5

In my case setting a username and password in config/packages/doctrine.yaml did not create a username/password protected sqlite database.

doctrine:
    dbal:
        charset:  UTF8
        url: '%DATABASE_URL%'
        user:     'foo'
        password: 'bar'

It seems like the parameters username and password are ignored?