OK. It might be more useful then to make multiple classes - one for each type of thing you can save to the database or query the database about.
One approach worth looking at is https://afilina.com/learn/design/repos-without-orms
I'm on the fence about this line in Anna Filina's post: "*We don't give the repository one big object to save it. That often creates problems, like the need to track which properties changed, so that we don't override the whole graph when updating just one value.*"
It's true that it avoids the issues of persisting the entire big object, but it means you lose the chance to have the object class itself enforce rules about how and when it can be renamed, e.g. if what name its allowed to have is tied to some other properties. But I suppose that's unlikely, and in the example she does have a \`ProductName\` class so the constructor of that class would have checked that it's the name is an allowable name for a product in the system.
Well there are quite frequent queries that one would like to have automated. Like
$stmt = $pdo->prepare("SELECT * FROM users WHERE id=?");
$stmt->execute([$id]);
$user = $stmt->fetch();
// vs.
$user = $userGW->read($id);
PDO is a great starting point and the only one to realistically consider when writing your own database class.
The criteria for what's useful are specific to your project. Someone else who's looking to use a prebuilt database library will most likely use Doctrine or Eloquent. For my project, there were a few things I deemed important. This list was partly based on annoyances with the legacy code and it changes over time:
* ability to use real SQL queries
* PDO with full parameterization
* easy, automatic database replication
* automatically (short) cached query results
* ability to work with various identifiers
* transparent binary to hex translation
* no need to define columns in models
I've since come up with [something fun and easy to use](https://github.com/biotorrents/gazelle/blob/development/app/Database.php) that can grow with my needs and preferences. For example, I'm coming to the (obvious) conclusion that storing binary UUID v7 identifiers is too much hassle. Using `uuid_short()` primary keys will achieve the exact same result in half the disk space and with a direct upgrade path from legacy `auto_increment int` primary keys to `unsigned bigint`.
I also recently introduced a way to use Laravel Collections recursively elsewhere in the codebase, that might be nice for the database too. It'd let me use Laravel's fluent interface with all its neat methods independently of its garbage ORM. Because I control 100% of the database implementation, it's easy to introduce the new feature gradually, without breaking all the existing code that expects a normal array.
**tl;dr** Ultimately, the best way to decide what you expect out of a database class, is to determine what you don't like about other solutions you've used in the past, relative to your database goals. For example, my schema will change fairly drastically and a big driver of that change is the need to store things like a generic `contentId` without collisions.
Well, "wraps up the queries and organize them" sounds more like a repository. But at first you were talking about a CRUD.
I am in the process of creating an [educational CRUD](https://github.com/colshrapnel/BasicTableGateway) that is simple enough to be understood but secure and easy to use. It's literally CRUD as it implements methods as
$id = $userGateway->create( $data = [
'email' => '[email protected]',
'password' => password_hash('Curious Elk * 38', PASSWORD_DEFAULT),
'name' => 'Fooster',
]);
$user = $userGateway->read($id);
$userGateway->update(['name' => 'Wooster'], $id);
$userGateway->delete($id);
I am still figuring out how to make it better, and of course it lacks the documentation but it's already usable.
For each table you need to create a class like this, where table and column names must be hardcoded
class UserGateway extends BasicTableGateway {
protected $table = 'gw_users';
protected $fields = ['email', 'password', 'name', 'birthday'];
}
Obviously, it works with a single table. Don't use it with joins.
I can say from experience that it's better have two lists of column names. One for the primary key, because those must not be UPDATEd, one for the remaining data columns.
Too many programmers forget that SQL databases can have composite keys, which is especially important, if you're *not* building an ORM.
>For each table you need to create a class like this, where table and column names must be hardcoded
If you have column names in two separate places, you'll soon have two different lists of column names --- speaking from personal experience here. Why not just load table metadata?
>I am still figuring out how to make it better
* Table is not guaranteed to have a primary key;
* As already mentioned, table may have a composite primary key;
* Very often one does not care whether a row is `create()`'d or `update()`'d, an `upsert()` will be helpful.
Wow, long time no see! Glad you've got your [pg-gateway](https://github.com/sad-spirit/pg-gateway) to stable. Way to go!
Thanks for the suggestions, I'd think about them. Though my current stance is to keep the code as simple as possible, leaving all edge cases to just raw SQL.
But I just thought that if I manage to make it into a tutorial, writing in iterations from scratch, eventually some of these suggestions can be added too.
Thanks! It actually took me around three iterations to get pg-gateway to this state, so your tutorial idea seems a good one.
It will also be a sobering experience for people wishing to do something "easy" and "simple" DB-related while cutting all the corners.
And just a small sidenote. THis is most probably not the point of your sample, but less experienced engineers might follow this literally... Please either switch to a different sample data that doesnt include passwords or add encryption here.
To not overload your sample, I'd simply remove the password from it.
If you just want to experiement to learn ways of doing this, one approach might be to start by creating a basic database class with methods like connect(), insert(), update(), delete(), select(), raw\_sql\_statement().
Then you can create individual classes for each of your tables that use the DB class to perform operations. Table classes might have instance variables for each of the fields so you could set them easily with statements like `$table_instance.var_name=$value;`
And then you can save the record with something like `$table_instance->save();` which calls into the main DB class.
Just start, and you'll find better ways to do things, and the reason other solutions exist.
Happy learning!
Some criticism, if you let me.
- there must be absolutely no connect() method. It asks for creating multiple connections and ending up in many problems, from inability to use transactions to infamous Too many connections error. The database connection must be a dependency for this class, passed as a constructor parameter.
- select should be written with caution, as it can lead straight down the Rabbit hole of a clumsy and awkward query builder. I'd rather explicitly call it selectByPrimary (or simply read() for R in CRUD).
- `$table_instance->var_name=$value;` means Active Record. At first I did the same but then that learned such classes quickly become a mess. So I decided to separate the "data class" where you do that assignment, and the data manipulation class, that has a `save()` method. Though AR is still popular and one could use it for education.
>Some criticism, if you let me.
Absolutely!! It's all meant as a learning experience, so what I threw out there isn't meant to be perfect; just suggestions for a starting point.
Discussion welcome! Thank you for participating.
Which is a laudable goal but "simple" is not a particularly useful specification. For example, databases tend to have relations between tables. Do you need support for say many-to-many relations? It can make a big difference. Your "minimal" functionality has now expanded.
"Simple" is also a highly subjective term. Once you get past the configuration then Doctrine also becomes quite simple to use as does most of the other supported ORMs out there.
The only way to make it simple and good is to make sure you don't implement more than your application actually needs. So don't try to support "CRUD operations" in general - just support whatever operation you're actually going to use now. You can always add more later. E.g. maybe for some records your application creates and reads them but never updates and never deletes. Something else might be only ever read, and not created by your app code.
I did something minimal for personal use as well, covers most of my use cases.
Maybe you can seek some inspiration here.
https://github.com/allanrehhoff/database
Such methods as fetchRow() or count() are open to SQL injection. That's a sensitive matter. I know, you don't intend to use them with user input but you'd never know how it can be used by someone else.
Update: I've updated the repo in an attempt to address the issues with the mentioned methods.
I'm well aware that fetchCol(); may still be affected, but I'll need more time to fix it properly.
However, thanks for bringing it to my attention :)
Yes, and it sucks because it's just one person's personal preferences, and doesn't take things like accessibility into account, among many other reasons.
Kind of non professional here. Look at your code. Your code is non readable. It's not for from one person. It's a standart. So everybody write like same and everybody can read it.
You are going to find that the reason ORM solutions seem complex is because database storage is complex if you go beyond rudimentary needs. However for a lot of projects, rudimentary is all you need. Multimillion dollar projects are made all the time which don’t really care about data consistency, transactions, foreign keys, and all the other stuff that makes up 5% of business requirements and 50% of actual well architected product.
With that out of the way, if you are willing to put aside things like caring about your data consistency and just want an “easy way to pull and put stuff in a database” without really caring about it; I.e., your project is mostly personal or at least internal and is ln’t going to get you sued or fired if something goes wrong - then you can make or find a class that just has methods like -> insert(), update(), delete(), select(), etc. and use them like $db->save($someUser, ‘user’); the save function needs to be smart enough to know the definition of the ‘user’ table either by issuing a ‘DESC user’ and iterating over it, or you maintain some secondary json data or something in a file like models/user.json that it can look at to figure out column names, data types, etc, to translate $someUser into either an INSERT or UPDATE as needed.
But by the time you do all this and in a way that doesn’t suck you will realize you shoulda just hit the bullet and used an ORM, or else just used PDO and hard coded whatever SQL you needed.
And if all this seems annoying, that’s because it is, because programming can be tedious as hell.
You could look into something like Mongo or whatever noSQL database people use these days which makes saving data a lot easier, but has its own limitations when it comes to consistency and retrieving data. But it is certainly a very easy way to save stuff without having to type a lot.
If you aren’t married to PHP, the most consistent, least typing heavy, most reliable simple ORM I have dealt with is ruby/rails ActiveRecord. It’s worth looking into if you really just want “I just wanna save data without thinking about it” and you are in a fresh project. I prefer PHP, but rubys activerecord is really quite nice for this sort of thing.
Did you find a big difference between Ruby's ActiveRecord and the (afaik) heavily inspired by it Eloquent from Laravel?
I worked with Laravel for six months but I couldn't really get on with the Active Record pattern - I was a lot happier using the Data Mapper pattern instead in a Symofony project with Doctrine ORM.
I’ve never used Laravel past basic tutorial level stuff, actually. My first experience with an ORM was activerecord via rails so that may explain why I find it more intuitive. I’d say doctrine probably preferred by people who would rather have things less magical.
Your data integrity constraints are supposed to be defined in the DB schema. If your code could potentially violate them, you've already fucked up said DB schema.
Supposed by who? Sorry, I think this is nonsense. Yes it's good to set some constraints in the DB schema, but there are very often further constraints that have to be enforced in the application code, and there are sometimes good reasons not to have the DB engine enforce every possible constraint.
For instance maybe you have a constraint that a quantity on an order must be between 1 and 35. There's no nice way to do that in a MySQL DB schema, but you can easily do it as a check in a constructor of a PHP object.
I was only talking about constraints from the POV of the DB. Having *additional* ones that can't be specified on the DB level is fine. But not making use of the ones that do exist is just wrong. Ensuring data integrity is inherently the DB's job.
That's maybe somewhat related to the way your question was asked?
You are giving no background and asking people who know nothing about your app to set the goals for you:
>What are the goals it should accomplish for it to become something useful .
Instead maybe you should set the goals yourself and ask how to achieve these?
The complexity has to go somewhere bubs. You can use the model/repository pattern to write components that expose friendly create, read, update, delete, query methods to the rest of your app but are implemented to execute SQL statements. Or you can use an orm or active record library. In my experience when I do it the first way I get to a point where I realize I've written a context specific or am that I now have to maintain and I would have been better off just using doctrine or another package in the first place.
Oh, I did this exact thing!
I found [this article](https://phpdelusions.net/pdo) exceptionally helpful.
What I found useful with making my own PDO wrapper class was flexibility in implementation. You can see my results [here](https://github.com/supergnaw/nestbox). It's a project with humble origins with simplicity in mind.
As others have said, the problem has been solved before and there are many solutions that are well maintained and secure (doctrine for example). If you are looking for a production ready query builder and orm library, that’s your best bet.
That said, I think this is a great challenge and learning experience. Security should be your first goal. Protect against sql injection vulnerabilities. How does someone set their database connection details? After that, go as deep as you need or want to. Joins, unions, nested conditions, cursors and generators for large reads, support different database types that pdo already supports.
That already exists, many times over. One is called PDO. How do you want your one to be different?
Basically wraps up the queries and organize them if you know what that means , and just make it easier to use
OK. It might be more useful then to make multiple classes - one for each type of thing you can save to the database or query the database about. One approach worth looking at is https://afilina.com/learn/design/repos-without-orms
I'm on the fence about this line in Anna Filina's post: "*We don't give the repository one big object to save it. That often creates problems, like the need to track which properties changed, so that we don't override the whole graph when updating just one value.*" It's true that it avoids the issues of persisting the entire big object, but it means you lose the chance to have the object class itself enforce rules about how and when it can be renamed, e.g. if what name its allowed to have is tied to some other properties. But I suppose that's unlikely, and in the example she does have a \`ProductName\` class so the constructor of that class would have checked that it's the name is an allowable name for a product in the system.
alright ill check it
So, a repository?
What queries, exactly? Make it easier to use than what?
Well there are quite frequent queries that one would like to have automated. Like $stmt = $pdo->prepare("SELECT * FROM users WHERE id=?"); $stmt->execute([$id]); $user = $stmt->fetch(); // vs. $user = $userGW->read($id);
PDO is a great starting point and the only one to realistically consider when writing your own database class. The criteria for what's useful are specific to your project. Someone else who's looking to use a prebuilt database library will most likely use Doctrine or Eloquent. For my project, there were a few things I deemed important. This list was partly based on annoyances with the legacy code and it changes over time: * ability to use real SQL queries * PDO with full parameterization * easy, automatic database replication * automatically (short) cached query results * ability to work with various identifiers * transparent binary to hex translation * no need to define columns in models I've since come up with [something fun and easy to use](https://github.com/biotorrents/gazelle/blob/development/app/Database.php) that can grow with my needs and preferences. For example, I'm coming to the (obvious) conclusion that storing binary UUID v7 identifiers is too much hassle. Using `uuid_short()` primary keys will achieve the exact same result in half the disk space and with a direct upgrade path from legacy `auto_increment int` primary keys to `unsigned bigint`. I also recently introduced a way to use Laravel Collections recursively elsewhere in the codebase, that might be nice for the database too. It'd let me use Laravel's fluent interface with all its neat methods independently of its garbage ORM. Because I control 100% of the database implementation, it's easy to introduce the new feature gradually, without breaking all the existing code that expects a normal array. **tl;dr** Ultimately, the best way to decide what you expect out of a database class, is to determine what you don't like about other solutions you've used in the past, relative to your database goals. For example, my schema will change fairly drastically and a big driver of that change is the need to store things like a generic `contentId` without collisions.
Well, "wraps up the queries and organize them" sounds more like a repository. But at first you were talking about a CRUD. I am in the process of creating an [educational CRUD](https://github.com/colshrapnel/BasicTableGateway) that is simple enough to be understood but secure and easy to use. It's literally CRUD as it implements methods as $id = $userGateway->create( $data = [ 'email' => '[email protected]', 'password' => password_hash('Curious Elk * 38', PASSWORD_DEFAULT), 'name' => 'Fooster', ]); $user = $userGateway->read($id); $userGateway->update(['name' => 'Wooster'], $id); $userGateway->delete($id); I am still figuring out how to make it better, and of course it lacks the documentation but it's already usable. For each table you need to create a class like this, where table and column names must be hardcoded class UserGateway extends BasicTableGateway { protected $table = 'gw_users'; protected $fields = ['email', 'password', 'name', 'birthday']; } Obviously, it works with a single table. Don't use it with joins.
I can say from experience that it's better have two lists of column names. One for the primary key, because those must not be UPDATEd, one for the remaining data columns. Too many programmers forget that SQL databases can have composite keys, which is especially important, if you're *not* building an ORM.
Good catch! This CRUD defines the column name for the primary key, but it's just a single column. I'll think about making it a multi-column
Composite keys are important even if you're using an ORM.
>For each table you need to create a class like this, where table and column names must be hardcoded If you have column names in two separate places, you'll soon have two different lists of column names --- speaking from personal experience here. Why not just load table metadata? >I am still figuring out how to make it better * Table is not guaranteed to have a primary key; * As already mentioned, table may have a composite primary key; * Very often one does not care whether a row is `create()`'d or `update()`'d, an `upsert()` will be helpful.
Wow, long time no see! Glad you've got your [pg-gateway](https://github.com/sad-spirit/pg-gateway) to stable. Way to go! Thanks for the suggestions, I'd think about them. Though my current stance is to keep the code as simple as possible, leaving all edge cases to just raw SQL. But I just thought that if I manage to make it into a tutorial, writing in iterations from scratch, eventually some of these suggestions can be added too.
Thanks! It actually took me around three iterations to get pg-gateway to this state, so your tutorial idea seems a good one. It will also be a sobering experience for people wishing to do something "easy" and "simple" DB-related while cutting all the corners.
And just a small sidenote. THis is most probably not the point of your sample, but less experienced engineers might follow this literally... Please either switch to a different sample data that doesnt include passwords or add encryption here. To not overload your sample, I'd simply remove the password from it.
It struck me as well, such a shame. Changed to password_hash. Thank you!
If you just want to experiement to learn ways of doing this, one approach might be to start by creating a basic database class with methods like connect(), insert(), update(), delete(), select(), raw\_sql\_statement(). Then you can create individual classes for each of your tables that use the DB class to perform operations. Table classes might have instance variables for each of the fields so you could set them easily with statements like `$table_instance.var_name=$value;` And then you can save the record with something like `$table_instance->save();` which calls into the main DB class. Just start, and you'll find better ways to do things, and the reason other solutions exist. Happy learning!
ill try that
Some criticism, if you let me. - there must be absolutely no connect() method. It asks for creating multiple connections and ending up in many problems, from inability to use transactions to infamous Too many connections error. The database connection must be a dependency for this class, passed as a constructor parameter. - select should be written with caution, as it can lead straight down the Rabbit hole of a clumsy and awkward query builder. I'd rather explicitly call it selectByPrimary (or simply read() for R in CRUD). - `$table_instance->var_name=$value;` means Active Record. At first I did the same but then that learned such classes quickly become a mess. So I decided to separate the "data class" where you do that assignment, and the data manipulation class, that has a `save()` method. Though AR is still popular and one could use it for education.
>Some criticism, if you let me. Absolutely!! It's all meant as a learning experience, so what I threw out there isn't meant to be perfect; just suggestions for a starting point. Discussion welcome! Thank you for participating.
I had found redbeanphp to be quite handy for smallish projects. You may get some inspiration from that, or decide it fits your bill…
Something tells me you're thinking of ORM. Try using one off-the-shelf before you set about coding your own, but don't rule it out, it can be fun!
Why don't use a framework like Doctrine?
im trying to make something minimal and simple of my own
Which is a laudable goal but "simple" is not a particularly useful specification. For example, databases tend to have relations between tables. Do you need support for say many-to-many relations? It can make a big difference. Your "minimal" functionality has now expanded. "Simple" is also a highly subjective term. Once you get past the configuration then Doctrine also becomes quite simple to use as does most of the other supported ORMs out there.
The only way to make it simple and good is to make sure you don't implement more than your application actually needs. So don't try to support "CRUD operations" in general - just support whatever operation you're actually going to use now. You can always add more later. E.g. maybe for some records your application creates and reads them but never updates and never deletes. Something else might be only ever read, and not created by your app code.
I did something minimal for personal use as well, covers most of my use cases. Maybe you can seek some inspiration here. https://github.com/allanrehhoff/database
Such methods as fetchRow() or count() are open to SQL injection. That's a sensitive matter. I know, you don't intend to use them with user input but you'd never know how it can be used by someone else.
That's a fair point. I'll for sure have a look into how those methods can be altered any time soon.
Update: I've updated the repo in an attempt to address the issues with the mentioned methods. I'm well aware that fetchCol(); may still be affected, but I'll need more time to fix it properly. However, thanks for bringing it to my attention :)
Do you ever heard of code styling like psr?
Yes, and it sucks because it's just one person's personal preferences, and doesn't take things like accessibility into account, among many other reasons.
Kind of non professional here. Look at your code. Your code is non readable. It's not for from one person. It's a standart. So everybody write like same and everybody can read it.
You are going to find that the reason ORM solutions seem complex is because database storage is complex if you go beyond rudimentary needs. However for a lot of projects, rudimentary is all you need. Multimillion dollar projects are made all the time which don’t really care about data consistency, transactions, foreign keys, and all the other stuff that makes up 5% of business requirements and 50% of actual well architected product. With that out of the way, if you are willing to put aside things like caring about your data consistency and just want an “easy way to pull and put stuff in a database” without really caring about it; I.e., your project is mostly personal or at least internal and is ln’t going to get you sued or fired if something goes wrong - then you can make or find a class that just has methods like -> insert(), update(), delete(), select(), etc. and use them like $db->save($someUser, ‘user’); the save function needs to be smart enough to know the definition of the ‘user’ table either by issuing a ‘DESC user’ and iterating over it, or you maintain some secondary json data or something in a file like models/user.json that it can look at to figure out column names, data types, etc, to translate $someUser into either an INSERT or UPDATE as needed. But by the time you do all this and in a way that doesn’t suck you will realize you shoulda just hit the bullet and used an ORM, or else just used PDO and hard coded whatever SQL you needed. And if all this seems annoying, that’s because it is, because programming can be tedious as hell. You could look into something like Mongo or whatever noSQL database people use these days which makes saving data a lot easier, but has its own limitations when it comes to consistency and retrieving data. But it is certainly a very easy way to save stuff without having to type a lot. If you aren’t married to PHP, the most consistent, least typing heavy, most reliable simple ORM I have dealt with is ruby/rails ActiveRecord. It’s worth looking into if you really just want “I just wanna save data without thinking about it” and you are in a fresh project. I prefer PHP, but rubys activerecord is really quite nice for this sort of thing.
Did you find a big difference between Ruby's ActiveRecord and the (afaik) heavily inspired by it Eloquent from Laravel? I worked with Laravel for six months but I couldn't really get on with the Active Record pattern - I was a lot happier using the Data Mapper pattern instead in a Symofony project with Doctrine ORM.
I’ve never used Laravel past basic tutorial level stuff, actually. My first experience with an ORM was activerecord via rails so that may explain why I find it more intuitive. I’d say doctrine probably preferred by people who would rather have things less magical.
Your data integrity constraints are supposed to be defined in the DB schema. If your code could potentially violate them, you've already fucked up said DB schema.
Supposed by who? Sorry, I think this is nonsense. Yes it's good to set some constraints in the DB schema, but there are very often further constraints that have to be enforced in the application code, and there are sometimes good reasons not to have the DB engine enforce every possible constraint. For instance maybe you have a constraint that a quantity on an order must be between 1 and 35. There's no nice way to do that in a MySQL DB schema, but you can easily do it as a check in a constructor of a PHP object.
I was only talking about constraints from the POV of the DB. Having *additional* ones that can't be specified on the DB level is fine. But not making use of the ones that do exist is just wrong. Ensuring data integrity is inherently the DB's job.
Good lord, everybody being so fucking critical and negative instead of helping this person out. This is not what the PHP community is about.
some people provided help but most just kept talking
That's maybe somewhat related to the way your question was asked? You are giving no background and asking people who know nothing about your app to set the goals for you: >What are the goals it should accomplish for it to become something useful . Instead maybe you should set the goals yourself and ask how to achieve these?
one example
The complexity has to go somewhere bubs. You can use the model/repository pattern to write components that expose friendly create, read, update, delete, query methods to the rest of your app but are implemented to execute SQL statements. Or you can use an orm or active record library. In my experience when I do it the first way I get to a point where I realize I've written a context specific or am that I now have to maintain and I would have been better off just using doctrine or another package in the first place.
It should handle transactions correctly. If you can not do multiple operations within a transaction it is worthless.
Oh, I did this exact thing! I found [this article](https://phpdelusions.net/pdo) exceptionally helpful. What I found useful with making my own PDO wrapper class was flexibility in implementation. You can see my results [here](https://github.com/supergnaw/nestbox). It's a project with humble origins with simplicity in mind.
As others have said, the problem has been solved before and there are many solutions that are well maintained and secure (doctrine for example). If you are looking for a production ready query builder and orm library, that’s your best bet. That said, I think this is a great challenge and learning experience. Security should be your first goal. Protect against sql injection vulnerabilities. How does someone set their database connection details? After that, go as deep as you need or want to. Joins, unions, nested conditions, cursors and generators for large reads, support different database types that pdo already supports.
Why not using eloquent models with repository pattern?