While Jet includes a robust ORM DataModel, in a well-designed and written application the need for SQL is directly suppressed. However, this does not mean that working with SQL is impossible. On the contrary. Even if you use ORM, it is useful or necessary to use SQL directly for various specific tasks, such as various analytical outputs, complex update scripts, and so on. Not to mention that Jet DataModel of course also works with the database and thus needs some kind of unified backend.
For this reason, Jet includes a relatively simple facade for working with the Jet\Db database. This currently uses PDO as a backend, but has several other purposes:
- Incorporate DB work into the whole and use other system features such as application configuration definition, factories to make DB work fit into the overall Jet concept.
- Create a unified layer that will allow to replace PDO with something else in the future if a better solution comes, or allow developers to create their own backend with the fact that the interface will always be preserved and therefore it will not be necessary to modify the application.
- Connect DB work to Jet Profiler.
Let's see how to work with Jet\Db right away. Within the application configuration you can have at least one, but more connections to the database defined. You need to get an instance of this connection before working with SQL. By connection we mean an instance of the appropriate backend + the appropriate configuration. Then you work with the connection in the usual way:
$db = Db::get(); //Default connection
$db->execute('INSERT INTO some_table SET
'name' => 'Some Name',
'age' => 42
$adults = $db->fetchAll('SELECT id, name, age FROM same_table WHERE age>18');
$erp_db = Db::get('erp_connection');
$bills = $erp_db->fetchAssoc(
query: 'SELECT * FROM bills WHERE invoice_date>=:date AND paid=0',
'invoice_date' => '2021-12-01'
For an overview of methods you can use to work with SQL, see the Jet\Db_Backend_Interface description
The Db configuration uses application configurations. So it's easy to implement things like an installer, or extend the configuration with additional parameters and so on. Within Jet\Db, the following classes are designed to work with the configuration:
Main configuration definition. This is not a definition of a specific connection, but holds a list of connection definitions and information about which connection is taken as the main one.
Abstract class of the backend configuration - i.e. a specific connection.
The database connection configuration definition class for the default PDO backend.
But it is neither necessary nor appropriate for an application to operate on these classes in the sense of directly creating instances of them. The corresponding factory is used for this purpose even in the case of Jet\Db. In addition, in a normal application there is no need to worry about configuration (unless you are developing a new installer, for example).
Like the configuration, it is also true for the backend that you don't have to take care of these classes in the application and especially not directly create any instances. The connection is available to you via the Jet\Db::get() method.
And the backend is handled internally via the appropriate factory. So you are able to easily and transparently replace the Jet\Db_Backend_PDO class with your own implementation if you need to.