Database access

In order to perform database access you may want to create models and migrations. The models are the interface between your app and the database. It uses prepared statements in order to prevent SQL injection. The migrations are used to create a fresh database with fresh tables.

In order to create a migration go to the app/migrations folder and create a new file, e.g. example_migration.php. Now add a class with the name Example_migration_Migration. The „_Migration“ will be appended to the script file name (ucfirst, too) in order to resolve the class name. Inside the class you specify two methods: up() and down(). The down method is used to remove the table and the up method is used to create the table. Your class must provide a constructor where it recieves the PDO connection handler instance. Save it to a private member variable. Then in the up() method you instantiate a Asatru\ Database\Migration object passing the name of the table as first argument and the connection handler reference as second argument. After that you can call the methods of the created object to define your table. At first you may want to drop the old table via the drop() method. Then you will want to define the columns. Therefore you use the add() method. You pass an SQL query string in order to create a column, e.g. „text varchar(260) not null“. When you have added all your desired columns then you call the create() method in order to create the table. In order to alter a table you just use the method append() in order to insert a new column. Note that you may not mix creation of new tables with altering tables.

In your down() method you call the drop() method of a database object.

Next step is to create a model for that migration. These are created in the app/models folder. Create a file called, for instance, MyModel.php. Then open the script file and create a class MyModel. It must have the same name as the script file to be resolved. Also it extends the Asatru\Database\Model class. You have to implement the static method tableName() which returns a string that identifiers the migration associated with this model. After that you can implement your static getters and setters. You can perform select, update, insert, delete and raw queries:

public static function Model::where(name, comparision, value): Use a conditional and-query. Call the method for each condition
public static function Model::whereOr(name, comparision, value): Use a conditional or-query. Call the method for each condition
public static function Model::limit(count): Limit the query result
public static function Model::groupBy(ident): Group items by ident
public static function Model::orderBy(ident, type): Order items by ident. Type is either asc or desc.
public static function Model::first(): Get first item
public static function Model::get(): Perform the query and get the items
public static function Model::all(): Get the entire table
public static function Model::find(id, key): Find an entry by id. Use key parameter if you want to specify the name of the key so look for
public static function Model::count(): Get the amount of found items
public static function Model::aggregate(type, column, opt:name): Find an aggregate of the column (avg, min, max, sum, etc.)
public static function Model::whereBetween(column, value1, value2): Use a conditional between andquery. Call the method for each condition
public static function Model::whereBetweenOr(column, value1, value2): Use a conditional between orquery. Call the method for each condition
public static function Model::update(ident, value): Add this item to the updated item list
public static function Model::insert(ident, value): Add this item to the inserted item list
public static function Model::go(): Perform either an update or insert operation
public static function Model::delete(): Perform a delete operation
public static function Model::raw(qry, args): Perform a raw database operation

The result of the operation depends of the its kind:

  • For fetching data it returns an instance of Asatru\Database\Collection
    • call the count() method to get the amount of collected entries
    • call the get(ident) method to get the related item. This can be an instance of the collection class, too.
    • Call each(callback) in order to iterate through all collected items
  • For inserting, updating and deleting it returns a boolean indicating whether the operation could be executed
  • For getting the count it returns the amount of found entries

In order to manage migrations you can use the following functions:

  • migrate_fresh(): Drops all tables and recreates them
  • migrate_list(): Runs only newly created migration scripts
  • migrate_drop(): Drops all migrations

The database connection is adjusted via the .env file.

# Set to true to enable database functionality
DB_ENABLE=false

# Hostname or address of the database service
DB_HOST=localhost

# Username for authentication
DB_USER=root

# Password for authentication
DB_PASSWORD=""

# Used service port, normally you won't need to change this
DB_PORT=3306

# Database name to be used within the database service
DB_DATABASE=asatru

# Driver to be using. Currently only MySQL is supported
DB_DRIVER=mysql

You can create a new model along with a migration via:

php asatru make:model <model name> <table name>
© 2020 by Daniel Brendel - all rights reserved