24

Oct 2019

Generating Schema

Before run schema, users must create file schema in directory “./your_name_project/database/schema/”. To create a file, users can run its command on terminal :

aurora schema:create table_name

For example, if users want to create table my_table :

aurora schema:create my_table

the file will automatically be created in the directory “./your_name_project/database/schema/”, for example, the file name is 08202019my_table.js. File schema have predetermined structure by dividing into three parts, that is :

1. Module.exports.create

This section is used for creating a table and column in the database. Users can add some function field that has been provided.

2. Module.exports.update

This section is used for updating a column in the table. Users can add some function field that has been provided.

3. Module.exports.delete

This section is used for dropping a table or column. Users can add some function field that has been provided.

Default fill from generate file schema, it’s (in 08202019my_table.js):

module.exports.create = { 

'table_name' : 'my_table', 

'engine' : 'innoDB', 

'blueprint' : function(){


}

};



module.exports.update = {

'blueprint' : function(){



}

};



module.exports.delete = {

'blueprint' : function(){

dropIfExistsTable('my_table');

}

};

Default, table_name on module.exports.create automatically fill value from table name on command generate schema file, then have one function for drop table dropIfExistsTable('my_table'). From generate a default engine for table is innoDB.


Manage Schema

Aurora JS have some function for create, update, drop column and table. This for make it easy for user to create table with column without use SQL syntax. For Alpha version just support MYSQL. Example some function can be use in module.exports.create, that is :

Function 

Description

increment(‘column_name’);

Auto Increment, Primary Key, and Integer Type

integer(‘column_name’,12);

INTEGER type column with optional length

smallInteger(‘column_name’);

SMALLINT type column

mediumInteger(‘column_name’);

MEDIUMINT type column

bigInteger(‘column_name’);

BIGINT type column

decimal(‘column_name’);

DECIMAL type column

float('column_name’);

FLOAT type column

double('column_name’);

DOUBLE type column

real('column_name’);

REAL type column

bit('column_name’,6);

BIT type column with optional length 

boolean('column_name’);

BOOLEAN type column

serial('column_name’);

SERIAL type column

date('column_name’,1);

DATE type column with optional length

dateTime('column_name’,1);

DATETIME type column with optional length

timeStamp('column_name’,1);

TIMESTAMP type column with optional length

time('column_name’,1);

TIME type column with optional length

year('column_name’);

YEAR type column

char('column_name’,10);

CHAR type column with optional length

varchar('column_name’,10);

VARCHAR type column with optional length

tinyText('column_name’);

TINYTEXT type column

text('column_name’);

TEXT type column

mediumText('column_name’);

MEDIUMTEXT type column

longText('column_name’);

LONGTEXT type column

binary('column_name’,10);

BINARY type column with optional length

varBinary('column_name’,10);

VARBINARY type column with optional length

tinyBlob('column_name’);

TINYBLOB type column

mediumBlob('column_name’);

MEDIUMBLOB type column

blob('column_name’);

BLOB type column

longBlob('column_name’);

LONGBLOB type column

enums('column_name’,” ‘one‘,’two’,’three’ ”);

ENUM type column

set('column_name’,” ‘one‘,’two’,’three’ ”);

SET type column

geometry('column_name’);

GEOMETRYtype column

point('column_name’);

POINT type column

lineString('column_name’);

LINESTRING type column

polygon('column_name’);

POLYGON type column

multiPoint('column_name’);

MULTIPOINT type column

multiLineString('column_name’);

MULTILINESTRING type column

multiPolygon('column_name’);

MULTIPOLYGON type column

geometryCollection('column_name’);

GEOMETRYCOLLECTION type column

 

On module.exports.update user can use some function for update or add column/index:

Function

Description

column('my_column'), varchar('new_my_column', 50);

To update type or rename a column

(Example : user have column “my_column” on table, and he want rename  “my_column” to “new_my_column” and change type column to varchar.

addColumn(), varchar('new_column', 12);

To add a column

(Example : user want add column varchar)

addIndex(), index(['my_column'], 'new_index_column');

To add index to a certain column with custom name index 

(Example : user want add index to column ‘my_column’ with custom name index it’s ‘new_index_column’)

addIndex(), index(['my_column',’my_column2’], 'new_index_column_multiple');

To add index to multiple column with custom name index 

(Example : user want add index to column ‘my_column’ and ‘my_column_2’ with custom name index it’s ‘new_index_column_multiple’.

addIndex(), index(['my_column']);

To add index to a certain column without custom name index 

(Example : user want add index to column ‘my_column’).

addIndex(), index(['my_column',’my_column2’])

To add index to multiple column without custom name index 

(Example : user want add index to column ‘my_column’ and ‘my_column_2’) .

 

On module.exports.delete users can use some function to drop column,index, and primary key:

Function

Description

dropColumn('my_column');

To drop a column on table

(Example : user want remove column ‘my_column’ on table)

dropIndex('my_index');

To drop index on table

(Example : user want remove index ‘my_index’ on table)

dropPrimary();

To remove primary key on table

(Example user want remove primary key on table)

dropTable('my_table');

To remove a table.

(Example user want remove table ‘my_table’ on database)

dropIfExistsTable('my_table');

To remove table if exists this table on database.

(Example user want remove table ‘my_table’ on database)

 

not only limited to adding column with type anymore, but users can also add some attribute for column. For example if users want to add unique, nullable and etc, users can use some of the function in Aurora DB that is :

Function

Description

nullable();

Allow null on column

unique();

Add unique to column

primary()

Set primary key to column

 

For creating relationship table, users can use function foreign() on module.exports.create, example :

foreign(local_column),references(table_name_references,column_references),onDelete('CASCADE/RESTRICT'),onUpdate('CASCADE/RESTRICT');

Example:

 users want relation column id_mytable2_fk to table my_table2 on column id and CASCADE on update and delete, to create this relation that is :

foreign(‘id_mytable2_fk’),references(‘my_table2’,’id’),onDelete('CASCADE'),onUpdate('CASCADE');

 


Command Schema

Make it easy for users to use command generator schema on terminal, users have some optional command, that is :

 

1. Create table from schema to database

Run schema (module.exports.create) for create table to database, users can use command aurora db:run on terminal :

aurora db:run

To run schema to certain file schema user can use command -s or --schema:

aurora db:run -s name_file_schema
aurora db:run --schema name_file_schema

To run this command to custom configuration connection, users can add name file config in last position, example :

aurora db:run name_config
aurora db:run -s name_file_schema name_config
aurora db:run --schema name_file_schema name_config

 

2. Update table from schema to database

Run schema (module.exports.update) for updating table to database, users can use command aurora db:update on terminal :

aurora db:update

To run schema to certain file schema user can use command -s or --schema:

aurora db:update -s name_file_schema
aurora db:update --schema name_file_schema

To run this command to custom configuration connection, users can add name file config in last position, example :

aurora db:update name_config
aurora db:update -s name_file_schema name_config
aurora db:update --schema name_file_schema name_config

 

3. Delete table or column from schema to database

Run schema (module.exports.delete) for deleting table from database, users can use command aurora db:delete on terminal :

aurora db:delete

To run schema to certain file schema user can use command -s or --schema:

aurora db:delete -s name_file_schema
aurora db:delete --schema name_file_schema

To run this command to custom configuration connection, users can add name file config in last position, example :

aurora db:delete name_config
aurora db:delete -s name_file_schema name_config
aurora db:delete --schema name_file_schema name_config

 

4. Re-create table from schema to database

On re-create table to database, users can use command aurora db:refresh on terminal, how it works first run module.exports.delete, then module.exports.create, and module.exports.update :

aurora db:refresh

To run schema to certain file schema users can use command -s or    --schema :

aurora db:refresh -s name_file_schema
aurora db:refresh --schema name_file_schema

To run this command to custom configuration connection, users can add name file config in last position, example :

aurora db:refresh name_config
aurora db:refresh -s name_file_schema name_config
aurora db:refresh --schema name_file_schema name_config

 


Generator Schema

To make it easy, users can use create schema, Aurora.JS one special feature. Users can generate schema with field use terminal. Example :

1. Run Generator 

Run schema with generate users can give command on terminal, that is :

aurora schema:generate

2. Insert Table Name

First, users get a question :

? What table name do you want to make ? 

example, users insert “my_table”, and enter

3. Insert Columns

Next, users will get another question to insert on how many columns to make in the table:

? How many columns do you want to make ? 

And users insert total must be number to create a column on the table, 

example, user insert “2”.

4. Insert Column Name

First user insert name for column 1, and user get question :

? The name of column 1 that you want to make?  

Example user insert “my_column_one” and enter 

5. Insert Column Type

After insert column name, users will get another question to insert column type:

? The type of column 1 that you want to make ? (Use arrow keys or type to search)

And user scan search with input in terminal, for now column type only support MYSQL, that is:

Type

Description

increment(‘column_name’);

Auto Increment, Primary Key, and Integer Type

integer(‘column_name’,12);

INTEGER type column with optional length

smallInteger(‘column_name’);

SMALLINT type column

mediumInteger(‘column_name’);

MEDIUMINT type column

bigInteger(‘column_name’);

BIGINT type column

decimal(‘column_name’);

DECIMAL type column

float('column_name’);

FLOAT type column

double('column_name’);

DOUBLE type column

real('column_name’);

REAL type column

bit('column_name’,6);

BIT type column with optional length 

boolean('column_name’);

BOOLEAN type column

serial('column_name’);

SERIAL type column

date('column_name’,1);

DATE type column with optional length

dateTime('column_name’,1);

DATETIME type column with optional length

timeStamp('column_name’,1);

TIMESTAMP type column with optional length

time('column_name’,1);

TIME type column with optional length

year('column_name’);

YEAR type column

char('column_name’,10);

CHAR type column with optional length

varchar('column_name’,10);

VARCHAR type column with optional length

tinyText('column_name’);

TINYTEXT type column

text('column_name’);

TEXT type column

mediumText('column_name’);

MEDIUMTEXT type column

longText('column_name’);

LONGTEXT type column

binary('column_name’,10);

BINARY type column with optional length

varBinary('column_name’,10);

VARBINARY type column with optional length

tinyBlob('column_name’);

TINYBLOB type column

mediumBlob('column_name’);

MEDIUMBLOB type column

blob('column_name’);

BLOB type column

longBlob('column_name’);

LONGBLOB type column

enums('column_name’,” ‘one‘,’two’,’three’ ”);

ENUM type column

set('column_name’,” ‘one‘,’two’,’three’ ”);

SET type column

geometry('column_name’);

GEOMETRYtype column

point('column_name’);

POINT type column

lineString('column_name’);

LINESTRING type column

polygon('column_name’);

POLYGON type column

multiPoint('column_name’);

MULTIPOINT type column

multiLineString('column_name’);

MULTILINESTRING type column

multiPolygon('column_name’);

MULTIPOLYGON type column

geometryCollection('column_name’);

GEOMETRYCOLLECTION type column

Users can select any column type above and just press enter to select it

6. Insert column length

For column type enums and set, users can’t input number. Users must input default value.

example:  “a,b,c”  

Example for input length with number user get question :

? The length of column 1 that you want to make ?

And for input with value, users will get the question:

? The value of column 1 that you want to make ? (Example : a,b,c,d) 

For submit users need to enter

7. Insert column attribute

Last, users can add an attribute to a column that is:

Attribute

Description

No Attribute

Set column does not have attribute

primary

Set Column to primary key

unique

Set unique column

nullable

Allow null on column

And enter, for column type increment users can’t input attribute. If users input a total of two (2)  columns, users need to  input the name for column 2

7.1 Edit Column 

Next, users will get another question to confirm columns :

? Are the columns you want to make correct? (Y/n) 

If users input yes users will go to Create Relation table point 3, if no users will get another question for input number column to edit :

? What column number do you want to change? 

And users can input number for column to edit

8. Create Relation

Users can create relation, first users want to confirm for make relation :

Point 1:

? What column number do you want to change? 

If no user will go to the next step, if yes users will get question for insert local column for relation :

Point 2:

? Local column name for relation?

Example, users insert “id_my_table_two_fk”, then user must input table for relation :

Point 3:

? Table to relation?

After that users must insert references column for relation :

Point 4:

? References column name for relation?

Then users insert rules on update (CASCADE/RESTRICT) :

Point 5:

? On update? (Use arrow keys)

Then users insert rules on delete (CASCADE/RESTRICT) :

Point 6:

? On delete? (Use arrow keys)

Last, users confirm for relation to yes or no if no, user can edit relation

9. Run Create Rable To Database

First, confirm if users want to create table to database or not (yes/no) :

? Do you want to create this table for your database?

If yes user input, next user input configure connection, default is the main configuration

? Configure the connection you want to use? (if you want to use default configuration, directly enter) (main) 

And automatically table will be created to the database