Phpunit configuration refresh my database

I think one of your migrations is incorrect. Can you show us the migration with the project_id column in it?

The error message states that it's tryibng to add a foreign key constraint on your tasks table for the project_id column. So look for this code in your migration.

 $table->foreign('project_id')
                  ->references('id')
                  ->on('projects');

And make sure that you create that field in the migration.

I fixed migrations and the error changed to:

1) Tests\Unit\ExampleTest::testBasicTest
Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 no such table: users (SQL: select * from "users" where "active" = 1 order by "activity_level" desc)

The database is not resetetting anymore, so I suspect that the problem is that the testing database is empty. So the question is how to run migrations for it and shouldn`t it happen automatically when running tests?

To migrate the database each time you run a test you need to use the

use DatabaseMigrations;

First of all,DatabaseMigrations triggers php artisan migrate command and before the application is destroyed, it rolls everything back, running all of your migrations rollback commands, and if they are all correct it will destroy all of your tables, which should not be causing any problems.

Judging from the type of error you are receiving, it seems to be that there is still some faulty code in your migrations that fails to create the users table. Have you changed the users migration?

Another thing you should note (that I've experienced in the past) is that having raw SQL queries might sometimes fail to execute in SQLite.

@WIZJO - It seems that your user migration table have some issue. May be you can share your code.

Meanwhile, you can do this for refreshing your database.

class ExampleTest extends TestCase
{
    use DatabaseMigrations;
    use RefreshDatabase;

@tray2 : As you can see in my code listing at the top that trait is already in code.

@dime_galev & @tisuchi Yes, I add some additional fields to it, but I dont see any place which could cause problems:

2018_01_10_203901_create_users_table.php

    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->integer('role_id')->references('id')->on('roles')->onDelete('cascade')->unsigned()->default(4);
            $table->string('name', 50)->nullable();
            $table->string('lastname', 50)->nullable();
            $table->string('email', 50)->nullable()->unique();
            $table->string('phone', 50)->nullable();
            $table->string('postal', 10)->nullable();
            $table->string('city', 200)->nullable();
            $table->string('address', 250)->nullable();
            $table->string('nip', 15)->nullable();
            $table->string('pesel', 15)->nullable();
            $table->integer('hour_rate')->unsigned()->nullable();
            $table->boolean('active')->unsigned()->default(1);
            $table->string('password', 191)->nullable();
            $table->text('notes')->nullable();
            $table->rememberToken();
            $table->integer('activity_level')->unsigned()->default(0);
            $table->timestamps();
        });
    }

You are setting a boolean as unsigned.

Try changing

$table->boolean('active')->unsigned()->default(1);

To

$table->boolean('active')->default(1);

@wizjo I've just tested your users migration on a clean laravel project and it passed without any errors, I guess the problem lies somewhere else. What happens when you delete all tables manually and run the php artisan migrate on a clean database? Do you still get the same error?

@tray2 : unsigned() at boolean seams not to be a problem

@dime_galev : I got error:

In Connection.php line 664:

  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'wizjo_crm.users' doesn't exist (SQL: select * from `user
  s` where `active` = 1 order by `activity_level` desc)


In PDOConnection.php line 82:

  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'wizjo_crm.users' doesn't exist


In PDOConnection.php line 80:

 SQLSTATE[42S02]: Base table or view not found: 1146 Table 'wizjo_crm.users' doesn't exist

Perhaps a certain clue will be that I did not make migrations on a regular basis. At some point I stopped doing them and created tables "manually" in phpmyadmin. At the moment when someone said that I need migrations to tests - I created them. In addition, because I thought that the order of migrations may be significant, I manipulated the filenames so that those that according to me should be first (because others have indexes related to them) had the earliest dates in the names.

Here is the list of filenames of my migrations:

2018_01_09_203901_create_roles_table.php
2018_01_10_203901_create_users_table.php
2018_01_10_203902_create_companies_table.php
2018_01_10_205034_create_websites_table.php
2018_01_13_124753_create_company_user_pivot_table.php
2018_01_16_193644_create_issuers_table.php
2018_01_20_152514_create_project_user_pivot_table.php
2018_01_23_185307_create_tasks_table.php
2018_01_23_191128_create_task_user_pivot_table.php
2018_01_23_191221_create_comments_table.php
2018_11_20_235120_create_cache_table.php
2018_12_22_143752_create_meetings_table.php
2018_12_22_150250_create_meeting_types_table.php
2018_12_26_150607_create_expenses_table.php
2019_05_29_161218_create_projects_table.php
2019_05_29_161243_create_categorables_table.php
2019_05_29_161308_create_categories_table.php
2019_05_29_161348_create_files_table.php
2019_05_29_161414_create_invoices_table.php
2019_05_29_161437_create_invoice_positions_table.php
2019_05_29_161502_create_invoice_relations_table.php
2019_05_29_161530_create_jobs_table.php
2019_05_29_161624_create_permissions_table.php
2019_05_29_161725_create_procedures_table.php
2019_05_29_161816_create_reminders_table.php
2019_05_29_161857_create_statuses_table.php
2019_05_29_162026_create_valuations_table.php
2019_05_29_162106_create_permission_role_pivot_table.php
2019_05_29_162120_create_procedure_user_pivot_table.php
2019_05_29_162126_create_reminder_user_pivot_table.php

@wizjo I believe that you won't be able to run any tests with an in-memory database until you are sure that running php artisan migrate on an empty database corresponds with your current database.

Well, at least you now know what the issue is, and consider not skipping migrations in future, even when you are working alone on a project.

I hope you manage to fix your migrations. :)

That error message clearly states that your users table does not exist in your database.

What I would do is create a new laraval project and a new database for it and setup the .env to match the new database, copy the migrations to the new project and run `php artisan migrate. then take a look and see if all the tables are migrated properly. The migration process will blow up if something is wrong wirth it.

If the migration goes well and all tables are in the database then there is something wrong in your code in your test, controller or model.

@dime_galev & @tray2 :

I created fresh Laravel project and solved all migration problems (btw. it was quite strange that I have to change "foreign" method to "integer" in my migrations, otherwise I got an error), but when moving back my files to my root project the error doesn`t change.

Digging deeper I noticed that the problem was in AppServiceProvider.php with this line:

View::share('all_users', User::mostActive()->where('active', '=', 1)->get()->pluck('full_name_company_email', 'id'));

Commenting this out moves me one step further, so the error changes to almost the same, but concering another table:

In Connection.php line 664:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'wizjo_crm.permissions' doesn't exist (SQL: select * from `permissions`) In PDOConnection.php line 82:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'wizjo_crm.permissions' doesn't exist

In PDOConnection.php line 80:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'wizjo_crm.permissions' doesn't exist

I found out that commenting out the content of my app\Providers\AuthServiceProvider.php solves finally problems with migrations (runnig command: php artisan migrate):

class AuthServiceProvider extends ServiceProvider
{
    /**
     * The policy mappings for the application.
     *
     * @var array
     */
    protected $policies = [
        'App\Model' => 'App\Policies\ModelPolicy',
    ];

    /**
     * Register any application authentication / authorization services.
     *
     * @param  \Illuminate\Contracts\Auth\Access\Gate  $gate
     * @return void
     */
    public function boot(GateContract $gate)
    {
        parent::registerPolicies($gate);

        // Dynamically register permissions with Laravel's Gate.
        foreach ($this->getPermissions() as $permission) {
            $gate->define($permission->name, function ($user) use ($permission) {
                return $user->hasPermission($permission);
            });
        }
    }

    /**
     * Fetch the collection of site permissions.
     *
     * @return \Illuminate\Database\Eloquent\Collection
     */
    protected function getPermissions()
    {
        return Permission::with('roles')->get();
    }
}

...so now my tests run correctly, but I have additional 2 problems connected with code below which I have to comment out:

1. How to share between all views data about all users in database (I need it for displaying select in my CRM which allows me to quick login to any user account).
2. I have roles and permissions table and AuthServiceProvider needs tables to be already migrated.

To share data bewween all view https://laravel.com/docs/master/views#sharing-data-with-all-views

Sharing data with all views

Sharing data with only one view file

Authorization

@tray2 & @dime_galev But, it is exactly what I have in my code and that cause problem with tests...