How to make SQLite work in Laravel

Question

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?

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

Answer 2

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

Answer 3

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

Answer 4

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

:)

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

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

Answer 10

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.

Answer 11

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.