Can Dreamfactory use any other kind of database other than MySQL to store its to store user, role, and service information?

[Chakr]

I’m using dreamfactory on bluemix. I’m currently using a Clear db MySQL database to setup dreamfactory. Is it possible to set it up on a IBM DB2 database? I don’t mean as a service. I want to use DB2 as the database for dreamfactory, as a requirement for it to run at all.

[Im_PJ]

User registration, authentication and authorization in a different database

2.0 will let you select any SQL database to store user and role data (currently user and role data is stored in MySQL only). 2.0 will also make it easy to authenticate users with OAuth and Active Directory. Permanent login (i.e. no session timeout) will also be supported in 2.0. So you won’t have to use LDAP for any of the features above. It’s all built into the 2.0 platform. Why do you need to port the user data to DynamoDB? We’re a little behind, but 2.0 code should be public on GitHub in a …

Not possible as of now. But they promise to support this feature with DF v2.0.
And I’m eagerly waiting for that.

[benbusse]

2.0 will let you pick your database. 2.0 will be in beta on Bitnami first week of Sep.

[Im_PJ]

@benbusse I’m trying to do this in DF2. I want to use different db for user management and be able to use this api call- api/v2/user/session/ with my own db?
As per your blog, > Pick default SQL database of choice (no longer limited to MySQL).
Lack of documentation makes it difficult to use such features of DF2.

[aislam]

DreamFactory 2.0 is built using Laravel 5.1 PHP framework. So, it supports all databases that Laravel supports out of the box. You can easily configure this in the config/database.php config file. Details are documented in the file. However, Laravel doesn’t support IBM DB2 out of the box. But I found this third party package https://github.com/cooperl22/laravel-db2 that you can check out. I have not tried this package but it’s popular enough to show up in several forums while I was doing my search.

[Im_PJ]

Thanks but I was looking for a way to setup my own user db through admin console. Its a feature of DF2 so I dont want to change config in php files. My question is not related to IBM DB2 but in general how to have your own user db and be able to use such api calls api/v2/user/session/ with it.

[jeffreystables]

Once you do set up the database.php config file to point to your DB of choice, the whole DreamFactory user DB will be created in that target DB. If you’re not willing to change the config of which DB that DF2 uses as its system DB, then you will not be able to replace the default DB with the DB of your choice for system calls like /api/v2/user/session.

[Im_PJ]

@aislam @jeffreystables I did what you suggested but it breaks the whole system. I hope you meant to change the below code in database.php

'dreamfactory' => [
            'driver'    => 'dblib',
            'host'      => env('DB_HOST', 'hostname'),
                        'port'  => env('DB_PORT', 'portno'),
            'database'  => env('DB_DATABASE', 'dbname'),
            'username'  => env('DB_USERNAME', 'uname'),
            'password'  => env('DB_PASSWORD', 'pwd'),
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
            'strict'    => false,
        ],  

I created an empty sql serv DB and changed parameters above to connect to it. Do I need to create the table structures that DF uses or it will be auto generated. can you pls write the steps in detail. This shouldn’t be so difficult to do if its a feature of DF2.

[aislam]

Instead of ‘dblib’ use ‘sqlsrv’ for driver. Laravel will automatically use ‘dblib’ driver if is installed. Once you make that change run “sudo php artisan cache:clear” in the DreamFactory installation root directly. This will clear all system caches. Please make sure that your database is created and empty then Access your DreamFactory instance on a browser. You should see a welcome page followed by a form to create the first admin user.

[Im_PJ]

@aislam I still get this error and HTTP 500 in access log.

This is what I changed in database.php

   'default' => 'df2',
    'df2' => [
                'driver'   => 'sqlsrv',
                'host'     => '10.20.0.1',
                'port'        => '1433',
                'database' => 'bitnamidb',
                'username' => 'user',
                'password' => 'user@4562'    ]

After that I ran command sudo php artisan cache:clear and got message application cache cleared. Then I access dreamfactory in browser but get the above error. I created empty database without any tables as u suggested. Where I’m going wrong? connection to db is working as I have tested it inside df sql services.

[aislam]

First, make sure your web server can read/write to storage/ and bootstrap/cache directories. If you are still getting 500 then check the log in storage/logs/ directory. Or you can also enable debugging in the .env file. Set APP_DEBUG to true there. It should give you a stack trace after the ‘Whoops, looks like something went wrong.’ error.

[Im_PJ]

@aislam my apache user has 0777 permissions on those folders. Still the same error. This is from the starting of logs. More such error at bottom.

Could not get cors config from DB - SQLSTATE[HY000]: General error: 208 General SQL Server error: Check messages from the SQL Server [208] (severity 16) [(null)] (SQL: select * from [cors_config] where enabled = 1)  

PDOException' with message 'SQLSTATE[HY000]: General error: 208 General SQL Server error in /dreamfactory/htdocs/vendor/laravel/framework/src/Illuminate/Database/Connection.php:312 

I don’t see any tables created in the remote db.

[aislam]

That error at the starting of the log is normal (unless you see that happening again anywhere else in the log file). On the first run DreamFactory encounters this error (hence you see it in the log) and then goes ahead creates the system tables. Did you try setting the APP_DEBUG=true in the .env file and accessing the system on browser to see the error stack trace? If not then please try it to see the actual error message.

[Im_PJ]

@aislam I couldn’t find .env file so I created file “local.env” in dreamfactory/htdocs and have put APP_DEBUG=true there.
I’m not familiar with laravel setup so let me know if this is correct. This is one more SQL error from the logs.

(SQL: select count(*) as aggregate from [user] where [is_active] = 1 and [is_sys_admin] = 1)'

[aislam]

There should be a .env file in the dreamfactory/htdocs directory. Sometimes it’s hidden by OS. So, you may need to unhide your hidden files to see it. Putting a local.env won’t make any difference. My email address is arif@dreamfactory.com. Please send me your log file so that I can look at it.

[Im_PJ]

Thanks. I’m getting the stack trace now. I have mailed you the log file. pls have a look.

[Val]

I tried to follow the steps here for v. 2.0.1 to connect to SQL DB and I don’t see any errors nor changes to existing behavior.
I updated:
‘default’ => env(‘DB_DRIVER’, ‘sqlsrv’),
…

	'sqlsrv' => [
		'driver'   => 'sqlsrv',
		'host'     => env('DB_HOST', 'localhost\sqlexpress'),
		'database' => env('DB_DATABASE', 'DreamFactory'),
		'username' => env('DB_USERNAME', 'dreamuser'),
		'password' => env('DB_PASSWORD', 'dreamuser'),
		'port'		=> env('DB_PORT', '6806'),
		'prefix'   => '',
	],