Posts tagged with database
Recently I was working on a client project where a data import was performed via queues. Each record was imported in its own queued job using multiple queue workers. After the data import was done we had more rows than expected in the database. In this blogpost I'd like to explain why that happened.…
In an amazing (and funny) talk Lukas Eder demonstrates how powerful SQL (or any 4th generation programming language) really is.
Join thousands of developers
Every two weeks, I share practical tips, tutorials, and behind-the-scenes insights from maintaining 300+ open source packages.
No spam. Unsubscribe anytime. You can also follow me on X.
Michael Dyrynda, co-host of the Laravel News and North Meets South podcasts shares his knowledge on how to handle applications that share one or more databases.
It took me a couple of months to get this to a working state, so I hope that I'm able to save you some time should you find yourself in my shoes at some point in the future!
https://dyrynda.com.au/blog/sharing-databases-between-laravel-applications
When the db structure changes, it's sometimes easy to perform the data migration inside a database migration.
— Freek Van der Herten (@freekmurze) February 24, 2018
Migration 1: do the db migration + restructure the data
Migration 2: drop the old columns
I'd only do this for simple stuff. pic.twitter.com/v2roA6OxBc
Read more [twitter.com]
In an older post on his blog Mohamed Said demonstrates how you can leverage virtual columns to speed up queries on data stored as JSON.
Laravel 5.3 is shipped with built-in support for updating and querying JSON type database fields, the support currently fully covers MySQL 5.7 JSON type fields updates and lookups,...
Let's see how we may create a generated column to store users favorite color for later indexing.
https://themsaid.com/laravel-mysql-json-colum-fast-lookup-20160709
Logan Henson, a developer at Tighten, wrote a new post on the company blog about MySQL's cool ST_Distance_Sphere function.
On a client project recently, we had to make it easy to filter database query results based on the distance between a user and an entity in our database. It's easy to get overwhelmed in that context, worrying about the crazy amount of PHP calculations you're going to have to run. ... If you need to calculate this, you can actually get surprisingly far by just using MySQL!
https://tighten.co/blog/a-mysql-distance-function-you-should-know-about
Last week our team released a new package called laravel-db-snapshots. It provides a few artisan commands to quickly dump and load a database. We've built this for is to help us develop features in an app that require the database to be in a specific state. With this package we can take a dump of…
Out of the box Laravel comes with a few commands to migrate the database. One of them is migrate:refresh. That one will first run the down-steps for all your migrations and then run all the up steps. After that process your database should have the same structure as specified in your migrations. But…
Generated columns where introduced in MySQL 5.7. In the latest post on her blog Gabriela D'Ávila explains the feature.
There are two types of Generated Columns: Virtual and Stored. ... Consider using virtual columns for data where changes happens in a significant number of times. The cost of a Virtual Column comes from reading a table constantly and the server has to compute every time what that column value will be. ... You should consider using Stored Columns for when the data doesn’t change significantly or at all after creation,
https://blog.gabriela.io/2016/10/17/understanding-generated-columns/
If you like the post, be sure to check out Gabriela's excellent talk at Laracon EU as well:
Colin Viebrock, author of the well know laravel-sluggable package, created a new Sequel Pro bundle that can genenerate Laravel migration files.
Connect to a database, and select a table in the left-hand column. From the application menu, choose Bundles › Export › Export to Laravel Migration. The resulting Laravel migration file will be saved to your desktop. You can then move this file into your Laravel project (usually /database/migrations) and then run artisan migrate.
Laravel 5.3, which will be released at this years Laracon US, has some new very handy functionality to work with MySQL 5.7 JSON columns. In a post on his site Matt Stauffer demonstrates the new API.
While Laravel has had the ability to cast your data to and from JSON since version 5.0, it was previously just a convenience—your data was still just stored in a TEXT field. But MySQL 5.7 introduced an actual JSON column type.Laravel 5.3 introduces a simple syntax for lookups and updates based on the value of specific keys in your JSON columns.
https://mattstauffer.co/blog/new-json-column-where-and-update-syntax-in-laravel-5-3
If you're going to use this functionality, you should also read Mohamed Said's post on how to improve performance by using MySQL generated columns.
JSON columns cannot be indexed. You can work around this restriction by creating an index on a generated column that extracts a scalar value from the JSON column.
http://themsaid.com/laravel-mysql-json-colum-fast-lookup-20160709/
Every single day I use Sequel Pro to manage MySQL databases for all projects I'm working on. Sequel Pro gets used in both development and production environments. Because the databases mostly have the same name and tables in all environments it's very easy to mix them up. You have to be really…
Sebastian De Deyne, developer at Spatie and creator of several Spatie packages, has written a post on how to override Laravel's default translation provider.
When building a website for a client that wants to be able to manage content, Laravel's language files aren't ideal since you can't edit them without diving into a bundle of text files. We recently decided to drop all the lang files in our custom CMS in favor of persisting translations in the database, which allows us to build a custom interface for managing them.This post is a quick overview on overwriting Laravel's default translation loader, which means you can keep using the lang method while fetching the translations from a database. Writing a custom loader is easier than it sounds. First we'll set up our translation models, then we'll write our loader, and finally register it in our application.
https://sebastiandedeyne.com/posts/2016/using-a-database-for-localization-in-laravel
Seb implemented the described functionality in Blender, our Laravel template we use to kickstart nearly all our projects.
A few weeks ago Gabriela D'Ávila helped a famous guy with getting an MySQL import down from 16 to 6 minutes. In a post on her blog she explains how that was done.
A few weeks ago my friend Frank de Jonge told me he managed to improve an import into a MySQL server down from more than 10 hours to 16 minutes. According to him it had something to do with one of the field types (too long fields to really small data) and the amount of indexes and constraints in the tables. We were talking about 1 million records here. He wondered if it was possible to make it even faster.http://gabriela.io/blog/2016/05/17/fast-data-import-trick/...
Turns out there are many ways of importing data into a database, it all depends where are you getting the data from and where you want to put it.
The Query Tracer package can help you find out where a specific database query is being called. It works with both Clockwork and the Laravel Debugbar. Here's a screenshot taken from the readme:
https://github.com/fitztrev/query-tracer
EDIT: turns out that Laravel Debugbar can do this out of the box:
The database model behind WordPress doesn’t follow several good database design rules and conventions. When we’re designing a database for a specific purpose, knowing all its desired functionalities in advance, we can follow all of those rules. But WordPress needs to cover anything that anyone could have in mind, so sacrificing foreign keys and using EAV is something that must be done.http://www.vertabelo.com/blog/technical-articles/wordpress-behind-the-scenes-part-2
Most databases will contain some records that must be cleaned up. The reasons why a record can become unneeded are diverse: maybe it's a temporary record that was only needed for a little while if you're logging stuff to a table, a record may become too old to be of interest anymore ... To help a…
A few weeks ago I released a package called db-dumper. The package can dump the structure and contents of a database to a file. Here's how it can be used: Spatie\DbDumper\Databases\MySql::create() ->setDbName($databaseName) ->setUserName($userName) ->setPassword($password)…
Povilas Korop shared a neat trick at Laraveldaily.com today.
Let’s imagine the situation: you have a big database table (like 10 000 rows or bigger) and you need to run an update to one column. But you cannot run just SQL query – there is some PHP logic behind it. So foreach loop could potentially take forever or bump into a default 30-second script limit. Luckily, Laravel has a neat solution for it.http://laraveldaily.com/process-big-db-table-with-chunk-method/
You may be asking yourself "Why PostgreSQL?" There are several choices for open source relational databases out there (we looked at MySQL, MariaDB and Firebird for this article), but what does PostgreSQL have that they don't? PostgreSQL's tag line claims that it's: "The world's most advanced open source database." We'll give you a few reasons why PostgreSQL makes this claim.https://www.compose.io/articles/what-postgresql-has-over-other-open-source-sql-databases/