Debating Between Personal Caching Mechanism and This Package for Handling City Data - Advice Needed

[andrewdwallo]

Hey guys,

I'm working with a large dataset containing city information (about 130,000 rows from a CSV file). The data is primarily read-only and rarely changes. I'm debating between two approaches for managing this data efficiently: Laravel's built-in caching mechanism and using this package to load all the data into an SQLite in-memory database. Your insights would be invaluable to me.

Current Implementation: Laravel Cache

Here's a snippet of my current implementation using Laravel's Cache:

class CityService
{
    public function getCitiesByCountryAndState(string $countryCode, string $stateId): array
    {
        $cacheKey = "cities.{$countryCode}.{$stateId}";

        return Cache::remember($cacheKey, now()->addMonth(), function () use ($countryCode, $stateId) {
            $cities = $this->fetchCitiesByCountryAndState($countryCode, $stateId);
            return collect($cities)->map(static function ($item) {
                return City::fromArray($item);
            })->toArray();
        });
    }

    private function fetchCitiesByCountryAndState(string $countryCode, string $stateId): array
    {
        $filePath = resource_path('data/cities.csv');
        $fileHandle = fopen($filePath, 'rb');
        $header = fgetcsv($fileHandle);

        $cities = [];
        while (($row = fgetcsv($fileHandle)) !== false) {
            $city = array_combine($header, $row);

            if ($city['country_code'] === $countryCode && $city['state_id'] === $stateId) {
                $cities[] = $city;
            }
        }

        fclose($fileHandle);

        return $cities;
    }
}

How it Works:

• I cache the data by country and state using their combination as the cache key.
• Data is fetched once for a specific country and state, then cached for subsequent requests.
• Service is connected to a model to use Laravel Eloquent like operations.

Pros:

• Data is cached based on the country_code and state_id, making it quick for frequent queries.
• Simple to implement and ties in well with Laravel.

Cons:

• This implementation reads from a CSV file every time a new cache needs to be set.
• Limited querying capabilities (still pretty easy though).

Alternative Approach: This package

As an alternative, I'm considering using this package, which would load all city data into an in-memory SQLite database.

How it Works:

• Load all data into an SQLite in-memory database at the start.
• Entire dataset becomes available for quick querying without further file read operations.

Pros:

• Quick data retrieval once loaded.
• More memory-efficient in the long run as data is loaded only once.

Cons:

• Initial heavy memory impact due to loading all 130,000 rows at once.
• Could be overkill if most queries only require a small subset of data.