PHP library to (Create, Read, Update, Delete) in Mysql/Sqlite databases with zero configuration and some magic.
This library relies in some conventions to avoid configuration.
- Table names SHOULD be in singular and camelCase
- Fields names SHOULD be in singular and camelCase
- The primary key of all tables MUST be
id
. - Foreign keys MUST be
[tableName]_id
. For example,post
table usespost_id
as foreign key. - Associative tables MUST use an underscore joining the two tables in alphabetic order. For example, the relationship between
post
andtag
ispost_tag
butpost
andcategory
iscategory_post
.
This package is installable and autoloadable via Composer as simple-crud/simple-crud.
$ composer require simple-crud/simple-crud
SimpleCrud has the following classes:
- Database: Manage the database connection. Uses internally Atlas.PDO
- Query: Creates the database queries. SimpleCrud is tested with MySQL and SQLite but due uses Atlas.Query internally, in theory Postgres and Microsoft SQL should be supported too.
- Table: Manages a database table
- Field: Manages a database field. Used to format and validate values
- Row: To store and modify a row
- RowCollection: Is a collection of rows
Let's say we have the following database scheme:
CREATE TABLE "post" (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`title` TEXT,
`category_id` INTEGER,
`type` TEXT,
FOREIGN KEY(`category_id`) REFERENCES category(id)
);
CREATE TABLE `category` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`name` TEXT
);
CREATE TABLE `tag` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`name` TEXT
);
CREATE TABLE `post_tag` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`tag_id` INTEGER NOT NULL,
`post_id` INTEGER NOT NULL,
FOREIGN KEY(`tag_id`) REFERENCES tag(id),
FOREIGN KEY(`post_id`) REFERENCES post(id)
);
To start, create an instance of SimpleCrud\Database
passing the PDO
connection.
use SimpleCrud\Database;
$pdo = new PDO($dsn, $username, $password);
$db = new Database($pdo);
//To get any table, use magic properties, they will be instantiated on demand:
$post = $db->post;
SimpleCrud load the database scheme and detects automatically all relationships between the tables using the naming conventions described above. For example the table "post" has a field called "category_id", so SimpleCrud knows that each post has one category.
Note: In production environment, you may want to cache the scheme in order to avoid execute these queries and improve the performance. You can do it in this way:
use SimpleCrud\Scheme\Cache;
use SimpleCrud\Scheme\Mysql;
if ($cache->has('db_scheme')) {
$array = $cache->get('db_scheme');
$scheme = new Cache($array);
} else {
$scheme = new Mysql($pdo);
$cache->save('db_scheme', $scheme->toArray());
}
$db = new Database($pdo, $scheme);
You can interact directly with the tables to insert/update/delete/select data:
Use ArrayAccess
interface to access to the data using the id
:
//Get the post id = 3;
$post = $db->post[3];
//Check if a row exists
if (isset($db->post[3])) {
echo 'exists';
}
//Delete a post
unset($db->post[3]);
//Update a post
$db->post[3] = [
'title' => 'Hello world'
];
//Insert a new post
$db->post[] = [
'title' => 'Hello world 2'
];
//Tables implements the Countable interface
$totalPost = count($db->post);
If you want to select a row by other key than id
, just use a method with the field name:
$post = $db->post->slug('post-slug');
Sometimes, you want to get a row or create it if it does not exist. You can do it easily with getOrCreate
method:
$post = $db->post->getOrCreate(['slug' => 'post-slug']);
A Row
object represents a database row and is used to read and modify its data:
//get a row by id
$post = $db->post[34];
//Get/modify fields values
echo $post->title;
$post->title = 'New title';
//Update the row into database
$post->save();
//Remove the row in the database
$post->delete();
//Create a new row
$newPost = $db->post->create(['title' => 'The title']);
//Insert the row in the database
$newPost->save();
A Query
object represents a database query. SimpleCrud uses magic methods to create queries. For example $db->post->select()
returns a new instance of a Select
query in the tabe post
. Other examples: $db->comment->update()
, $db->category->delete()
, etc... Each query has modifiers like orderBy()
, limit()
:
//Create an UPDATE query with the table post
$updateQuery = $db->post->update(['title' => 'New title']);
//Add conditions, limit, etc
$updateQuery
->where('id = ', 23)
->limit(1);
//get the query as string
echo $updateQuery; //UPDATE `post` ...
//execute the query and returns a PDOStatement with the result
$PDOStatement = $updateQuery();
The method get()
executes the query and returns the processed result of the query. For example, with insert()
returns the id of the new row:
//insert a new post
$id = $db->post
->insert([
'title' => 'My first post',
'text' => 'This is the text of the post'
])
->get();
//Delete a post
$db->post
->delete()
->where('id = ', 23)
->get();
//Count all posts
$total = $db->post
->selectAggregate('COUNT')
->get();
//note: this is the same like count($db->post)
//Sum the ids of all posts
$total = $db->post
->selectAggregate('SUM', 'id')
->get();
select()->get()
returns an instance of RowCollection
with the result:
$posts = $db->post
->select()
->where('id > ', 10)
->orderBy('id ASC')
->limit(100)
->get();
foreach ($posts as $post) {
echo $post->title;
}
If you only need the first row, use the modifier one()
:
$post = $db->post
->select()
->one()
->where('id = ', 23)
->get();
echo $post->title;
select()
has some interesting modifiers like relatedWith()
to add automatically the WHERE
clauses needed to select data related with other row or rowCollection:
//Get the post id = 23
$post = $db->post[23];
//Select the category related with this post
$category = $db->category
->select()
->relatedWith($post)
->one()
->get();
Queries use Atlas.Query library to build the final queries, so you can see the documentation for all available options.
Function | Description |
---|---|
one |
Select 1 result. |
relatedWith(Row / RowCollection / Table $relation) |
To select rows related with other rows or tables (relation added in WHERE ). |
joinRelation(Table $table) |
To add a related table as LEFT JOIN . |
getPageInfo() |
Returns the info of the pagination. |
from |
Atlas.Query Select() |
columns |
Atlas.Query Select() |
join |
Atlas.Query Select() |
catJoin |
Atlas.Query Select() |
groupBy |
Atlas.Query Select() |
having |
Atlas.Query Select() |
orHaving |
Atlas.Query Select() |
orderBy |
Atlas.Query Select() |
catHaving |
Atlas.Query Select() |
where |
Atlas.Query Select() |
orWhere |
Atlas.Query Select() |
catWhere |
Atlas.Query Select() |
whereEquals |
Atlas.Query Select() |
limit |
Atlas.Query Select() |
offset |
Atlas.Query Select() |
distinct |
Atlas.Query Select() |
forUpdate |
Atlas.Query Select() |
setFlag |
Atlas.Query Select() |
bindValue |
Atlas.Query Select() |
Function | Description |
---|---|
relatedWith(Row / RowCollection / Table $relation) |
To update rows related with other rows or tables (relation added in WHERE ). |
set |
Atlas.Query Update() |
setFlag |
Atlas.Query Update() |
where |
Atlas.Query Update() |
orWhere |
Atlas.Query Update() |
catWhere |
Atlas.Query Update() |
orderBy |
Atlas.Query Update() |
limit |
Atlas.Query Update() |
offset |
Atlas.Query Update() |
Function | Description |
---|---|
orIgnore() |
To ignore silently the insertion on duplicated keys, instead throw an exception. |
set |
Atlas.Query Insert() |
setFlag |
Atlas.Query Insert() |
Function | Description |
---|---|
relatedWith(Row / RowCollection / Table $relation) |
To delete rows related with other rows or tables (relation added in WHERE ). |
setFlag |
Atlas.Query Delete() |
where |
Atlas.Query Delete() |
orWhere |
Atlas.Query Delete() |
catWhere |
Atlas.Query Delete() |
orderBy |
Atlas.Query Delete() |
limit |
Atlas.Query Delete() |
offset |
Atlas.Query Delete() |
Both Row
and RowCollection
can load automatically other related rows. Just use a property named as related table. For example:
//Get the category id=34
$category = $db->category[34];
//Load the posts of this category
$posts = $category->post;
//This is equivalent to:
$posts = $db->post
->select()
->relatedWith($category)
->get();
//But the result is cached so the database query is executed only the first time
$posts = $category->post;
This allows make things like this:
$titles = $db->post[34]->tag->post->title;
//Get the post id=34
//Get the tags of the post
//Then the posts related with these tags
//And finally, the titles of all these posts
Use magic methods to get a Select
query returning related rows:
$category = $db->category[34];
//Magic property: Returns all posts of this category:
$posts = $category->post;
//Magic method: Returns the query instead the result
$posts = $category->post()
->where('pubdate > ', date('Y-m-d'))
->limit(10)
->get();
The n+1 problem can be solved in the following way:
//Get some posts
$posts = $db->post
->select()
->get();
//preload all categories
$posts->category;
//now you can iterate with the posts
foreach ($posts as $post) {
echo $post->category;
}
You can perform the select by yourself to include modifiers:
//Get some posts
$posts = $db->post
->select()
->get();
//Select the categories but ordered alphabetically descendent
$categories = $posts->category()
->orderBy('name DESC')
->get();
//Save the result in the cache and link the categories with each post
$posts->link($categories);
//now you can iterate with the posts
foreach ($posts as $post) {
echo $post->category;
}
For many-to-many relations, you need to do one more step:
//Get some posts
$posts = $db->post
->select()
->get();
//Select the post_tag relations
$tagRelations = $posts->post_tag()->get();
//And now the tags of these relations
$tags = $tagRelations->tag()
->orderBy('name DESC')
->get();
//Link the tags with posts using the relations
$posts->link($tags, $tagRelations);
//now you can iterate with the posts
foreach ($posts as $post) {
echo $post->tag;
}
To save related rows in the database, you need to do this:
//Get a comment
$comment = $db->comment[5];
//Get a post
$post = $db->post[34];
//Relate
$post->relate($comment);
//Unrelate
$post->unrelate($comment);
//Unrelate all comments of the post
$post->unrelateAll($db->comment);
The select
query has a special modifier to paginate the results:
$query = $db->post->select()
->page(1)
->perPage(50);
$posts = $query->get();
//To get the page info:
$pagination = $query->getPageInfo();
echo $pagination['totalRows']; //125
echo $pagination['totalPages']; //3
echo $pagination['currentPage']; //1
echo $pagination['previousPage']; //NULL
echo $pagination['nextPage']; //2
SimpleCrud uses PSR-14 Event Dispatcher to dispatch events. The events are attached to tables allowing to validate data, modify queries, etc.
use SimpleCrud\Events\BeforeSaveRow;
use SimpleCrud\Events\CreateSelectQuery;
//Get the event dispatcher
$dispatcher = $db->post->getEventDispatcher();
//Assign the BeforeSaveRow event listener
$dispatcher->listen(BeforeSaveRow::class, function (BeforeSaveRow $event) {
$row = $event->getRow();
if (!$row->createdAt) {
$row->createdAt = new Datetime();
}
});
//Assign a CreateSelectQuery
$dispatcher->listen(CreateSelectQuery::class, function (CreateSelectQuery $event) {
$query = $event->getQuery();
//Add automatically a where clause in all selects
$query->where('active = true');
});
//Create a new post
$post = $db->post->create(['title' => 'Hello world']);
//Save the post, so BeforeSaveRow event is triggered
$post->save();
$post->createdAt; //This field was filled and saved
//Select a post, so CreateSelectQuery is triggered and only active posts are selected
$posts = $db->post->select()->get();
You can provide your own event dispatcher:
$myDispatcher = new Psr14EventDispatcher();
$db->post->setEventDispatcher($myDispatcher);
The available Events are:
SimpleCrud\Events\BeforeSaveRow
: Executed before save a row using$row->save()
.SimpleCrud\Events\BeforeCreateRow
: Executed before create a new row with$table->create()
.SimpleCrud\Events\CreateDeleteQuery
: Executed on create a DELETE query with$table->delete()
.SimpleCrud\Events\CreateInsertQuery
: Executed on create a INSERT query with$table->insert()
.SimpleCrud\Events\CreateSelectQuery
: Executed on create a SELECT query with$table->select()
.SimpleCrud\Events\CreateUpdateQuery
: Executed on create a UPDATE query with$table->update()
.
The purpose of the SimpleCrud\Fields
classes is to convert the data from/to the database for its usage. For example, in Mysql the format used to store datetime values is "Y-m-d H:i:s", so the class SimpleCrud\Fields\Datetime
converts any string or Datetime
instance to this format, and when you select this value, you get a Datetime instance. The available fields are:
- Boolean: To manage boolean values
- Date: To manage date values. Converts the database values to a
Datetime
- Datetime: To manage datetime values. Converts the database values to a
Datetime
- Decimal: Converts values to float numbers or NULL
- Field: It's the default field and doesn't transform the value
- Integer: Converts values to integers or NULL
- Json: To store json structures.
- Serializable: To store arrays or any other serializable data structure.
- Set: Manages multiple values. For example: ['red', 'blue', 'green'] will be stored as "red,blue,green" in database.
- Point: Manages geometry points more info
- Other advanced fields can be found here: https://github.com/oscarotero/simple-crud-extra-fields
The Field classes are asigned automatically according with the field type in the database. There are also "special names" that have specific types asigned:
- Integer format will be asigned to any field named
id
or ending by_id
. - Datetime format will be asigned to any field named
pubdate
or ending byAt
(for example:createdAt
,updatedAt
etc). - Boolean format will be asigned to any field named
active
or starting byis
orhas
(for example:isActived
,hasContent
, etc)
Example:
$post = $db->post->create([
'title' => 'My post',
'text' => 'My post text',
'createdAt' => new Datetime('now'),
'isActive' => true
]);
$post->save();
//Use magic properties to get the Field instance
$titleField = $db->post->title;
You may want to store some database configuration, for example the default language or base path where the assets are stored. To do that, there are the getConfig
and setConfig
methods:
$db->setConfig('name', 'value');
echo $db->getConfig('name'); //value
If you need to save values in multiple languages, just have to create a field for each language using the language as suffix. For example, to save the title in english (en) and galician (gl), just create the fields title_en
and title_gl
.
Then, you have to configure the current language using the SimpleCrud::ATTR_LOCALE
attribute:
//Set the current language as "en"
$db->setConfig(SimpleCrud::CONFIG_LOCALE, 'en');
//Select a post
$post = $db->post[23];
//Get the title in the current language
echo $post->title; //Returns the value of title_en
//You can access to any languages using the full name:
echo $post->title_en;
echo $post->title_gl;
//And assign a diferent value to the current language
$post->title = 'New title in english';
SimpleCrud
use internally Atlas.PDO to manage the connection and perform the queries in the database. You can see the documentation for more details.
$db->getConnection()->logQueries(true);
//-- Run queries --//
$queries = $db->getConnection()->getQueries();
You can use your own custom classes for tables, rows and row collections:
Use setTableClasses
to assign custom classes to table:
$db = new SimpleCrud\Database($pdo);
$db->setTableClasses([
'post' => CustomPost::class,
'comment' => CustomComment::class,
]);
$db->post; //Returns an instance of CustomPost
To create field instances, SimpleCrud use the SimpleCrud\Field\FieldFactory
factory class that you can customize or even replace with your own factory:
use SimpleCrud\Fields\FieldFactory;
use SimpleCrud\Fields\Boolean;
$db = new SimpleCrud\Database($pdo);
//Create a factory for your custom field
$factory = new FieldFactory(
Year::class, //Your custom field class name
['integer'], //All fields of type integer will use this class
['year', '/$year/'], //All fields named "year" or matching this regex will use this class
['min' => 2000], //Default config
);
$db->setFieldFactory($factory);
//Modify a existing field
$db->getFieldFactory(Boolean::class)->addNames('enabled');
//Use it:
$db->post->fields['year']; //returns an instance of Year
$db->post->fields['enabled']; //returns an instance of SimpleCrud\Fields\Boolean
To define the Rows and RowCollections classes used in a specific table, first create a custom table and use ROW_CLASS
and ROWCOLLECTION_CLASS
protected constants to set the class.
namespace MyModels;
use SimpleCrud\Table;
class Post extends Table
{
protected const ROW_CLASS = PostRow::class;
protected const ROWCOLLECTION_CLASS = PostRowCollection::class;
protected function init()
{
//Insert code to be executed after the instantion
}
public function selectLatest()
{
return $this->select()
->orderBy('createdAt DESC')
->limit(10);
}
}
Now configure the database to use this class for the table post
:
$db = new SimpleCrud\Database($pdo);
$db->setTableClasses([
'post' => MyModels\Post::class,
]);
$latests = $db->post->selectLatest()->get(); //Returns an instance of MyModels\PostRowCollection
foreach ($latests as $post) {
//Instances of MyModels\PostRow
}