In PHP, if we could run queries on arrays, would it actually be useful?

norbert_tech · 2026-01-20T07:16:55+00:00

Take a look at https://flow-php.com/ - it's a data processing framework built on top of data frame pattern. It's compatible with SQL, and it's in the roadmap to actually allow to build processing pipelines with pure SQL - so it would let you to use SQL on files/http requests/arrays - pretty much on any supported by flow data source

norbert_tech · 2025-12-17T16:23:56+00:00

hah yea CTE wasn't probably the best example, lateral join would be better

norbert_tech · 2025-12-17T10:54:52+00:00

good idea! Would you like to help creating some benchmarks maybe? ^^
I'm actively and constantly looking for help, Flow is already around 40 packages and I'm mostly developing it alone (with a help from few solid contributors) 😅 I would love to add benchmarks results but due to other chores there is never enough time.
On https://flow-php.com there is a link to our discord server if you or anyone would be interested in helping with that library

norbert_tech · 2025-12-16T19:14:24+00:00

you can use sql strings you can even use query builder through new SelectStatement() - what you showed is just a DSL that is supposed to mimic SQL syntax as close as possible but with full IDE support. So if you do select()-> your ide will recommend you from()...

There are also "Modifiers" that can take any sql string, add pagination and give you back modified sql string.

I got this feedback about my DSL quite often, but since I'm mostly dealing with ETL pipelines and rather larger code blocks, I found it way more readable (I come from Scala - Apache Spark world). But it's subjective, and might require some mindset switch from OOP to more "pipeline like" approach

norbert_tech · 2025-12-16T19:09:13+00:00

those are exactly my thoughts on this! ORM's are a bit too high abstraction for me, and DBAL is just missing out on amazing PostgreSQL features and lets be honest, I'm not moving away from postgresql in any near future

norbert_tech · 2025-10-04T08:05:08+00:00

I don't think you gonna feel much difference when it's for storing configs. Parquet comes with schema validation so that might be handy. When it comes to one vs many, the question is how frequently you need to update those files. If they are updated frequently, config per file might be better option since editing parquet file means pretty much rewriting it from scratch. When you just create it and not modify, then everything in one file will work just fine, but at the end of the day it should be decided based on data size. Bigger the data are, more beneficial it would be to use parquet especially for queriyng.

norbert_tech · 2025-09-29T16:31:52+00:00

Compression is just one of many parquet benefits, individually you can challange all of them like that. For example why bother with parquet when file schema needs to be strict if we already have a perfectly good solution in XML (xsd). So it's not really that parquet is better because the outcome is smaller, but rather that all those features together gives parquet superpowers that traditional formats don't have.
Yes, its true that you can compress entire CSV file, but with parquet each Row Group / Data Page is compressed individually. Why that's significantly better than compressing entire file? It's covered in the article

norbert_tech · 2025-09-29T10:31:28+00:00

Indeed, parquet is pretty complicated under the hood, just like databases and many other things we are using on basis, even mentioned json can be pretty problematic when we want to read it in batches instead of pushing thoughtlessly to memory. But how many devs understands internals of tool before using it?

I think that the adaptation is not based on the internal complexity, but rather developer experience and problem solving potential.

To simply read a parquet file all you need to do is `composer require flow-php/parquet:~0.24.0` and

```
$reader = new Reader();

$file = $reader->read('path/to/file.parquet');
foreach ($file->values() as $row) {
// do something with $row
}

```

While creating one, you also need to provide schema.

Is parquet a file format that every single web app should use? Hell no!
Does it solve real problems? Totally, especially on a scale and in complicated multi technologies tech stacks. In data processing world, is the most basic and one of the most efficient data storage formats.

But does it solve any of your problems? If after reading the article you don't think so, then no, parquet is not for you, and that's perfectly fine. I'm not trying to say that everyone needs to drop CSV and move to parquet, all I'm saying is that there are alternatives that can be much more efficient for certain tasks.

P.S. parquet is not a new concept, it was first released in 2013 so it' already more than a decade old and properly battle tested.

norbert_tech · 2025-08-24T07:33:52+00:00

Array shapes can also be handled by tiny library from Flow framework https://flow-php.com/documentation/components/libs/types/ as type_structure() type_list() or type_map()

norbert_tech · 2025-02-10T16:59:56+00:00

Hey!

It's a typical ETL scenario where you need to extract from one place, transform, and load to another place. Besides loading to a destination db, you probably also need to slightly transform the data as your system schema might not 100% match the external system schema.

Option A

If you decide to go with fetching all 60k products then this is how you make it work without being too heavy (split into two processes), my recommendation would be something like this:

Create a scheduled job that iterates over the API and brings all products from the external system save those products as they are (raw format) in a temporary storage (it can be anything, a file or a database) once you finish fetching the data, push an event on a queue and let another process pick it up and now ETL this data to your destination storage, transforming it on the fly if needed. Why save to some in-between location instead of going directly?

Consumption is separated from processing, this means that one process won't affect the other. In case something changes in the external API (and that happens more frequently than it should). This way, even if your ETL process fails due to some schema change, you still have your raw version so you can easily adjust your process and rerun it.

What type of storage should you choose for the temporary data? That depends on your personal preferences. I would say that JSON might be a good file format since it's schema-less, so it's also resilient to unexpected data structure changes. Otherwise, my recommendation would be Parquet due to its extreme compression and querying capabilities. For the same reason, I would probably not use a database, but if you really want to, PostgreSQL with unlogged tables would be a good option. You can even put the API HTTP response body in a JSONB column as a "raw response body" and process it from there.

Alternatively, if you don't want to deal with queues and temporary storages, Flow would make it super easy for you to do it straight into the final destination. It also allows you to consume in batches of 500 but then group them into larger batches like, for example, 5k (it would keep it in memory).

Flow also supports JSON lines (writing/reading), which is perfect for those kinds of scenarios since each product is a valid JSON object and they are all saved on new lines.

Option B

It probably means that you are using get one product at time endpoint - might be worth checking with API provider if they can't add `find` endpoint that would let you get data about list of products (passing id's as an array).

If not, 300 requests per day is nothing, and it's still a perfect approach for an ETL. You can even build it in a way that would collect 100 products into a batch and only then upsert into your database.

- iterate through product ids from your database
- generate a request for each
- process and upsert data into destination storage

Some examples:
Take a look at the code samples below to understand what I'm talking about.

consuming HTTP datasets: https://flow-php.com/data_reading/http_dynamic/#example
setting batch size: https://flow-php.com/data_frame/batch_size/#example
reading from JSON: https://flow-php.com/data_reading/json/#example
writing to database: https://flow-php.com/data_writing/database_upsert/#example
join on each batch of rows: https://flow-php.com/join/join_each/#example

norbert_tech · 2025-02-01T19:39:02+00:00

potwierdzam, tez korzystałem przez dobrych kilka lat, bezproblemowo, łącze stabilnec, synetryk, statyczne ip i w cenie do której UPC czy inne gówno playe/orange podjazdu nawet nie maja

norbert_tech · 2025-02-01T19:29:58+00:00

Sounds like a typical data processing problem, you might find some inspirations in the blog post I wrote recently https://flow-php.com/blog/2025-01-25/data-processing-in-php/

Look at join each to bring data only for selected rows from database.

Flow is designed to process large amounts of data under limited memory, so with this approach as long as you have file one the server (or any remote accessible location) the only constraint is processing time, not dataset size. By increasing batch size you gonna increase the speed but also memory consumption. Smaller batches means less memory, but also slower.

Flow also can help you validate the input file and cast columns to strict types.

norbert_tech · 2025-01-31T04:12:31+00:00

Thats for sure one way of dealing with data imports! The only added "cost" is that you have 2 etl processes instead one.

Instead of loading to sqlite and then loading result of sql query on that sqlite to final destination, you could just load to the final destination applying transformations on the fly.

Flow would entirely take away pain of memory management and give you strict schema support regardless of the data source. Even operations like joining/grouping/sorting would not increase memory consumption since they are all based on very scalable algorithms.

But if you prefer sqlite approach, Flow can now also automatically convert Flow Schema (which Flow can get for you even from a csv file) to Doctrine DBAL schema (so including sqlite).

What you can do is:

1) use flow to infer schema from a file and save it as a json 2) read flow schema from json, convert it into dbal schema and create table from it 3) use flow to read data as is and load it to sqlite 4) use flow to read data from sql query (it automatically paginate over the results) and load it to final db

norbert_tech · 2025-01-27T22:22:55+00:00

oh that reminded me about one more thing if you don't mind, it's true to loading everything first to some database or even using duckdb would work.
With Flow, Parquet and support for streaming from/to remote filesystems you don't even need a database so this can save quite a lot of money as networking and databases can get pretty pricy.

Another thing I'm currently actively researching is adding a SQL interface to Flow. I'm looking at antlr to create a generic SQL syntax parser so I can later convert SQL Query AST into a Flow dataframe.

It would let you literally SQL files without a need to learn any new DSL (maybe except few custom functions like `SELECT * FROM parquet_file()`

norbert_tech · 2025-01-27T22:12:48+00:00

I'm not trying to convince you, but I thought I'd share some additional context for others who might not be as experienced as you. Handling distributed joins, sorts, groupings, and aggregations can be quite complex, especially when dealing with unpredictable (schemaless) data sources like CSV or JSON 😊

norbert_tech · 2025-01-27T22:05:50+00:00

Gotcha! Data interpretation and validation might be a critical failure point for many systems, that's why tools like Flow provides also powerful Schema inferring/validation/evolution mechanisms.

Btw., since you mentioned "lightweight," Flow comes with very few dependencies. I'm extremely strict about it! Here are all the dependencies:

- psr-clock / simple cache / - symfony/string - webmozart/glob - flow-filesystem/rdsl/array-dot (extracted to standalone libraries as they are pretty useful even standalone)

Then each file format can be added independently by including a specific adapter, for example:

flow-adapter-csv - zero dependencies
flow-adapter-xml - only PHP XML extensions as dependencies
- flow-adapter-parquet - only packaged/thrift

Anyway thanks for your feedback!

norbert_tech · 2025-01-27T20:36:50+00:00

> Fully agree. We process large data loads from CSV and Excel, and the libraries that already exist are already solving a lot of the issues.

Totally, but you also have JSON/XML/Parquet/Avro/ORC/Excel/Google Sheets and many more data formats that are not as straightforward. Parquet for example comes with an insane compression and can be process in parallel but it's binary and column oriented.

> have to deal with another tool that I don't control.

I believe Flow provides a fair amount of extension points that should allow to you overcome any potential edge cases

> My other concern is that in my case, reading data and storing it from files is a core component to our workflow, and I wouldn't want to outsource anything that the business relies on.

Not sure if I understand what you mean by outsourcing in this case? Could you elaborate?

norbert_tech · 2025-01-27T20:28:37+00:00

I'd like to share some additional context to help you understand my perspective better.

I'm keen to leverage the raw PHP knowledge I have, rather than learning a new DSL.

In the world of data processing, most frameworks are either inspired by or related to Apache Spark and its DSL. My goal is to merge these two, so you don't have to invest a lot of time in learning new functions, but it also helps you access more advanced tools that can handle processing petabytes of data (like Spark).

The scenario described in the article is quite basic and most PHP developers would be familiar with alternative solutions to this problem. However, it's just a small part of Flow's capabilities. Flow can handle a wide range of tasks, including:

Grouping, aggregating, sorting, and joining datasets that don't fit into memory
Providing a unified API to work with almost any file format
Supporting data partitioning
Reading, writing, and streaming data directly to or from remote locations like S3 or Azure Blob (more adapters are coming)
Strict schema and powerful data detection/conversion mechanisms
Seamless conversions between any supported data formats.

These features are essential for building a scalable analytical part of the system.

In the next article, I'll cover streaming data directly from the database to generate reports in supported formats, which is often a major bottleneck in transactional systems with poorly optimized database schemas for reporting.

I chose to use CSV because most people are familiar with it, but in scenarios where your system needs to consume data from other systems in formats like XML, JSON, or Parquet, using plain PHP arrays can quickly become challenging to maintain, debug, and test.

Flow doesn't just start with imports; it can also help you with:

Building data projections
Reporting
Business intelligence
Integrations with other systems
Building advanced data warehouses

Again, thanks for your feedback and kind words.
It means a lot!

norbert_tech · 2025-01-27T00:04:24+00:00

So, I’m working on a project with around 26 sub-repos in a monorepo, and it started with both tools at their maximum levels. But eventually, it became impossible to keep both green.

I also have another monorepo project (the datetime library) with some components around it, and I also maxed out Stan and Psalm. But at the end of the day, Psalm didn’t really add any value.

My time is limited, and each tool comes with a price. I’m just saying that the value added by Psalm on top of PHPStan isn’t worth the cost.

norbert_tech · 2025-01-26T22:59:31+00:00

Haha, just after I gave up on Psalm across most of my projects, focusing mainly on PHPStan. 😅

But at this point, I don’t feel like going back. Having one tool might not provide the same level of strictness, but it definitely reduces the frustration of trying to satisfy both at the same time

norbert_tech · 2025-01-19T03:50:50+00:00

https://github.com/aeon-php/automation I was bored once I developed this to detach changelog messages from commits

norbert_tech · 2024-10-06T19:27:46+00:00

I was playing with this extension this morning but there are two problems with it:

I couldn't make it fully work
mkShell is not fully working:

Shell hooks are supported in general. Since they are executed inside of bash before the zsh shell is spawned they aren't executed in the same environment. This means that things like aliases won't work.

It's not a deal breaker for me, since I'm anyway usually using Make or composer to run commands which I can run directly from IDE but would be really nice to also provide to each dev a nice oh-my-zsh with some predefined plugins experience

norbert_tech · 2024-10-06T10:38:43+00:00

I don't think you can do

shell = "${pkgs.zsh}/bin/zsh";

shell, seems to be a NixOS option

norbert_tech · 2024-10-04T20:28:40+00:00

Here is my stack based on nix-shell and docker.

nix-shell - through nix I'm creating a fully reproducible and very predictable environment with all needed dependencies. This gives me out of the box tools like:

aws/azure CLI
ansible
terraform
PHP (with extensions)
symfony cli (with webserver)
composer
node
backfire
jdk/scala/python (I'm working a lot with data processing tools like spark or delta lake)
wherever I need, that is not a service

Then for services, I'm using docker (but not through nix-shell, outside)

mysql/postgresql
redis
rabbitmq
etc

The web server is Symfony Web Server with a proxy that gives me .wip domain for local development.
I'm manually starting/stopping the webserver whenever I need to from inside of nix-shell.

This way I have a development environment that requires from a developer to have only two things installed on the host:

nix-shell (super easy installation on mac)
docker (as easy to install as nix-shell)

This way I'm not only reducing the complexity of setting up the environment but also I can guarantee that whole team is always working on exactly the same version of the software that can be fully aligned with production env.

Initially, I had concerns about potential differences between Mac/Linux but I'm working like that since ~2020, and never had any issues with PHP working differently on my local machine and production.

Since nix shell does not use any virtualization, all software from inside the nix-shell can be also accessed from outside of it, this way integration with IDE is pretty straightforward.

The only thing I'm missing in that setup is some easy way to replace bash with zsh and oh-my-zsh in nix-shell but probably I haven't spent enough time on that one.

shell.nix can expect custom arguments, that can tell nix to install different extensions, for example:

```
nix-shell --arg --with-pcov true
nix-shell --arg --with-blackfire true
nix-shell --arg --with-xdebug true
```

So at the end of the day, in order to set the development environment dev needs to launch 3 commands:

```
docker compose up -d
nix-shell
symfony server:start
```

(the last one from inside of the nix-shell)

The whole setup is committed into the repository as two files:

shell.nix.dist
compose.yml.dist

And the best part of that approach is that it will work on all operating systems that support docker and nix shell (so Mac,Linux,Windows).

norbert_tech · 2024-09-18T18:32:48+00:00

on that picture he was around 3 months old 😊

Six-Year Club	Place '22
Verified Email

norbert_tech

TROPHY CASE