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?

回答1:

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'),

回答2:

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

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

Update

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')),

回答3:

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

:)

回答4:

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

回答5:

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

回答6:

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.

回答7:

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