Databases

Applications need to store data. By default, App Factory provides support for relational database management systems (RDBMS). You can use App Factory portal to create databases in MySQL or set up the database from a tool like MySQL workbench and connect to it from App Factory.

The default user roles have the following permissions on databases: 

This section covers the following topics:

Creating databases

Follow the instructions below to create a database.

1. Log in to the App Factory portal (for the online version, try https://appfactorypreview.wso2.com). See the table above for information on which role can configure databases in which lifecycle stage.
2. Select an application from the list of apps that you own or are invited to participate in.
3. Click the Resources tab from the left panel.  
    
4. The Resource Overview page opens. 
5. To create a new database, click the Create Database button in the Overview page. Alternatively, you can go to the Database Configurations tab and click the Databases button.

6. Provide the necessary details in the form that appears and click Create Database.
   

   Field Name	Description
   Database Name	Name of the database. Maximum number of allowed characters is 7.
   Default User Password	If you create this database without specifying a database user under the Advanced Options, the system automatically creates a default user and assigns it to the database. You can specify its password here. Valid password should consist of letters, numbers and special characters (Only !, @, #, $, %, ^, *, _ ). Use 6 to 15 characters. Capitalization matters.
   Advanced Options	Use these options when you want to override the default database settings and define a custom database configuration. Database Environment: By default, the environment of the database is set to Development but you can select any other stage from this drop-down list. It is populated by the life cycle stages configured in the system. By default, they are Development, Testing and Staging but you can configure your own using instructions in Adding/Modifying Life Cycle Stages. User: This drop-own list is populated by the database users created for this application. You can select a user of your choice. If you create the database without defining a user here (i.e., creating the database without selecting the Advanced Options), the system creates a user by default and assigns it to the database. You cannot change this default user's configurations later. You can however delete it and set another user to the database. Also see Creating a database user. Permission Template: This drop-own list is populated by the permission templates created for this application. You can select a template of your choice. If you create the database without defining your own permission template (i.e., creating the database without selecting the Advanced Options), the system creates a permission template by default and assigns it to the database. You can change this default permission template's configurations later or delete it. Also, see  Creating a permission template.

7. After creating, the new database appears under Databases. Click on its name to edit its user and permission template or delete it. You cannot change its lifecycle stage.

Creating a database user

1. In the Resource Overview page, click the Create DB User button. Alternatively, you can go to the Database Configurations tab and click the DB User button.

2. Provide the necessary details. For example,
   
   The user name can be a maximum of 7 characters and the password a maximum of 15 characters. 

3. After creating a database user, click on its name to navigate to its details page from which you can change the user's settings or delete.

Creating a database permission template

1. In the Resource Overview page, click the Create Template button. Alternatively, go to the Database Configurations tab and click the DB Template button.
2. Provide a template name and select permissions. 
   
   

   The permissions are as follows:
   

   Permission	Description
   SELECT	Allow use of SELECT
   INSERT	Allow use of INSERT
   UPDATE	Allow use of UPDATE
   DELETE	Allow use of DELETE
   CREATE	Allow database and table creation
   DROP	Allow databases, tables, and views to be dropped
   GRANT	Allow access or privileges on the database objects to the users.
   REFERENCES	Allow access to create a foreign key reference to the specified table
   INDEX	Allow indexes to be created or dropped
   ALTER	Allow use of ALTER TABLE
   CREATE_TEMP_TABLE	Allow use of CREATE TEMPORARY TABLE
   LOCK_TABLES	Allow use of LOCK TABLES on tables for which you have the SELECT privilege
   CREATE_VIEW	Allow views to be created
   ALTER_VIEW	Allow views to be altered
   CREATE_ROUTINE	Allow stored routine creation
   ALTER_ROUTINE	Allow stored routines to be altered or dropped
   EXECUTE	Allow the user to execute stored routines
   EVENT	Allow use of events for the Event Scheduler
   TRIGGER	Allow trigger operations

   
   

3. After creating a template, click on its name to navigate to its details page using which you can change the permission settings or delete it.

Updating databases

Following commands show how to edit the database created earlier.

1. First, log in to My SQL and list the databases available as follows. [username] and [password] are the credentials of the user you attached to the database when creating it.

   mysql -h mysql.dev.appfactorypreview.wso2.com -u [username] -p[password]
   show databases; 

2. A list of databases managed by the user is shows. Select the one you need to connect to. [dbname] is the name of your database.
   use [dbname];
   

3. For more information on commonly-used commands refer the site : http://beginner-sql-tutorial.com/sql.htm
    

Connecting to an external database

You can also set up the database from a tool like MySQL workbench and commect to it from App Factory. To connect to a remote database, create a datasource for it.

1. Click Resources -> Datasources and then Create Datasource.
2. Since we use information of an existing database to create the datasource, select the Database URL as Custom Database from the drop-down list and enter URL in the text are below. For example,
   
3. Enter the credentials of a user of the remote database and finish. You now have a datasource in App Factory that refer to a remote database.
4. Developers should refer the datasource name as jdbc/<datasource_name> when used in implementation. For example,
   

   

Also see sample Reusing a Datasource in all Lifecycle Stages. It explains how to define a datasource resource for an application and how to use it to point to different databases in different lifecycle stages of the application.