Database Flashcards
What are migrations used for?
Defining the application’s database schema and making it sharable
Which facade is typically used for database agnostic support for creating and manipulating tables across all of Laravel’s supported database systems?
Schema
Create a migration using Artisan!
php artisan make:migration create_examples_table
How do you run all of your outstanding migrations?
php artisan migrate
Use Artisan to see the SQL statements, that would be executed by the migrations, without actually running them!
php artisan migrate –pretend
How can you stop multiple servers from trying to migrate the same database at the same time, if your application is running across multiple servers?
php artisan migrate –isolated
What do you need to do to roll back the latest migration operation?
php artisan migrate:rollback
Roll back the last 3 migrations!
php artisan migrate:rollback –step=3
Use Artisan to see the SQL statements, that would be executed by a rollback, without actually rolling back!
php artisan migrate:rollback –pretend
Which command rolls back all of your applications migrations?
php artisan migrate:reset
How can you roll back and remigrate your database in one command?
php artisan migrate:refresh
Drop all tables from your database and then migrate and seed it in one command!
php artisan migrate:fresh –seed
Which method of the Schema facade should you use to make a new database table?
Schema::create
Create a database table, which includes an id, 2 strings and time stamps!
Schema::create('example', function (Blueprint $table) {
$table->id();
$table->string('example1');
$table->string('example2');
$table->timestamps();
});How can you check if a database includes a table / column / index?
Schema::hasTable('exampletable')
Schema::hasColumn('exampletable', 'examplecolumn')
Schema::hasIndex('exampletable', ['examplecolumn'], 'exampleindex')Create a table that uses the “InnoDB” engine!
Schema::create('example', function (Blueprint $table) {
$table->engine('InnoDB');
// ...
});Which method do you need to use to perform a schema operation on a database connection that is not your application’s default connection?
Schema::connection('sqlite')->create...
(just using sqlite as an example)
Specify the charset encoding as “utf8mb4” for a database during its creation!
Schema::create('example', function (Blueprint $table) {
$table->charset('utf8mb4');
// ...
});What is the main characteristic, that a temporary table has?
A table, that was created with the temporary method, drops all tables once the connection is closed
How can you add a comment to a table within the Schema::create?
$table->comment('Business calculations');
Add the integer “score” to an already existing table!
Schema::table('example', function (Blueprint $table) {
$table->integer('score');
});How can you rename a table in an existing database?
Schema::rename($from, $to);
How can you drop an existing table?
Schema::drop('example');
alternativelySchema::dropIfExists('example');
What does the boolean() method do?
The boolean method creates a BOOLEAN equivalent column:
$table->boolean('confirmed');
What does the char() method do?
The char method creates a CHAR equivalent column with of a given length:
$table->char('name', length: 100);
What does the dateTime() method do?
The dateTime method creates a DATETIME equivalent column with an optional fractional seconds precision:
$table->dateTime('created_at', precision: 0);
What does the dateTimeTz() method do?
The dateTimeTz method creates a DATETIME (with timezone) equivalent column with an optional fractional seconds precision:
$table->dateTimeTz('created_at', precision: 0);
What does the date() method do?
The date method creates a DATE equivalent column:
$table->date('created_at');
What does the foreignId() method do?
The foreignId method creates an UNSIGNED BIGINT equivalent column:
$table->foreignId('user_id');
What does the id() method do?
The id method is an alias of the bigIncrements method. By default, the method will create an id column; however, you may pass a column name if you would like to assign a different name to the column:
$table->id();
What does the ipAdress() method do?
The ipAddress method creates a VARCHAR equivalent column:
$table->ipAddress('visitor');
When using PostgreSQL, an INET column will be created.
What does the rememberToken() method do?
The rememberToken method creates a nullable, VARCHAR(100) equivalent column that is intended to store the current “remember me” authentication token:
$table->rememberToken();
What does the set() method do?
The set method creates a SET equivalent column with the given list of valid values:
$table->set('flavors', ['strawberry', 'vanilla']);
What does the softDeletes() method do?
The softDeletes method adds a nullable deleted_at TIMESTAMP equivalent column with an optional fractional seconds precision. This column is intended to store the deleted_at timestamp needed for Eloquent’s “soft delete” functionality:
What does the timestampTz() method do?
The timestampTz method creates a TIMESTAMP (with timezone) equivalent column with an optional fractional seconds precision:
$table->timestampTz('added_at', precision: 0);
Create a table called “users” , which includes a column “name” and make the column default to “guest” if nothing else is provided!
Schema::create('people', function (Blueprint $table) {
$table->id();
$table->string('name')->default('guest');
// ...
});While adding columns to an already existing table and already being inside the Schema, add the string column “address” after the column “name”!
$table->after('name', function (Blueprint $table) {
$table->string('address');
});What do you need to do to create a string column in a table with a size limit to the string?
$table->string('example', $size);
Given a table with a column with a string “name” that has a size limit of 30, increase the size limit to 50!
(within Schema::table)$table->string('name', 50)->change();
ALWAYS INCLUDE ALL ATTRIBUTES, EVEN THE ONES YOU DON’T CHANGE THE VALUE OF OR THEY WILL BE DELETED!!!
Within a Schema::table, how do you rename a column?
$table->renameColumn('from', 'to');
Within a Schema::table, how can you drop columns?
$table->dropColumn('example'); <- Drops one column
$table->dropColumn(['example1', 'ex2', 'ex3']);How can you force the input values for columns to be unique?
$table->string('example')->unique();
If you have already defined a column, how can you make it unique after the fact?
$table->unique('examplecolumn');
Pass an array of columns to an index!
$table->index(['example1', 'example2']);
Rename an index!
$table->renameIndex('from', 'to');
Which method should you use to create a foreign key constraint?
$table->foreignId('example_id')->constrained();
Why should you use foreign key constraints?
They force referential integrity at a database level
If the foreignId that a column of a different table is referencing gets updated or deleted, how can you ensure the updating or deletion of the column in your other table?
While setting up the contrained foreignId, also include:
~~~
$table->foreignId(‘example_id’)
->contrained()
->onUpdate(‘cascade’)
->onDelete(‘cascade’);
~~~
Drop the foreignId of the column “example_id”!
$table->dropForeign(['example_id']);
What is pagination?
The dividing of content in a website into separate pages.
For example, if a website is showing you 500 results to a search query but only displays 20 per page, splitting the results up across multiple pages, that’s pagination.
Split up the amount of results of a query into multiple subpages using pagination! Include 15 results per page!
INSERT QUERY->paginate(15)
also works with Eloquent results, doesn’t need to be a query
Why should you sometimes use paginate() and sometimes simplePaginate()?
Simple pagination only displays a “next” and “previous” link in your application’s UI, while normal pagination also includes how many pages of records there are in total and which specific one you are currently on. For things like your e-mails mailbox it makes more sense to paginate(), but for things way bigger in size it becomes nonsensical.
If multiple paginators are on a single screen and two or more of them use the page as the query string parameter, then there will be a conflict. How could you resolve this?
To resolve this conflict, you may pass the name of the query string parameter you wish to use to store the paginator’s current page via the third argument provided to the paginate, simplePaginate, and cursorPaginate methods:
use App\Models\User;
$users = User::where('votes', '>', 100)->paginate(
$perPage = 15, $columns = ['*'], $pageName = 'users'
);
Which type of pagination should you use for “infinite” scrolling user interfaces?
cursorPaginate();
Which type of pagination uses the lowest amount of resources?
cursorPaginate();
What do you need to do to add something to the end of a paginated link?
add “sort=votes” to the end of $users!
(in the Route::get)$users->appends(['sort' => 'votes']);
Return the JSON of “/examples” in the model “Example”
Route::get('/examples', function() {
return Example::paginate();
);How do you export your pagination views using Artisan?
php artisan vendor:publish –tag=laravel-pagination
Where can you designate a different file as the default pagination view?
In the boot() method of App\Providers\AppServiceProvider
Which method do you need to use to begin a query in the DB facade? Also add the needed method to retrieve the results of the query!
DB::table('example')->get()
Retrieve all “users” from the DB facade and output their names!
$users = DB::table('users')->get();
foreach ($users as $user) {
echo $user->name;
}Return the “email” of the first user in the table “users” that has the “name” “John”!
$user = DB::table('users')->where('name', 'John')->first();
return $user->email;In a big database processing data from there becomes very time consuming, so splitting the task up becomes mandatory at some point. Optimize this code bit, so that it only processes 100 users at a time:
DB::table(‘users’)->orderBy(‘id’) {
foreach ($users as $user) {
// …
}
});
DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
foreach ($users as $user) {
// ...
}
});How can you stop processing further chunks from within the closure?
return false;
What is a problem that can arise from using the chunk() method instead of the chunkById() method?
If database records are being updated while chunking results, the chunk results could change in unexpected ways. The chunkById() method automatically paginates the results based on the record’s primary key.
What is the main difference between using the chunk() and lazy() methods for streaming query results?
Instead of passing each chunk into a callback, the lazy() method returns a LazyCollection, which lets you interact with the results as a single stream.
Again, if retrieved records are being updated while iterating over them, it is best to use the lazyById() or lazyByIdDesc() methods instead.
Define $usercount as the amount of “users”!
$usercount = DB::table('users')->count();
What is the raw() method of the DB facade used for? What makes using it wrongly dangerous?
The raw() method allows you to insert an arbitrary string into a query. Due to raw statements being injected into the query as strings, it could open up the possibility of SQL injection vulnerabilities.
What is the union() method used for?
The union() method unions 2 or more queries together. You may for example create an initial query and then use the union() method to union it with more queries after the fact.
Which 3 arguments does the where() method require?
where('value 1', ' operator', 'value 2')
you could for example want to look for entries in a table, where users are below the age of 35 by adding:->where('age', '<', 35)
to your query.
If the operator you want to use is “=”, you don’t have to specify that. To get users at the exact age of 35 you could add this to your query:->where('age', 35)
When chaining together calls to the query builder’s where() method, the where clauses are joined together by an “and” operator by default. How could you make that an “or” instead?
->where( // ... ) ->orWhere( // ... )
You may want to have multiple where conditions being met with an and operator under your orWhere() clause. How can you achieve that?
->orWhere(function (Builder $query) {
$query->where( // ... )
->where( // ... )Which method should you use when you want to add to a query that certain things aren’t the case?
->whereNot( // ... )
this can also have multiple things, that are not supposed to be the case added like with the orWhere() function.
orWhereNot() also exists.
How can you check if something is inside of a JSON array in a query?
->whereJsonContains( // ... )
How could you check if a part of a JSON array is above a certain length in a query?
->whereJsonLength('example->thing', '>', 1)
This example checks if thing under example is above the length of 1, if you want to use the “=” operator you can again just leave it out and it will default to “=”
What method is used to check if a part of a retrieved query is similar to something?
->whereLike('name', '%John%')
This would check for entries, where the name is similar to John. By default this is not case sensitive but if you wish for it to be, you can add a “, caseSensitive: true” before closing the bracket
Which method should you use to verify if a given column value is contained within a given array?
->whereIn('columnname', [ENTER VALUES HERE])
In a query, how can you check for entries in a column where the values are within a given range?
->whereBetween('columnname', [value 1, value2])
How can you check if the values retrieved in a query in a certain column are outside of a certain range?
->whereNotBetween('columnname', [value 1, value2])
Which method verifies if a value in a column is between two values of two columns in the same table row?
->whereBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight])
obviously it can also be about other values but this is just a good example to show the usefulness of this method
Which method verifies that the value of a given column is NULL?
->whereNull('columnname')
there are also the variants:
~~~
whereNotNull()
orWhereNull()
orWhereNotNull()
~~~
How can you compare a columns value against a date to find values that have the given date?
->whereDate('created_at', '2001-02-14')
date needs to be in the format i have given in this example
Verify that something was “created_at” during a certain month!
->whereMonth('created_at', '02')
add a 0 to the number of the month if it is a single digit number
Which method checks for the specific time of day the value of a column contains?
->whereTime('created_at', '=', '11:20:45')
How could you check if the value contained within 2 columns is equal?
->whereColumn('first_name', 'last_name')
Within a query, create the necessary where clauses to check if the retrieved values have the name John AND EITHER over 100 votes or the title “Admin”!
->where('name', '=', 'John')
->where(function (Builder $query) {
$query->where('votes', '>', 100)
->orWhere('title', '=', 'Admin');
})Always group orWhere() calls in order to avoid unexpected behavior when global scopes are applied!
This card is not supposed to ask any question that you should answer, just keep this as a reminder to ALWAYS GROUP YOUR orWhere CALLS!
Which method allows you to sort the results of the query by a given column and which parameters does it accept?
->orderBy('columnname', 'asc')
->orderBy('columnname', 'desc')
(asc = ascending / desc = descending)
What do you need to do to sort the results of a query by when they were entered into the database? How does it work?
->latest() sorts by latest ->oldest() sorts by oldest
They check the “created_at” column and sort by those values depending on which of the 2 methods you use.
How can you randomize the order of the results retrieved by a query?
->inRandomOrder()
Which method undoes all “order by” clauses that have been applied to a query?
->reorder()->get();
How can you group query results?
->groupBy('example')
->having('example', '>'; 100)
the having() method works very similarly to the where() method in this situation
Let’s assume that a query outputs a bunch of items, but you are only interested in the 11th-15th entry of what the query finds. Make the query only output those!
->skip(10)->take(5)->get();
Create a where clause that only gets applied under a certain condition!
->when($condition, function (Builder $query, string $condition) {
$query->where('example', $condition);
})
->get();If conditions have an else clause that you can add after them that gets executed if the condition is not met. What is the equivalent for the when() method?
When a when() condition is not met, the part after it does not get executed, but if a second “, function (Builder $query) { …” exists after the first one, then that will be executed instead so this way you can have the “else” equivalent to this “if” like conditional.
How can you add entries into a table using a query?
DB::table('example')->insert([
'email' => 'ex@amp.le',
'age' => 23
]);Insert multiple entries into a table in one query!
DB::table('example')->insert([
['email' => 'exa@mp.le', 'age' =>16],
['email' => 'e@xamp.le', 'age' => 24],
]);Insert ‘id’, ‘name’, ‘email’, ‘email_verified_at’ into ‘new_users’ using the values of the entry in ‘users’, where ‘updates_at’ is equal to the time span between now and last month!
DB::table('new_users')->insertUsing([
'id', 'name', 'email', 'email_verified_at'
], DB::table('users')->select(
'id', 'name', 'email', 'email_verified_at'
)->where('updated_at', '<=', now()->subMonth()));How can you insert something in a way that adds what you have entered if it doesnt exist in the table yet, but also updates the values in certain columns when the entry already exists in the database table?
DB::table('flights')->upsert(
[
['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
],
['departure', 'destination'],
['price']
);
The first argument is an array of values to be inserted into the database table, the second argument lists the column(s) that uniquely identify records with the associated table and the third and final argument is an array of columns that should be updated, if an entry with the unique identifiers already exists.
How can you update values in a table?
DB::table('example')->where('id', 1)->update(['coolness' => 1]);
Which method can you use to either insert an item if it doesn’t exist, or update selected values if it does?
->updateOrInsert()
The first argument is an array of values to be searched in the database in case of them existing, the second and last argument is an array of values that should be updated and what their new values should be.
How can you delete records from a table?
->delete()
you can also specify a where clause before the deletion argument so that only specific entries get deleted.
What does truncating a table mean and which method do you use to do it?
Truncating a table removes all records from the table and resets the auto-incrementing ID to zero. Be especially careful because CASCADE behavior will be applied.
What does a shared lock do?
Calling the sharedLock() method will prevent a selected row from being modified until the transaction the sharedLock() is called upon in is completed.
What is the difference between a sharedLock() and lockForUpdate()?
A “for update” lock prevents the selected records from being modified or from being selected by another shared lock.
Which methods let you see the debug information?
->dump(); ->dd();
dump will display the debug information and let the code keep running while dd will end the code and display the debug information.
What does a seeder class do?
A seeder class seeds your database with data
Where should you add your seeder classes to so that they will be ran in an order of your choosing?
In the call() method of the DatabaseSeeder in database/seeders
Create a seeder using Artisan!
php artisan make:seeder ExampleSeeder
What is the only method contained in a seeder?
run()
Add 3 seeders to the DatabaseSeeder to be ran!
(within run() method)
$this->call([
TestSeeder::class,
ExampleSeeder::class,
BeispielSeeder::class,
]);
How can you stop your seeder from dispatching model events?
use WithoutModelEvents;
Seed your database using Artisan!
php artisan db:seed
Freshly migrate and then seed your database using Artisan!
php artisan migrate:fresh –seed
