What authentication system & database manager should I use when creating a RESTful API using Restler?

Currently I'm creating a RESTful API using Composer packages. I have already found "luracast/restler" which is a very powerful package to create an API.

But I'm still looking for a package to manage my database and an authentication system. Here are some requirements/features I would like to see.

Database manager:

creating database structures/ updating database structures from config files
basic SQL query builder
Array to database

(ex.

array(
 'table1' => array(
  array(
   'row1'=> 'value1',
   'row2'=> 'value2'
  ),
  array(
   'row1'=> 'value3',
   'row2'=> 'value4'
  )
 )
)

would become INSERT INTO table1 ('row1', 'row2') VALUES ('value1', 'value2'), ('value3', 'value4') )

Authentication system:

has to work with Database manager
easy and simple authentication (autogenerated api key)
account management system (maybe seperate system/ API)

Does someone have any suggestions?

Solution

In order to help you with this kind of requirement we have created restler application templates in restler/application package. Every branch in that has different template for different use cases, The one you need is in eloquent branch.

You can install it using the following composer command

composer create-project restler/application=dev-eloquent my_api

Once the installation is complete you can get inside the directory

cd my_api

First you need to edit the database configuration file (app/config/database.php). Once you have decided what database you want to use (sqlite, mysql, postgres, etc) update the relevant information under connections. If I want to use mysql and database name is my_api, username is root and password is test, my configuration will look like

<?php

return array(
    'default' => 'mysql',
    'connections' => array(
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => '127.0.0.1',
            'database'  => 'my_api',
            'username'  => 'root',
            'password'  => 'test',
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),
);

Note:- I have removed irrelevant portions from the configuration file for clarity, you should keep them in your file

Once you have configured your database you can check the connection with the following command

php artisan migrate:install

Migration table created successfully.

Next you will create a migration file for creating the new table, how about creating an api where people will leave feedbacks?

php artisan migrate:make --create=feedbacks create_feedbacks_table

Created Migration: 2015_08_05_120727_create_feedbacks_table
Generating optimized autoload files

Edit the app/database/migrations/2015_08_05_120727_create_feedbacks_table.php file to have the following

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateFeedbacksTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('feedbacks', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name');
            $table->string('email');
            $table->text('message');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('feedbacks');
    }

}

Here we are creating feedbacks table with the name, email, and feedback columns. Next we will run the migration tool so that this table will be created.

php artisan migrate

**************************************
*     Application In Production!     *
**************************************

Do you really wish to run this command? yes
Migrated: 2015_08_05_120727_create_feedbacks_table

Now we can generate a model class with the following command

php artisan model:make Feedback

Model created successfully.
Generating optimized autoload files

Here we specify the model name as the singular version of the table name. app/models/Feedback.php is generated based on the table structure of feedbacks table

The comments at the top of the file is used by restler to understand what properties are exposed by the model

/**
 * Class Feedback
 *
 * @property-read  int    $id
 * @property       string $name
 * @property       string $email
 * @property       string $message
 * @property-read  string $created_at {@type date}
 * @property-read  string $updated_at {@type date}
 * 
 */

Next let us create app\controllers\Feedbacks.php with the following content

<?php

use Luracast\Restler\RestException;

class Feedbacks {
    /**
    * Get all feedbacks
    *
    * return array {@type Feedback}
    */
    public function index(){
        return Feedback::all();
    }

    public function get($id){
        if(!$feedback = Feedback::find($id)){
            throw new RestException(404, 'feedback not found');
        }
        return $feedback;
    }

    public function post(Feedback $feedback){
        $feedback->save();
        return $feedback;
    }

    public function delete($id){
        if(!$feedback = Feedback::find($id)){
            throw new RestException(404, 'feedback not found');
        }
        $feedback->delete();
        return ['success'=>true];
    }

}

Next edit the public/index.php to add the following line

$r->addApiClass('Feedbacks');

Thats all, you can start the webserver with

php artisan serve

Web app development server started on http://localhost:8000

Point your web browser to http://localhost:8000/explorer/ and have fun :)