B4J Tutorial Using MiniORMUtils in Web API Server 3

aeric · Nov 2, 2024

Introduction
At the time of writing, MiniORMUtils is version 1.14 and Web API Server 3 is version 3.00.
MiniORMUtils support SQLite and MySQL databases.
We don't need to write different SQL queries to work with SQLite and later migrate to MySQL.

How to Use MiniORMUtils

When we run Web API Server 3 for the first time, a new SQLite database is created by default.
The creation of database is achieved using CreateDatabase sub in Main module.
The project checks whether a database is already existed every time the server is started through CreateConnection sub.

Before we call this sub, the project needs to load configurations from config.ini file through InitDatabase sub.

B4X:

Private Sub InitDatabase
    cnn.Initialize
    cnn.DBDir = ctx.GetDefault("DbDir", "")
    cnn.DBFile = ctx.GetDefault("DbFile", "")
    cnn.DBType = ctx.GetDefault("DbType", "")
    ...
End Sub

No modification is needed on this sub. The correct way to modify the values is through editing the config.ini file.

Let's take a look at CreateConnection sub.

B4X:

Private Sub CreateConnection
    Try
        Log("Checking database...")
        #If MySQL
        Dim DBType As String = "MySQL"
        #Else
        Dim DBType As String = "SQLite"
        #End If
        If cnn.DBType.EqualsIgnoreCase(DBType) = False Then
            ShowBuildConfigurationNotMatch(cnn.DBType)
            Return
        End If
        DBConnector.Initialize(cnn)
        ...
End Sub

The selection of DBType is determined by Build Configurations.

The "Default" build configuration is selected means the project will utilize SQLite as database.
To use MySQL, select MySQL from the dropdown list.
If the selected DBType in Build Configuration does not match the configurations in config.ini, an error message will show and the server will be terminated.
If the configuration is matched, the project proceeds to initialize the DBConnector object.
B4X:
```
DBConnector.Initialize(cnn)
```

Depending on DBType, the project uses DBExist or DBExist2 sub for checking the database is existed.

B4X:

#If MySQL
Wait For (DBConnector.DBExist2) Complete (DBFound As Boolean)
#Else
Dim DBFound As Boolean = DBConnector.DBExist
#End If

If the return value for DBFound is True, a message log will be showed.

B4X:

If DBFound Then
    LogColor($"${cnn.DBType} database found!"$, COLOR_BLUE)
Else
    LogColor($"${cnn.DBType} database not found!"$, COLOR_RED)
    CreateDatabase
End If

DBFound = True

DBFound = False

Create Database

Depending on DBType, we call DBCreate for creating a new database.

B4X:

Private Sub CreateDatabase
    Log("Creating database...")
    Select cnn.DBType.ToUpperCase
        Case "MYSQL"
            Wait For (DBConnector.DBCreateMySQL) Complete (Success As Boolean)
        Case "SQLITE"
            Wait For (DBConnector.DBCreateSQLite) Complete (Success As Boolean)
    End Select

The database will be created. If it is failed then the sub will be exited.
To use MiniORMUtils, we must first initialize it.
We need to pass 2 parameters to the Initialize method.
B4X:
```
Dim MDB As MiniORM
MDB.Initialize(DBOpen, DBEngine)
```
If you want to add timestamps fields in your tables, we can set UseTimestamps to True.
B4X:
```
MDB.UseTimestamps = True
```
This will add 3 columns (created_date, modified_date and deleted_date) at the last columns to all of the following tables to be created.
If you don't need these columns, omit this line of code.
This setting can be set to True or False between creation of tables.
We will use a batch to execute the queries. Hence we will add every CREATE table query to a batch and execute them by batch.
This is set using the following code:
B4X:
```
MDB.AddAfterCreate = True
```
We also want to add some dummy data to the table.
We need to set the INSERT query as batch.
B4X:
```
MDB.AddAfterInsert = True
```

Create Table

We tell MiniORM the table name we want to create.
B4X:
```
MDB.Table = "tbl_categories"
```
We can add each column by passing an ORMColumn to the Columns list of the MiniORM.
B4X:
```
MDB.Columns.Add( <ORMColumn> )
```
We can use CreateORMColumn2 method to create ORMColumn by passing a Map.
B4X:
```
MDB.CreateORMColumn2( <Map> )
```
The Map must contains at least a keyvalue pair with the key "Name".
B4X:
```
CreateMap("Name": "category_name")
```
The complete code to add a table column for table tbl_categories with the name "category_name" is per the following:
B4X:
```
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "category_name")))
```
In SQLite, it will add a column of the type TEXT for category_name.
In MySQL, it will add a column of the type VARCHAR for category_name with length 255.
Then we call Create method to tell MiniORM to generate a CREATE query.

Insert Row

We can proceed to use MiniORM to add row to the table.
Since we have specified the table name before, we don't need to specify the table name again.
We just need to specify the column name as array to pass to the Columns property.
B4X:
```
MDB.Columns = Array("category_name")
```

Then we can add the value using the method Insert2.

B4X:

MDB.Insert2(Array("Hardwares"))
MDB.Insert2(Array("Toys"))

Execute Batch Query

Previously we have add CREATE and INSERT queries to the MiniORM object using methods such as Create and Insert2.

To execute all the queries, we use ExecuteBatch method.

B4X:

Wait For (MDB.ExecuteBatch) Complete (Success As Boolean)
If Success Then
    LogColor("Database is created successfully!", COLOR_BLUE)
Else
    LogColor("Database creation failed!", COLOR_RED)
End If

We will see a log message showing the execution success or failed.
Finally, we close the database connection using the Close method.
B4X:
```
MDB.Close
```
The Close method only applies to MySQL internally. SQLite has no effect but we left the code for compatibility.

This is the end of tutorial. Thanks for taking your time to read it.

Please do not post any question on this thread.
Always start a new question with the name Web API Server 3 or post your question here : [Q&A] Web Api Server 3

aeric · Nov 2, 2024

Using MiniORMUtils inside Server Handler

Referring to the generated code using Code Snippets in the Part #5: Add Boilerplate Code to Handler Using Code Snippets tutorial.

On top of the code, we see there are some variables declared as Private.

B4X:

Sub Class_Globals
        Private Request As ServletRequest
        Private Response As ServletResponse
        Private HRM As HttpResponseMessage
        Private DB As MiniORM
        Private Method As String
        Private Elements() As String
        Private ElementId As Int
End Sub

Request and Response are passing from Handle sub so we can use them through out the entire handler class.

B4X:

Sub Handle (req As ServletRequest, resp As ServletResponse)
    Request = req
    Response = resp
    ...
End Sub

We declared a variable HRM as HttpResponseMessage for generating the JSON output or response.
B4X:
```
Private HRM As HttpResponseMessage
```
HttpResponseMessage is a custom type defined in WebApiUtils library.
We declared DB as MiniORM for working with our database.
B4X:
```
Private DB As MiniORM
```
MiniORM is a class from MiniORMUtils library.
We declared a String array Elements() for storing the elements from the request URL.
B4X:
```
Private Elements() As String
```
Last variable is ElementId for storing the Id element.
B4X:
```
Private ElementId As Int
```

Initialization

We use the Initialize sub to initialize 2 variables, HRM and DB from the public variables we declared in Main module.

B4X:

Public Sub Initialize
    HRM.Initialize
    HRM.SimpleResponse = Main.Config.SimpleResponse
    DB.Initialize(Main.DBOpen, Main.DBEngine)
End Sub

Handle sub

The Handle sub look like following:

B4X:

Sub Handle (req As ServletRequest, resp As ServletResponse)
    Request = req
    Response = resp
    Method = Request.Method.ToUpperCase
    Dim FullElements() As String = WebApiUtils.GetUriElements(Request.RequestURI)
    Elements = WebApiUtils.CropElements(FullElements, 3) ' 3 For Api handler
    Select Method
        Case "GET"
            ...
        Case "POST"
            ...
        Case "PUT"
            ...
        Case "DELETE"
            ...
        Case Else
            Log("Unsupported method: " & Method)
            ReturnMethodNotAllow
            Return
    End Select
    ReturnBadRequest
End Sub

The Method variable read from the Request variable will be use for further routing.
We can use GetUriElements method of WebApiUtils to fill the FullElements array.
B4X:
```
Dim FullElements() As String = WebApiUtils.GetUriElements(Request.RequestURI)
```
Instead of counting the elements from 0 for the root element from the URL, what we are interested are the elements after the /api (for Api Handler).
Therefore we use CropElements method to "slice" or crop out the first elements.
B4X:
```
Elements = WebApiUtils.CropElements(FullElements, 3) ' 3 For Api handler
```
From this point, we can think that the first Element e.g Id is accessed using the index 0.
B4X:
```
ElementId = Elements(0)
```
You can see the logic inside ElementMatch sub.
Next, we will use Select-Case to control the flow of the request falls to the right sub.

Control Flow

Base on the Select-Case of variable Method, we can route to 2 API end points in this example.

The first end point is without Id where we pass an empty string pattern to the ElementMatch sub.
e.g URL: http://127.0.0.1:8080/api/users

B4X:

Select Method
    Case "GET"
        If ElementMatch("") Then
            GetUsers
            Return
        End If
        ...
    ...
End Select

If ElementMatch returns True then we can proceed to call GetUsers sub to return all the rows inside users table.
The second end point is with an Id where we pass the "id" pattern to the ElementMatch sub.
e.g URL: http://127.0.0.1:8080/api/users/2
B4X:
```
If ElementMatch("id") Then
    GetUserById(ElementId)
    Return
End If
```
Important: For every match, insert Return keyword before the End If keywords like the examples above.
Missing this keyword will cause the response Api returns the Bad Request output too. In other word, a malformed JSON will be returned.
The same steps apply to other Methods such as POST, PUT and DELETE depending on the Endpoint pattern you want to create.
We also check if there are request by other Method (e.g PATCH) and we don't want to allow such request to proceed.
B4X:
```
Case Else
    Log("Unsupported method: " & Method)
    ReturnMethodNotAllow
    Return
```
This is also the case where you want to create an Api that only allow POST then the remaining request can be consider under the Case Else flow.
Any route that does not match the desired pattern will fall under Bad Request and handled by ReturnBadRequest sub.

Commonly Use Subs

We can shorten some of the long codes by using extra subs.

This is an example:

B4X:

Private Sub ReturnApiResponse
    WebApiUtils.ReturnHttpResponse(HRM, Response)
End Sub

So instead of writing the full code, we can just use one word in our code.
You can see examples of using ReturnBadRequest, ReturnMethodNotAllow and ReturnErrorUnprocessableEntity.

aeric · Nov 3, 2024

Examples

GetUsers

B4X:

Private Sub GetUsers
    ' #Desc = Read all Users
    DB.Table = "tbl_users"
    DB.Query
    HRM.ResponseCode = 200
    HRM.ResponseData = DB.Results
    ReturnApiResponse
    DB.Close
End Sub

This sub is called when the request URL matched an empty pattern with GET method.
It is use to get all the rows in users table.
Important: In order for HelpHandler to read this handler to generate the API Documentation, the name of the sub must be started with "Get".
The first line is a comment. We use #Desc to tell HelpHandler class that the text after the equals sign is the description of the API that will appear in the documentation.
Similar to how we create a table, we need to pass the table name using the Table property when we want to query rows from a table.
B4X:
```
DB.Table = "tbl_users"
```
Next, we just need to call Query method.
B4X:
```
DB.Query
```
We want to return a status code 200 to the response.
B4X:
```
HRM.ResponseCode = 200
```
Since we are expecting the rows are returned as a JSON array or list, we pass the query result DB.Results (which is a list) to HRM.ResponseData.
B4X:
```
HRM.ResponseData = DB.Results
```
Then we call ReturnApiResponse to write the JSON output to the server response.
Finally we close the MiniORM object which will close the SQL resultset object especially for MySQL database.
B4X:
```
DB.Close
```

GetUserById

B4X:

Private Sub GetUserById (Id As Int)
    ' #Desc = Read one User by id
    ' #Elements = [":id"]
    DB.Table = "tbl_users"
    DB.Find(Id)
    If DB.Found Then
        HRM.ResponseCode = 200
        HRM.ResponseObject = DB.First
    Else
        HRM.ResponseCode = 404
        HRM.ResponseError = "User not found"
    End If
    ReturnApiResponse
    DB.Close
End Sub

This sub is called when the request URL matched an id pattern with GET method.
It is use to get a single row in users table if the id is existed.
Adding #Elements comment and parameter with type (e.g Id As Int) for the sub dictates how the documentation will appear.
Similar to the GetUsers sub, we pass the table name to MiniORM.
B4X:
```
DB.Table = "tbl_users"
```
To query for a single row from users table by id, we can use the Find method.
B4X:
```
DB.Find(Id)
```

If a row is found then we return the row as a single object.

B4X:

If DB.Found Then
    HRM.ResponseCode = 200
    HRM.ResponseObject = DB.First

We use the First method to ensure that only one object is read.
WebApiUtils through ReturnHttpResponse method will determine which format to return as JSON output based on the SimpleResponse settings.
It means the return value can be an array or an object.
If the id is not existed, we want to return a message to the API.
We assign status code 404 to indicate the item is not found.
B4X:
```
Else
    HRM.ResponseCode = 404
    HRM.ResponseError = "User not found"
```
Finally, we write the JSON output to server response and close the MiniORM object.
B4X:
```
ReturnApiResponse
DB.Close
```

PostUser

B4X:

Private Sub PostUser
    ' #Desc = Add a new User
    ' #Body = {<br>&nbsp;"name": "User_name"<br>}
    Dim data As Map = WebApiUtils.RequestData(Request)
    If Not(data.IsInitialized) Then
        HRM.ResponseCode = 400
        HRM.ResponseError = "Invalid json object"
        ReturnApiResponse
        Return
    End If

    ' Check whether required keys are provided
    Dim RequiredKeys As List = Array As String("key1", "key2", "key3")
    For Each requiredkey As String In RequiredKeys
        If data.ContainsKey(requiredkey) = False Then
            HRM.ResponseCode = 400
            HRM.ResponseError = $"Key '${requiredkey}' not found"$
            ReturnApiResponse
            Return
        End If
    Next
   
    ' Check conflict User name
    DB.Table = "tbl_users"
    DB.Where = Array("User_name = ?")
    DB.Parameters = Array As String(data.Get("user_name"))
    DB.Query
    If DB.Found Then
        HRM.ResponseCode = 409
        HRM.ResponseError = "User already exist"
        ReturnApiResponse
        DB.Close
        Return
    End If
   
    ' Insert new row
    DB.Reset
    DB.Columns = Array("User_name", "created_date")
    DB.Parameters = Array(data.Get("User_name"), data.GetDefault("created_date", WebApiUtils.CurrentDateTime))
    DB.Save
   
    ' Retrieve new row
    HRM.ResponseCode = 201
    HRM.ResponseObject = DB.First
    HRM.ResponseMessage = "User created successfully"
    ReturnApiResponse
    DB.Close
End Sub

This sub is called when the request URL matched the empty pattern with POST method.
It is use to create a new resource in this case the users table.
Meaning it will execute an INSERT query to the database using MiniORM.
Notice that we use a comment ' #Body to display the sample body. (Similar for PUT end point)

Take note that we only can write the comment in a single line.
For POST request, we need to read the data submitted in the POST body.
B4X:
```
Dim data As Map = WebApiUtils.RequestData(Request)
```
If the submitted payload is a valid JSON string, it can be parse using RequestData method of WebApiUtils and assign the result into a Map.
B4X:
```
If Not(data.IsInitialized) Then
    HRM.ResponseCode = 400
    HRM.ResponseError = "Invalid json object"
    ReturnApiResponse
    Return
End If
```
If the string is invalid, we return a result with status code 400 and exit the process.

We also want to check if all the required keys are provided.

B4X:

' Check whether required keys are provided
Dim RequiredKeys As List = Array As String("key1", "key2", "key3")
For Each requiredkey As String In RequiredKeys
    If data.ContainsKey(requiredkey) = False Then
        HRM.ResponseCode = 400
        HRM.ResponseError = $"Key '${requiredkey}' not found"$
        ReturnApiResponse
        Return
    End If
Next

For e.g we can replace key1 with user_name, key2 with email and key3 with password.

Then we check for existing records to avoid duplicate.

B4X:

' Check conflict User name
DB.Table = "tbl_users"
DB.Where = Array("User_name = ?")
DB.Parameters = Array As String(data.Get("User_name"))
DB.Query
If DB.Found Then
    HRM.ResponseCode = 409
    HRM.ResponseError = "User already exist"
    ReturnApiResponse
    DB.Close
    Return
End If

In the case of users, it is more useful if we check for unique emails. We can replace the above code to check for emails.
B4X:
```
' Check conflict emails
DB.Table = "tbl_users"
'DB.Where = Array("user_email = ?")
'DB.Parameters = Array As String(data.Get("user_email"))
DB.WhereValue(Array("user_email = ?"), Array(data.Get("user_email")))
DB.Query
```
There is a new shortcut method WhereValue in MiniORMUtils to replace method Where and Parameters.
Next, we can insert the data.
Notice we use method Reset to ensure any conditions or parameters are cleared before we reuse the MiniORM object.
B4X:
```
' Insert new row
DB.Reset
```
We specify the column names in order inside an array and assign to Columns property of MiniORM.
B4X:
```
DB.Columns = Array("user_name", "user_email", "user_password", "created_date")
```

Next, we specify the values associated with the columns into Parameters property as an array of objects.

B4X:

DB.Parameters = Array(data.Get("user_name"), data.Get("user_email"), data.Get("user_password"), data.GetDefault("created_date", WebApiUtils.CurrentDateTime))

Then we call the method Save. This will create an INSERT command and MiniORM will execute it.
B4X:
```
DB.Save
```
If the command executed successfully, we return a result with status code 201 to indicate a new resource is created.
We pass the first object from MiniORM which is queried based on the newly created id.
We can return the object by assigning it to ResponseObject.
We can also assign a message using the ResponseMessage property.
Finally, we write the JSON output to server response and close the MiniORM object.
B4X:
```
ReturnApiResponse
DB.Close
```
Take note of the key names inside the data map. For e.g use the lowercase data.Get("user_name") instead of data.Get("User_name").

aeric · Nov 4, 2024

Adding New Table

Often time we need to add a new table after the database is created.
To do it, we need to check whether a table is exist then we create the table.
For e.g we want to add a new table tbl_users.

TableExists sub

Let's create a sub for checking the table in Main module.
We will pass the table name into this sub.

The sub will return True if the table exist or otherwise False.

B4X:

Private Sub TableExists (TableName As String) As Boolean
    Dim MDB As MiniORM
    MDB.Initialize(DBOpen, DBEngine)
    #If MySQL
    Return MDB.TableExists2(TableName, ctx.Get("DbName"))
    #Else
    Return MDB.TableExists(TableName)
    #End If
End Sub

Create Table

We will add the code for creating table inside CreateConnection sub.

B4X:

If DBFound Then
    LogColor($"${cnn.DBType} database found!"$, COLOR_BLUE)
    ' Add code for checking table exist
    ...
Else
    LogColor($"${cnn.DBType} database not found!"$, COLOR_RED)
    CreateDatabase
End If

We use the sub we have created previously to check for the table name.
B4X:
```
If TableExists("tbl_users") = False Then
```

Then we can use MiniORM to create the table.

B4X:

Dim MDB As MiniORM
MDB.Initialize(DBOpen, DBEngine)
MDB.UseTimestamps = True
MDB.Table = "tbl_users"
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_name")))
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_email")))
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_password")))
MDB.Create
MDB.Execute
LogColor($"Table tbl_users is created!"$, COLOR_BLUE)

After calling the Create method, we call Execute to execute the SQL command.
The new table will be created when we run the project again.

The complete code is as following:

B4X:

Private Sub CreateConnection
    Try
        LogColor("Checking database...", COLOR_BLUE)
        #If MySQL
        Dim DBType As String = "MySQL"
        #Else
        Dim DBType As String = "SQLite"
        #End If
        If cnn.DBType.EqualsIgnoreCase(DBType) = False Then
            ShowBuildConfigurationNotMatch(cnn.DBType)
            Return
        End If
        DBConnector.Initialize(cnn)
        #If MySQL
        Wait For (DBConnector.DBExist2) Complete (DBFound As Boolean)
        #Else
        Dim DBFound As Boolean = DBConnector.DBExist
        #End If
        If DBFound Then
            LogColor($"${cnn.DBType} database found!"$, COLOR_BLUE)
            ' Check table exist
            If TableExists("tbl_users") = False Then
                Dim MDB As MiniORM
                MDB.Initialize(DBOpen, DBEngine)
                MDB.UseTimestamps = True
                MDB.Table = "tbl_users"
                MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_name")))
                MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_email")))
                MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_password")))
                MDB.Create
                MDB.Execute
                LogColor($"Table tbl_users is created!"$, COLOR_BLUE)
            End If
        Else
            LogColor($"${cnn.DBType} database not found!"$, COLOR_RED)
            CreateDatabase
        End If
    Catch
        LogError(LastException.Message)
        LogColor("Error checking database!", COLOR_RED)
        Log("Application is terminated.")
        ExitApplication
    End Try
End Sub

This is the end of tutorial. Thanks for taking your time to read it.

B4J Tutorial Using MiniORMUtils in Web API Server 3

aeric

Expert

aeric

Expert

aeric

Expert

Similar Threads

B4J Tutorial Using MiniORMUtils in Web API Server 3

aeric

Expert

aeric

Expert

aeric

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency