Database Laravel Package

Getting Started

1. Install via Composer: composer require nette/database.
2. Create a config (e.g., in config.neon or PHP) to register the service:

   services:
     - Nette\Database\Explorer([ 
       connection => [ 
         driver => 'mysqli', 
         host => 'localhost', 
         username => 'root', 
         password => '', 
         database => 'myapp' 
       ] 
     ])
   

   or via PHP:

   $database = new Nette\Database\Explorer([
     'driver' => 'mysqli',
     'host' => 'localhost',
     'username' => 'root',
     'password' => '',
     'database' => 'myapp',
   ]);
   

3. Start querying:

   $rows = $database->table('users')->where('active', 1)->fetchAll();
   foreach ($rows as $user) {
     echo $user->name;
   }
   

Implementation Patterns

• Fluent querying: Chain methods for readability and reusability.

  $query = $database->table('orders')
    ->where('status', 'pending')
    ->limit(10)
    ->orderBy('created_at', 'DESC');
  $pendingOrders = $query->fetchAll();
  

• Explorer abstraction: Treat tables as objects—no raw SQL needed for basic CRUD.

  // Insert
  $database->table('users')->insert([
    'email' => 'new@example.com',
    'created_at' => new DateTime,
  ]);
  
  // Update
  $user = $database->table('users')->get($id);
  $user->update(['name' => 'Updated Name']);
  

• Joins & relations: Use join() and select() with alias-friendly syntax.

  $result = $database->table('posts')
    ->select('posts.*, users.name AS author')
    ->join('users', 'posts.author_id = users.id')
    ->where('posts.status', 'published')
    ->fetchAll();
  

• Transactions: Wrap critical writes for consistency.

  $database->transaction(function (\Nette\Database\Explorer $db) {
    $db->table('orders')->insert([...]);
    $db->table('inventory')->where('product_id', $id)->decrement('stock');
  });
  

• Profiling & debugging: Use built-in helper methods:

  echo $database->getConnection()->debug(); // Current SQL with bound params
  

Gotchas and Tips

• No native eager loading: nette/database does not auto-join related data like full ORMs. Manually build joins or use fetchPair() + loops for optimization.
• Table names must be quoted only if reserved words or contain special chars—use ->table('order').
• FETCH_ALL vs fetch():
  • fetchAll() → array of Row objects (or arrays if configured).
  • fetch() → single Row or null.
  • Use fetchAssoc($key) to group results by a column.
• Parameter binding is automatic in where(), andWhere(), etc., but be cautious when using raw SQL fragments (where('id = ?', $id) is safe; where("id = $id") is not).
• Extend with custom table classes: Create subclasses of Nette\Database\Table\Selection to add domain-specific scopes (e.g., ActiveSelection).
• Connection reused by default: For CLI commands or long-running processes, explicitly close idle connections with $explorer->getConnection()->close().
• Laravel integration: Not native—but can be wrapped as a service provider if avoiding Doctrine/Eloquent. Prefer for legacy or microservice DB access where ORM overhead is undesirable.