CODEWITHSUNDEEP
sqlitelaravel-5.1laravel-5
Alex Lomia
Alex Lomia

Reputation: 7235

How to make SQLite work in Laravel

Whenever I run php artisan migrate, the following error is shown in the console:

[PDOException]
SQLSTATE[HY000] [14] unable to open database file

The database.sqlite file is located at database/. I'm running Windows 10, Laravel 5.2. Here is .env file config:

.env:

DB_CONNECTION=sqlite
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database
DB_USERNAME=homestead
DB_PASSWORD=secret

I have looked everywhere, but could not find what causes this error and how to resolve it.

Update

I managed to make migrations run successfully by replacing DB_DATABASE=database with DB_DATABASE=database/database.sqlite in .env file. However, new error occurs whenever I try to retrieve items from the database:

public function index()
{
    // cause of the error
    $cards = Card::all();

    return view('cards.index', compact('cards'));
}

The above action throws following error:

InvalidArgumentException in SQLiteConnector.php line 34:
Database (database/database.sqlite) does not exist.

The strange thing is, that the command Card::all() works flawlessly in php artisan tinker mode. What kind of magic is that?

Anyway, I've also found out, that the line:

'database' => env('DB_DATABASE', database_path('database.sqlite')),

in database.php file needs to be replaced with just database_path('database.sqlite') and everything starts to work normally.

It seems, that the root of the problem is env('DB_DATABASE') call. I went to SQLiteConnector.php file and dumped the output of both env('DB_DATABASE') and database_path('database.sqlite'). Here are their outputs respectively:

dd(env('DB_DATABASE'))               // => 'database/database.sqlite'
dd(database_path('database.sqlite')) // => 'D:\www\project\database\database.sqlite'

As you see, their output differs, and the second one is what is expected. Is this a Laravel bug? Or did I misunderstood something?

Upvotes: 39

Views: 76189

Answers (13)

vimuth
vimuth

Reputation: 5612

Just change DB_CONNECTION=mysql to DB_CONNECTION=sqlite and remove all other db related configurations in .env

Eg: DB_HOST=127.0.0.1, DB_PORT=3306

Then run

php artisan migrate

You can see the database inside,

database\database.sqlite

Upvotes: 0

blongho
blongho

Reputation: 1231

This is what worked for me

  1. Create the database file touch database/database.sqlite

  2. Specify database in .env like this

    DB_CONNECTION=sqlite
    #DB_HOST=127.0.0.1
    #DB_PORT=3306
    DB_DATABASE=database.sqlite
    #DB_USERNAME=root
    #DB_PASSWORD=
  1. Modify config/database.php like this

        'sqlite' => [
            'driver' => 'sqlite',
            'url' => env('DATABASE_URL'),
            'database' =>  database_path(env('DB_DATABASE')),
            'prefix' => '',
            'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
        ],
   ...
  1. Run the migration php artisan migrate

Those four steps were enough for me.

My setup: Windows 11, "php": "^8.1", "laravel/framework": "^10.10",

Upvotes: 0

Scott
Scott

Reputation: 8075

I found the following works rather well, if you prefer to use relative paths:

replace the 'database' line under SQLite with the following:

'database' => database_path(env('DB_DATABASE', 'database.sqlite')),

Upvotes: 1

caiohamamura
caiohamamura

Reputation: 2728

The problem is that php artisan migrate will use the DB_DATABASE from the root of the project, while serving the project with php artisan serve will run from the public directory.

The best solution for me is to create a link to database inside public directory:

  • UNIX: ln -s database public/database
  • Windows: mklink /J public\database database

Of course then you would have to deny access to database folder from HTTP requests but that should be easily done.

Upvotes: 0

keroles Monsef
keroles Monsef

Reputation: 741

  1. Create file called database.sqlite in this folder as database/database.sqlite

  2. Open the .env file and change MySQL to SQLite

  3. Comment password and username and databaseName using '#'

  4. run php artisan migrate enjoy

env file like this:

DB_CONNECTION=sqlite

#DB_HOST=127.0.0.1

#DB_PORT=3306

#DB_DATABASE=database

#DB_USERNAME=homestead

#DB_PASSWORD=secret

Upvotes: 26

Deepan Prabhu Babu
Deepan Prabhu Babu

Reputation: 912

This was the setting I had used,

'sqlite' => [
    'driver' => 'sqlite',
    'database' => database_path('database.sqlite'),
    'prefix' => '',
    'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
],

I also had to run touch database.sqlite in the database folder.

Upvotes: 0

motia
motia

Reputation: 1999

By using the relative path, migrations will fail because they use project directory as root directory...

To correct everything, I suggest setting:

   DB_DATABASE=database\database.sqlite

and tweaking the sqlite connections in config/database.php as follows:

  'database' => env('DB_DATABASE/..', database_path('database.sqlite')),

Original Answer

The .env file should contain this:

   DB_DATABASE=..\database\database.sqlite

With some testing you can verify that the link included in DB_DATABASE is relative to the 'public' directory (at least on my Windows machine). That's why we should introduce ..\ before your link.

And of course, using an absolute link should do it too:

   DB_DATABASE=D:\www\project\database\database.sqlite

as @Josh suggests

Upvotes: 22

Raghavendra N
Raghavendra N

Reputation: 3707

This worked for me in Laravel 5.5

  1. In .env file just have the connection name and remove all other DB_ related settings: DB_CONNECTION=sqlite_testing

  2. Define your sqlite settings in the config/database.php file:

    'connections' => [

    'sqlite_testing' => [
        'driver' => 'sqlite',
        'database' => database_path('testing-db.sqlite'),
        'prefix' => '',
    ],

    ...
]

My file is in the database/testing-db.sqlite.

Upvotes: 3

Gabriel Almeida
Gabriel Almeida

Reputation: 119

Complementing the awnser by our friend @alexander-lomia

Change:

'database' => env('DB_DATABASE', database_path('database.sqlite'))

To:

'database' => database_path(env('DB_DATABASE'))

in database.php

:)

Upvotes: 11

Diego Reinoso
Diego Reinoso

Reputation: 76

I got the same problem as you did. You have to use the absolute path in ENV file.

Please check the official documentation about this https://laravel.com/docs/5.4/database

DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite

Upvotes: 3

Rahi
Rahi

Reputation: 1485

Shortest and easiest solution is to remove default mysql settings in .env and work in database.php. That's what worked for me at least.

Remove the following...

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

Upvotes: 1

Josh Hartman
Josh Hartman

Reputation: 51

Instead of a relative path you need to use an absolute path in your .env file.

DB_DATABASE=/var/www/project/database/database.sqlite

or in your case:

DB_DATABASE=D:\www\project\database\database.sqlite

Upvotes: 4

Alex Lomia
Alex Lomia

Reputation: 7235

Short Solution

Though not answering the question, the way to fix "Database not found" issue is to replace the following line in database.php:

'database' => env('DB_DATABASE', database_path('database.sqlite')),

with 

'database' => database_path('database.sqlite'),

Upvotes: 36

Related Questions