DOCSEARCHbyAlgolia
Submit Docs

Medoo Database Framework

repository·master·Indexed 26 days ago

https://github.com/catfan/medoo

A lightweight PHP database framework providing a clean API for complex SQL, data mapping, and prepared statements. It supports multiple database engines including MySQL, MariaDB, PostgreSQL, SQLite, MSSQL, Oracle, and Sybase. Key features include CRUD operations, aggregate functions, transaction management via action(), and raw SQL execution through Medoo::raw() and query(). Requires PHP 7.3+ and the PDO extension.

Tokens
2.2K
Snippets
3
Records
24
Agent score
39%

What's inside Medoo

  1. Initialize Medoo and perform basic CRUD operations

    master

    To use Medoo, require the Composer autoloader, import the Medoo\Medoo namespace, and instantiate the Medoo class with your database configuration.

    Supported database types include mysql, mariadb, pgsql, sqlite, mssql, oracle, and sybase.

    Basic operations include:

    • insert($table, $data): Inserts data into a table.
    • select($table, $columns, $where): Retrieves data from a table based on conditions.
    // Load Composer's autoloader.
require 'vendor/autoload.php';

// Import the Medoo namespace.
use Medoo\Medoo;

// Create a database connection.
$database = new Medoo([
    'type' => 'mysql',
    'host' => 'localhost',
    'database' => 'name',
    'username' => 'your_username',
    'password' => 'your_password'
]);

// Insert data.
$database->insert('account', [
    'user_name' => 'foo',
    'email' => 'foo@bar.com'
]);

// Retrieve data.
$data = $database->select('account', [
    'user_name',
    'email'
], [
    'user_id' => 50
]);

echo json_encode($data);

  2. Install Medoo via Composer

    master

    To add Medoo to your project, use Composer to require the package and then update your dependencies.

    Run the following commands in your terminal:

    composer require catfan/medoo
composer update
    $ composer require catfan/medoo
$ composer update

  3. Initialize a Medoo database connection

    master

    To use Medoo, instantiate the Medoo class with an options array. You must specify the type (e.g., mysql, pgsql, sqlite, mssql, oracle, sybase).

    Required options typically include type, database, host, username, and password. You can also provide a custom prefix for table names or a pre-existing PDO instance.

    $database = new Medoo([
    // Required
    'type' => 'mysql',
    'database' => 'name',
    'host' => 'localhost',
    'username' => 'your_username',
    'password' => 'your_password',

    // Optional
    'charset' => 'utf8mb4',
    'port' => 3306,
    'prefix' => 'PREFIX_'
]);

  4. Configure Medoo connection options

    master

    The Medoo constructor accepts several configuration keys depending on the driver:

    KeyDescription
    typeThe database driver (e.g., mysql, pgsql, sqlite, mssql, oracle, sybase, mariadb maps to mysql)
    databaseDatabase name (or database_name)
    hostDatabase host (or server)
    usernameDatabase username
    passwordDatabase password
    prefixTable name prefix
    portDatabase port
    charsetCharacter set (e.g., utf8mb4)
    socketUnix socket path (for MySQL)
    dsnA custom DSN string or an array containing driver
    pdoAn existing PDO instance
    loggingBoolean to enable/disable query logging
    testModeBoolean to enable test mode (prevents execution, returns generated SQL)
    debugModeBoolean to enable debug mode (echoes the generated SQL)
    commandAn array of SQL commands to execute immediately after connecting

  7. Use Raw SQL expressions with Medoo::raw()

    master
    When you need to execute a specific SQL fragment that Medoo's query builder doesn't support, use the Medoo::raw() method. This returns a Raw object containing the SQL string and an optional map of placeholder bindings to prevent SQL injection.

  8. Fetch random rows with rand()

    master
    The rand() method retrieves rows in a random order. It automatically selects the appropriate random function based on your database driver (e.g., RAND() for MySQL, NEWID() for MSSQL, DBMS_RANDOM.VALUE for Oracle).

  9. Debug and inspect SQL statements in Medoo

    master

    Medoo provides several methods to inspect the SQL queries being generated and executed.

    • debug(): Enables debug mode. The next database operation will output the interpolated SQL statement directly.
    • beginDebug(): Enables both debug mode and debug logging.
    • debugLog(): Disables debug logging and returns an array of all collected interpolated SQL statements.
    • last(): Returns the most recent SQL statement executed, with bound values interpolated.
    • log(): Returns an array of all executed SQL statements. Each entry contains the interpolated SQL string and the execution time (formatted as a string, e.g., 0.000001s).

  10. Create a table with create()

    master
    Use the create() method to define and create a new table. You can specify the table name, an array of column definitions, and optional table configuration (like engine or charset). If using MySQL, PostgreSQL, or SQLite, it automatically appends IF NOT EXISTS to prevent errors if the table already exists.

  11. Fetch rows with select()

    master

    The select() method retrieves data from a table. It supports JOINs, specific column selection, and WHERE clauses.

    Signatures and behaviors:

    • All columns: select($table, $where)
    • Specific columns: select($table, $columns, $where)
    • With JOINs: select($table, $join, $columns, $where)
    • With Callback: Pass a callable as the last argument to process rows one by one instead of returning a full array.

    If a single column is requested (not *), the method returns a flat array of values instead of an associative array of rows.

  12. Execute database transactions with action()

    master
    Use the action() method to wrap multiple database operations in a transaction. Pass a callable containing your logic. If the callback returns false, the transaction is rolled back. If an exception is thrown, it is also rolled back. Otherwise, the transaction is committed.