Medhat Elmasry

Sunday, February 20, 2022

Node.js express, SQLite, CSV and Google Charts

In this article, I will develop a Node.js Express application that imports data from a CSV file into a SQLite database and render a pie chart using Google Charts. The purpose of this post is to familiarize the reader with Node Express, importing a CSV file into SQLite and, subsequently, rendering a pie chart with the data.

SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers. Even though it is not customary to use SQLite with MongoDB apps, it is worth exploring as this database engine is self-contained and does not need a database server.

Source code: https://github.com/medhatelmasry/xpress-sqlite-chart

The following is assumed about your environment:

Node.js & NPM are installed on your computer
You have installed nodemon
You already have some familiarity with Node.js

Initializing our node express app

Let us first create a skeleton template of a simple Node.js Express application. This application will focus of APIs, so there will be no need to use any view engines.

In a working directory, create a subdirectory named xpress-sqlite-chart for our new Node.js application with:

mkdir xpress-sqlite-chart
cd xpress-sqlite-chart

Let us initialize a new node app with:

npm init --yes

This creates a package.json that looks like this:

{
"name": "xpress-sqlite-chart",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC"
}

Edit the package.json as follows:

1) Change the value for main: from index.js to server.js:

2) add another script >> "start": "node server.js"

The resulting package.json will look like this, with changes highlighted:

{
"name": "xpress-sqlite-chart",
"version": "1.0.0",
"description": "",
"main": "server.js",
"scripts": {
"start": "node server.js",
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC"
}

We need to add some packages with the following npm command:

npm install express csvtojson sqlite3

The three above packages are self-explanatory for those familiar with Node.js.

Add folders config, data, database, public, and routes as shown below:

Sample data

Inside the data folder, create a text file named students.csv with the following content:

FirstName,LastName,School

Ann,Fay,Mining
Joe,Sun,Nursing
Ben,Ray,Mining
Zoe,Cox,Business
Sam,Ray,Mining
Dan,Ash,Medicine
Pat,Lee,Computing
Kim,Day,Nursing
Tim,Rex,Computing
Rob,Ram,Business
Jan,Fry,Mining
Jim,Tex,Nursing
Ben,Kid,Business
Mia,Chu,Medicine
Ted,Tao,Computing
Amy,Day,Business
Ian,Roy,Nursing
Liz,Kit,Nursing
Mat,Tan,Medicine
Deb,Roy,Medicine
Ana,Ray,Medicine
Lyn,Poe,Computing
Amy,Raj,Nursing
Kim,Ash,Mining
Bec,Kid,Nursing
Eva,Fry,Computing
Eli,Lap,Business
Sam,Yim,Nursing
Joe,Hui,Mining
Liz,Jin,Nursing
Ric,Kuo,Medicine
Pam,Mak,Computing
Stu,Day,Business
Tom,Gad,Medicine
Bob,Bee,Computing
Jim,Ots,Computing
Tom,Mag,Medicine
Hal,Doe,Mining
Roy,Kim,Medicine
Vis,Cox,Nursing
Kay,Aga,Nursing
Reo,Hui,Nursing
Bob,Roe,Mining

Building a basic Node.js Express application

In the config folder, add a file named index.js with the following content:

module.exports = {
'database_name': 'school.db',
// Setting port for server
'port': process.env.PORT || 3000
};

The above file serves as a configuration file that specifies the SQLite database name and the port number that will be used. As regards the port number, it takes the PORT environment variable, otherwise it defaults to 3000.

Add two files to the routes folder named index.js and students.js respectively.

The content of routes/index.js is as follows:

const express = require("express");
const router = express.Router();

router.get("/", (req, res, next) => {
res.json({"message":"Ok"})
});

module.exports = router;

The above will display a JSON object {"message":"Ok"} when the use points his browser to endpoint /.

The content of routes/student.js is as follows:

const express = require("express");
const router = express.Router();

router.get("/students", (req, res, next) => {
res.json([
{"id":1,"FirstName":"Ann","LastName":"Fay","School":"Mining"},
{"id":2,"FirstName":"Ben","LastName":"Ray","School":"Nursing"}
])
});

module.exports = router;

The above code sets up an endpoint that displays a JSON array of some fixed sample data of students.

In the root folder of your application, create a file named server.js and add to it the following code:

const express = require("express")
const app = express()
const path = require('path');
const config = require('./config');

var bodyParser = require("body-parser");
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());

var index = require("./routes/");
var students = require("./routes/students");

app.use(express.static(path.join(__dirname, 'public')));

app.use("/", index);
app.use("/api", students);

// Start server
app.listen(config.port, () => {
console.log(`Server running on port ${config.port}`);
});

What does the above code do?

The first four lines load express, path and config objects.
Next we load bodyParser so that we can extract json objects from the body of the response object
objects index & students that point to /routes/index.js and /routes/students.js respectively.
app.use(express.static(path.join(__dirname, 'public'))); sets a folder named public that will contain static files like *.css, *.js, *.html, images files, etc...
We then set / to go to object index and route /api to go to object students
Finally, we start our web server which listens on the port numbers specified in file config/index.js.

Start the node app by issuing the following command in a terminal window at the root of your application:

nodemon

If you point your browser http://localhost:3000, you will see the following:

Pointing your browser to http://localhost:3000/api/students will reveal the following:

We now have a skeleton web app that we can build out towards our final product.

Importing CSV data into SQLite

We will use the csvtojson package to import CSV data that resides in the data/students.csv file into SQLite.

Add a file named index.js to the database folder. Then, add the following code to database/index.js:

const sqlite3 = require('sqlite3').verbose()
const path = require('path');
const csv2json = require('csvtojson');
const config = require('../config');

const CSV_FILE = path.join(__dirname, "../data/students.csv")

let db = new sqlite3.Database(config.database_name, (err) => {
if (err) {
// Cannot open database
console.error(err.message)
throw err
} else {
console.log('Connected to the SQLite database.')
db.run(`CREATE TABLE students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
FirstName VARCHAR(80),
LastName VARCHAR(80),
School VARCHAR(50)
)`,
(err) => {
if (err) {
// Table already created
} else {
csv2json().fromFile(CSV_FILE)
.then(data => {
var insert = 'INSERT INTO students (FirstName, LastName, School) VALUES (?,?,?)';
for(const item of data) {
db.run(insert, [item.FirstName,item.LastName,item.School]);
}
}).catch(err => {
// log error if any
console.log(err);
});
// Table just created, creating some rows
}
});
}
});

module.exports = db

What does the above code do?

Packages for sqlite3 and csv2json are loaded to use SQLite and conversion from CSV to JSON libraries
We also load the path library so that we can use the path.join() function
The configuration file in config/index.js is referenced enabling us to access the database name
The location of our CSV file is defined in variable CSV_FILE
the command 'new sqlite3.Database()' creates a database file named school.db, if it does not already exist
Database table named students is created if it does not already exist
Function csv2json().fromFile() loads contents of the CSV file into a variable named data. We then iterate through the data collection to execute a SQL INSERT statement for each item in the collection

Reading data from students table in school.db SQLite database

In our routes/students.js API file, let's read data from our students database table. Add the following to the top of the routes/students.js file:

const db = require("../database");

The above allows us to access the db database object defined in database/index.js.

Instead of hardcoding JSON data in the "/students" GET route, we can read data from the database, Therefore, replace the the router.get("/students .... code in router/students.js with the following:

router.get("/students", (req, res, next) => {

var sql = "select * from students"
var params = []
db.all(sql, params, (err, rows) => {
if (err) {
res.status(400).json({"error":err.message});
return;
}
res.json({
"message":"success",
"data":rows
})
});
});

We execute the SQL statement "SELECT * FROM students" to read contents of students table and return the data as an array of JSON objects.

Testing our API

Let's test our application. When you point your browser to http://localhost:3000/api/students, you will see a list of all the students in the database:

Also note the creation of a SQLite database file named school.db in the root of your web app.

Creating a chart of students by school

We will use the freely available Google Charts library to render a pie-chart illustrating number of students in every school. The starting point is to create a aggregation of the data. This will be done by adding the following additional /chart route to routes/students.js just under the existing /students route:

router.get("/chart", (req, res, next) => {
sql = "SELECT School as school, COUNT(*) as count";
sql += " FROM students GROUP BY School";

var params = []
db.all(sql, params, (err, rows) => {
if (err) {
res.status(400).json({"error":err.message});
return;
}
res.json(rows)
});
});

Point your browser to http://localhost:3000/api/chart and you will see a list of aggregate data as follows:

We can render this aggregate data into a pie chart. In the public folder, create a simple HTML file named chart.html with the following content:

<!DOCTYPE html>
<html lang="en">

<head>
<title>Pie chart of students by school</title>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
</head>

<body>
<div id="chart_div"></div>
<script type="text/javascript">
google.charts.load('current', {
packages: ['corechart', 'bar']
});
google.charts.setOnLoadCallback(LoadData);
function LoadData() {
$.ajax({
url: '/api/chart',
dataType: "json",
type: "GET",
error: function (xhr, status, error) {
toastr.error(xhr.responseText);
},
success: function (data) {
PopulationChart(data);
return false;
}
});
return false;
}
function PopulationChart(data) {
var dataArray = [
['school', 'count']
];
$.each(data, function (i, item) {
dataArray.push([item.school, item.count]);
});
var data = google.visualization.arrayToDataTable(dataArray);
var options = {
title: 'Students by School',
width: 700,
height: 800
};
var divElement = document.getElementById('chart_div');
var chart = new google.visualization.PieChart(divElement);
chart.draw(data, options);
return false;
}
</script>
</body>

</html>

Note that we are making a ajax get call to endpoint /api/chart in the $.ajax() jQuery call. Also, note that new google.visualization.PieChart() generates a pie chart. With Google Charts, you can generate a multitude of chart types including bar, geo, column, area, etc...

When you point your browser to http://localhost:3000/chart.html you will see the following pie-chart.

No surprise, 27.9% of students are doing nursing during this age of covid.

CORS

In public/chart.html, change the URL in "url: '/api/chart', to "http://localhost:3000/api/chart" so that we can run the chart.html page outside of the current web server.

From within the file system of desktop computer, double click on file public/chart.html. The page loads in a browser but appears blank. Visit the browser developer tools console tab. You will see an error that resembles the following:

This suggests that we have a CORS issue. What is CORS?

Cross-origin resource sharing (CORS) is a browser mechanism which enables controlled access to resources located outside of a given domain.

We need to enable CORS in our server app. This is done by adding the following code to server.js just before app.use("/", index); around line 15:

// enable CORS
app.use(function(req, res, next) {
res.setHeader("Access-Control-Allow-Origin", "*");
res.setHeader('Access-Control-Allow-Methods', 'GET, POST, OPTIONS, PUT, PATCH, DELETE');
res.setHeader("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
next();
});

Now try double-clicking on public/chart.html (or refreshing the page) and you should see that it is working fine.

What have we learned in this tutorial?

initializing a node.js Express web app
building a basic Node.js Express web app
import a CSV file into SQLite database
work with SQLite in a Node.js application
creating a simple pie chart from aggregate data with Google Charts
enabling CORS in a Node.js Express application

Thanks for coming this far in this article. In my next article I will show you how you can take this app and turn it into a Docker image that can be pushed to Docker-Hub and, subsequently, hosted on Microsoft Azure.

Wednesday, February 9, 2022

PHP, SQLite, CSV and CanvasJS

In this article, I will import data from a CSV file into SQLite and render a chart using Canvas.JS. The purpose of this post is to familiarize the reader with importing a CSV file into a database and, subsequently, rendering a pie chart with the data.

Source Code: https://github.com/medhatelmasry/php_chart

The following is assumed:

You have PHP installed on your computer
You have the "extension=sqlite3" setting enabled in your php.ini.

Directory Structure

Inside of a working directory named php_chart, create the following directory structure:

The home page

Inside the root folder, add an index.html file with the following content representing a simple menu system:

<p><a href="./create" >Create school database and students table in SQLite.</a></p>

<p><a href="./import/" >Import seed data from csv file.</a></p>

<p><a href="./chart/" >Display chart with Canvas.JS</a></p>

Sample data

Inside the data folder, create a text file named seed-data.csv with data from this link.

The database file

In the root of your application, create a PHP file named include_db.php containing only one statement, representing the name of the database file:

<?php
$db = new SQLite3($_SERVER['DOCUMENT_ROOT'] . '/school.db');
?>

The above code creates a school.db SQLite database file if it does not already exist.

Creating database file and add Students table

Add an index.php file inside the create folder with the following content:

<?php include("../include_db.php"); ?>

<?php
echo "<hr /><h3>Create Student Table</h3>";
#===============================================
# Create table
#===============================================

$SQL_create_table = "CREATE TABLE IF NOT EXISTS Students (
StudentId VARCHAR(10) NOT NULL,
FirstName VARCHAR(80),
LastName VARCHAR(80),
School VARCHAR(50),
PRIMARY KEY (StudentId)
);";

echo "<p>$SQL_create_table</p>";

$db->exec($SQL_create_table);

$db->close();
?>

<hr /><a href="/" ><< BACK</a>

What does the above code do?

We first include the include_db.php file so that we have a handle to the db object representing our school.db database file.
Next, we create a Students table in the database by executing a "Create Table ..." SQL statement. The columns in the table match the items in our CSV file.
We close the connection to the database.
There is a link at the bottom that returns us to the home page.

Import CSV file into Students table

Add an index.php file inside the import folder with the following content:

<?php include('../include_db.php'); ?>

<?php

$count = $db->querySingle("SELECT count(*) from Students");

// if empty, insert sample data

if ($count == 0) {

$row = 1;

if (($handle = fopen("../data/seed-data.csv", "r")) !== FALSE) {

$data = fgetcsv($handle, 1000, ",", "\"", "\\");

while (($data = fgetcsv($handle, 1000, ",", "\"", "\\")) !== FALSE) {

$num = count($data);

echo "<p> $num fields in line $row: <br /></p>\n";

$row++;

$id = SQLite3::escapeString($data[0]);

$firstName = SQLite3::escapeString($data[1]);

$lastName = SQLite3::escapeString($data[2]);

$school = SQLite3::escapeString($data[3]);

$SQLinsert = "INSERT INTO Students (StudentId, FirstName, LastName, School)";

$SQLinsert .= " VALUES ";

$SQLinsert .= " ('$id', '$firstName', '$lastName', '$school')";

$db->exec($SQLinsert);

$changes = $db->changes();

echo "<p>The INSERT statement added $changes rows</p>";

}

$db->close();

What does the above code do?

We check whether or not there is any data in the Students table.
We load data from the CSV file only if the Students table is empty
The PHP fgetcsv() function is used to load CSV data into an array named $data
Every row of data in the CSV file is inserted into the Students table in the database
We close the connection to the database.

List imported students data

Add an index.php file inside the list folder with the following content:

<?php include("../include_db.php"); ?>

<table border="1">
<?php

echo "<hr /><h3>List of students</h3>";

$res = $db->query('SELECT * FROM Students');

while ($row = $res->fetchArray()) {
echo "<tr>\n";
echo "<td>{$row['StudentId']}</td>";
echo "<td>{$row['FirstName']}</td>";
echo "<td>{$row['LastName']}</td>";
echo "<td>{$row['School']}</td>";
echo "<tr>\n";
}

?>
</table>
<hr /><a href="/" ><< BACK</a>

What does the above code do?

The above code simply lists the contents of the Students in an html table.

Render a pie chart of "student count by school"

Add an index.php file inside the chart folder with the following content:

<?php include('../include_db.php'); ?>

<?php
$dataPoints = [];

$sql = "SELECT School as school, COUNT(*) as count";
$sql .= " FROM Students GROUP BY School";
$res = $db->query($sql);

while ($row = $res->fetchArray()) {
$arrayItem = array("label" => $row['school'], "y" => $row['count']);
array_push($dataPoints, $arrayItem);
}

$db->close();
?>

<script>
window.onload = function() {
var chart = new CanvasJS.Chart("chartContainer", {
animationEnabled: true,
title: {text: "Students by school"},
data: [{
type: "pie",
yValueFormatString: "#,##0.00\"\"",
indexLabel: "{label} ({y})",
dataPoints: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>
}]
});
chart.render();
}
</script>
<div id="chartContainer" style="height: 370px; width: 100%;"></div>
<script src="https://canvasjs.com/assets/script/canvasjs.min.js"></script>

<hr /><a href="/" ><< BACK</a>

What does the above code do?

A "SELECT ... GROUP BY ..." SQL statement is executed that generates a result-set containing count of students by school.
A two-dimensional array is created with key "label" containing school and key "y" containing count
The bottom part of the above code used the CanvasJS JavaScript library
The type property is set to the type of chart you wish to generate. In this case it is pie.
The dataPoints property contains our data from the $dataPoints two-dimensional array converted into JSON objects

Testing our app

We should be good to go. Let us test our very basic PHP application. We first need to start our server. This is accomplished by running the following command in a terminal window in the root of our application:

php -S localhost:8888

The above starts the PHP development server and listens on port 8888.

Point your browser to http://localhost:8888. You should see the following:

Click on the first "Create school database and students table in SQLite." link to create the database and Students table. This displays the following output with the INSERT statement.

If you look into the root folder of your app, you will see that a school.db file was created.

Click on the "BACK" link to return to the home page.

Next click on the "Import seed data from csv file." link to import CSV data into the Students database table. This displays a series of INSERT statements as shown below:

Return to the home page and click on the third "List data" link. You will see that data was imported into the database:

Return to the home page and click on the last "Display chart with Canvas.JS" link to see our pie chart.

I trust that this simple tutorial will help you visualize data with PHP.

Saturday, February 5, 2022

ASP.NET 6.0 Web API and Mongo DB

In this tutorial I will show you how to develop an ASP.NET 6.0 API application that interacts with MongoDB. In order to proceed you will need the following pre-requisites:

Docker - used to host MongoDB. This is my preferred approach because it is quick and does not take too many resources on the host computer.
.NET 6.0
Visual Studio Code

Source code: https://github.com/medhatelmasry/AspMongoApi

Companion Video: https://youtu.be/pDIqBRvy3g8

The Database

Run the following command in a terminal window to start a MongoDB container named mdb:

docker run -p 27777:27017 --name mgo -d mongo:4.1.6

You can verify that the container is running by typing the following command in a terminal window:

docker ps -a

Let us start a bash session inside the container so that we can create a database and add some sample data to it. Enter the following command to start an interactive bash session inside the container:

docker exec -it mgo bash

We can then use the mongo command line interface (CLI) to create a database and then add some sample data to a collection of students. Type the following command:

mongo

This takes you into a mongo session command prompt. Enter the following command to create a database named school-db:

use school-db

Next, let's create a collection named students and add to it six sample students:

db.Students.insertMany(
[
{'FirstName':'Sue','LastName':'Fox','School':'Business'},
{'FirstName':'Tom','LastName':'Max','School':'Mining'},
{'FirstName':'Ann','LastName':'Lee','School':'Nursing'},
{'FirstName':'Joe','LastName':'Roy','School':'Tourism'},
{'FirstName':'Jan','LastName':'Ash','School':'Communications'},
{'FirstName':'Eva','LastName':'Day','School':'Medicine'},
]);

You can retrieve the list of students with the following command:

db.Students.find({}).pretty();

To exit the mongo command prompt by typing:

exit

You can also exit the bash session and return to the host operating system by typing:

exit

Creating an ASP.NET 6.0 Web API application

Create an ASP.NET 6.0 Web API application. This is accomplished by entering this command:

dotnet new webapi -f net6.0 -o AspMongoApi

Let us see what our application looks like. Run the application by executing:

cd AspMongoApi
dotnet run

The above command builds and runs the application in a web server. Point your browser to https://localhost:????/weatherforecast (Where ???? is your port number). This is what you should see:

Stop the web server by hitting CTRL+C on the keyboard.

Building the Students API

Find the latest version of MongoDB driver from https://www.nuget.org/packages/MongoDB.Driver/. At the time of writing, the latest version was 2.14.1. Add the MongoDB driver Nuget package with the following command:

dotnet add package MongoDB.Driver --version 2.14.1

Now that we have the driver, we can proceed with coding our API. I will use Visual Studio Code because it is operating system agnostic. To load your application workspace into VS Code, simply type in the following command from the root folder of your application:

code .

We need a Student class to represent the schema for student documents in the Students collection in the MongoDB school-db database. Create a Models folder. Inside the Models folder, add a file named Student.cs with the following code:

using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;

namespace AspMongoApi.Models;
public class Student {
[BsonId]
[BsonRepresentation(BsonType.ObjectId)]
public string? Id { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }

[BsonElement("School")]
public string? Department { get; set; }
}

In the preceding class, the Id property:

Is required for mapping the Common Language Runtime (CLR) object to the MongoDB collection.
Is annotated with [BsonId] to designate this property as the document's primary key.
Is annotated with [BsonRepresentation(BsonType.ObjectId)] to allow passing the parameter as type string instead of an ObjectId structure. Mongo handles the conversion from string to ObjectId.

The Department property is annotated with the [BsonElement] attribute. The attribute's value of School represents the property name in the MongoDB collection.

Add the following to appsettings.json:

"StudentDbSettings": {
"CollectionName": "Students",
"ConnectionString": "mongodb://localhost:27777",
"DatabaseName": "school-db"
},

The entire contents of appsettings.json will look like this:

{
"StudentDbSettings": {
"CollectionName": "Students",
"ConnectionString": "mongodb://localhost:27777",
"DatabaseName": "school-db"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*"
}

Add StudentDbSettings.cs to the Models folder with this code:

namespace AspMongoApi.Models;

public class StudentsDbSettings {
public string ConnectionString { get; set; } = null!;
public string DatabaseName { get; set; } = null!;
public string CollectionName { get; set; } = null!;
}

The above StudentDbSettings class is used to store the appsettings.json file's StudentDbSettings property values. The JSON and C# property names are named identically to simplify the mapping process.

Add the following code to Program.cs before "var app = builder.Build();" :

builder.Services.Configure<StudentsDbSettings>(
builder.Configuration.GetSection("StudentDbSettings"));

In the above code, the configuration instance to which the appsettings.json file's StudentDatabaseSettings section binds is registered in the Dependency Injection (DI) container.

Create a folder named Services and add to it a file named StudentService.cs with the following code:

namespace AspMongoApi.Services;

public class StudentsService {
private readonly IMongoCollection<Student> _studentsCollection;

public StudentsService(IOptions<StudentsDbSettings> studentsDatabaseSettings) {
var mongoClient = new MongoClient(
studentsDatabaseSettings.Value.ConnectionString);

var mongoDatabase = mongoClient.GetDatabase(
studentsDatabaseSettings.Value.DatabaseName);

_studentsCollection = mongoDatabase.GetCollection<Student>(
studentsDatabaseSettings.Value.CollectionName);
}

public async Task<List<Student>> GetAsync() =>
await _studentsCollection.Find(_ => true).ToListAsync();

public async Task<Student?> GetAsync(string id) =>
await _studentsCollection.Find(x => x.Id == id).FirstOrDefaultAsync();

public async Task CreateAsync(Student newStudent) =>
await _studentsCollection.InsertOneAsync(newStudent);

public async Task UpdateAsync(string id, Student updatedStudent) =>
await _studentsCollection.ReplaceOneAsync(x => x.Id == id, updatedStudent);

public async Task RemoveAsync(string id) =>
await _studentsCollection.DeleteOneAsync(x => x.Id == id);
}

In the above code, an StudentsDbSettings instance is retrieved from DI via constructor injection. Also, the above class knows how to connect to the MongoDB server and use the available driver methods to retrieve, insert, update and delete data.

To register the StudentService class with DI to support constructor injection, add the following to Program.cs before "var app = builder.Build();" :

builder.Services.AddSingleton<StudentsService>();

Add a StudentsController.cs class to the Controllers folder with the following code:

namespace AspMongoApi.Controllers;

[ApiController]
[Route("api/[controller]")]
public class StudentsController : ControllerBase {
private readonly StudentsService _studentsService;

public StudentsController(StudentsService studentsService) =>
_studentsService = studentsService;

[HttpGet]
public async Task<List<Student>> Get() =>
await _studentsService.GetAsync();

[HttpGet("{id:length(24)}")]
public async Task<ActionResult<Student>> Get(string id) {
var student = await _studentsService.GetAsync(id);

if (student is null) {
return NotFound();
}

return student;
}

[HttpPost]
public async Task<IActionResult> Post(Student newStudent) {
await _studentsService.CreateAsync(newStudent);

return CreatedAtAction(nameof(Get), new { id = newStudent.Id }, newStudent);
}

[HttpPut("{id:length(24)}")]
public async Task<IActionResult> Update(string id, Student updatedStudent) {
var student = await _studentsService.GetAsync(id);

if (student is null) {
return NotFound();
}

updatedStudent.Id = student.Id;

await _studentsService.UpdateAsync(id, updatedStudent);

return NoContent();
}

[HttpDelete("{id:length(24)}")]
public async Task<IActionResult> Delete(string id) {
var student = await _studentsService.GetAsync(id);

if (student is null) {
return NotFound();
}

await _studentsService.RemoveAsync(student.Id!);

return NoContent();
}
}

The StudentsController class:

Uses the StudentService class to perform CRUD operations.
Contains action methods to support GET, POST, PUT, and DELETE HTTP requests.

Running the application

Start the application by executing the this command from a terminal windows at the root of the application:

dotnet run

You can view the data by pointing your browser to https://localhost:????/api/Students. This is the expected output:

Note that even though the last field is named School in the database, it appears as Department in our app because that is how we mapped it in the Student class.

Go ahead and test the other API endpoints for POST, PUT and DELETE using your favourite tool like Postman or curl. It should all work.

Cleanup

To stop & remove the MongoDB docker container, enter the following from any terminal window on your computer:

docker rm -f mgo

I hope you learned something new in this tutorial. Until next time, happy coding.

Reference:https://referbruv.com/blog/posts/integrating-mongodb-with-aspnet-core-(net-6)

docker-compose with MySQL pomelo driver and ASP.NET 7.0

It is customary to develop ASP.NET with either SQL Server or SQLite databases. How about if you want to use the popular MySQL database server? This article discussed one approach to making this possible by having your ASP.NET 7.0 development environment connect with MySQL running in a docker container.

Source code: https://github.com/medhatelmasry/AspMySQL-docker-compose

This article assumes the following:

You have .NET 7.0 installed on your computer.
You have Docker Desktop installed on your computer.
You have the dotnet-ef tool installed.

Let's get started.

Setting up the mariadb:10.7.3 container

To download the MySQL version 8.0.0 image from Docker Hub and run it on your local computer, type the following command from within a terminal window:

docker run -p 3333:3306 --name db -e MYSQL_ROOT_PASSWORD=secret -d mariadb:10.7.3

This starts a container named db that listens on port 3333 on your local computer. The root password is secret.

To ensure that the MySQL container is running, type the following from within a terminal window:

docker ps

You will see a message like the following:

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

67335fb804e4 mariadb:10.7.3 "docker-entrypoint.s…" 5 seconds ago Up 3 seconds 0.0.0.0:3333->3306/tcp

Creating our ASP.NET 7.0 MVC App

The first step is to create a working directory somewhere on your computer's hard drive. I did so by creating a folder named AspMySQL with the following terminal command:

mkdir AspMySQL

Thereafter, go into the new folder with:

cd AspMySQL

We will create an ASP.NET 7.0 application that uses individual authentication with the following command:

dotnet new mvc -f net7.0 --auth individual

To run the web application and see what it looks like, enter the following command:

dotnet run

You will see a message in the terminal window that resembles the following:

info: Microsoft.Hosting.Lifetime[14]
Now listening on: https://localhost:7042
info: Microsoft.Hosting.Lifetime[14]
Now listening on: http://localhost:5035
info: Microsoft.Hosting.Lifetime[0]
Application started. Press Ctrl+C to shut down.
info: Microsoft.Hosting.Lifetime[0]
Hosting environment: Development
info: Microsoft.Hosting.Lifetime[0]
Content root path: /Users/medhatelmasry/AspMySQL/

The above message indicates that the Kestrel web server is running and listening on port 7042. The port number you get is probably different. Start your browser with the appropriate URL. You should see a page that looks like this:

Look into the root folder of the project, you will find a file named app.db. This is an SQLite database file. The web application that we scaffolded is configured to work with SQLite. We will change it so that it works with the popular MySQL database instead. Go ahead and delete app.db.

Close your browser and stop the web server in the terminal window by hitting CTRL + C.

There is only one NuGet package that is needed to talk to MySQL. Add the package by typing the following command from within a terminal window:

dotnet add package Pomelo.EntityFrameworkCore.MySql -v 7.0.0

Let us configure our web application to use MySQL instead of SQLite. Open the Program.cs file in your favourite editor and comment out (or delete) the following statements found at around line 8:

~~var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");~~

~~builder.Services.AddDbContext<ApplicationDbContext>(options =>~~

~~options.UseSqlite(connectionString));~~

Replace the above code with the following:

var host = builder.Configuration["DBHOST"] ?? "localhost";
var port = builder.Configuration["DBPORT"] ?? "3333";
var password = builder.Configuration["DBPASSWORD"] ?? "secret";
var db = builder.Configuration["DBNAME"] ?? "test-db";
var user = builder.Configuration["DBUSER"] ?? "root";

string connectionString = $"server={host}; userid={user}; pwd={password};"
+ $"port={port}; database={db};SslMode=none;allowpublickeyretrieval=True;";

var serverVersion = new MySqlServerVersion(new Version(10,7,3));

builder.Services.AddDbContext<ApplicationDbContext>(options =>
options.UseMySql(connectionString, serverVersion));

Five environment variables are used in the database connection string. These are: DBHOST, DBPORT , DBPASSWORD, DBNAME and DBUSER. If these environment variables are not found then they will take up default values: localhost, 3333, secret, test-db and test-dbroot respectively.

Entity Framework Migrations

The migration files that were created in the /Data/Migrations folder contain commands for creating SQLite artifacts. These are not valid in our situation because we will be using MySQL and not SQLite. Therefore, delete the Migrations folder under /Data and create new migrations with the following command:

dotnet-ef migrations add M1 -o Data/Migrations

We can instruct our application to automatically process any outstanding Entity Framework migrations. This is done by adding the following statement to Program.cs right before the last app.Run() statement:

using (var scope = app.Services.CreateScope()) {
var services = scope.ServiceProvider;

var context = services.GetRequiredService<ApplicationDbContext>();
context.Database.Migrate();
}

Test our app

Now, let us test our web app and see whether or not it is able to talk to the containerized MySQL database server.

Run the web application with the following terminal command:

dotnet run

If all goes well, you will see a message that indicates that the web server is listening on some random port number. Point your browser to http://localhost:???? (where ???? is your port number). The same web page will appear as before. Click on the Register link on the top right side.

I entered an Email, Password and Confirm password, then clicked on the Register button. The website then displays the following page that requires that you confirm the email address:

Click on the “Click here to confirm your account” link. This leads you to a confirmation page:

The message on the top right side confirms that the user was saved and that communication between the ASP.NET MVC app and MySQL is working as expected.

Dockeri-zing solution

We will generate the release version of the application by executing the following command from a terminal window in the root directory of your web app:

dotnet publish -o dist

The above command instructs the dotnet utility to produce the release version of the application in the dist directory.

If you inspect the dist directory, you will see content similar to the following:

The highlighted file in the above image is the main DLL file that is the entry point into the web application.

Let us run the release version of the web app. To do this, change to the dist directory with the following terminal instruction:

cd dist

You can then run your main DLL file. In my case, this file is AspMySQL.dll. I executed the following command:

dotnet AspMySQL.dll

This displays the familiar messages from the web server that the app is ready to be accessed from a browser. Hit CTRL C to stop the web server.

We now have a good idea about what ASP.NET 7.0 artifacts need to be copied into a container. We shall simply copy contents of the dist directory into a Docker image that has the .NET 7.0 runtime.

Stop the web server by hitting CTRL C in the terminal window.

Also, in a terminal window, stop and remove the MySQL container with:

docker rm -f db

Return to the root directory of your project by typing the following in a terminal window:

cd ..

We need to create a docker image that will contain the .NET 7.0 runtime. A suitable image for this purpose is: mcr.microsoft.com/dotnet/aspnet:7.0

Create a text file named Dockerfile and add to it the following content:

FROM mcr.microsoft.com/dotnet/aspnet:7.0
COPY dist /app
WORKDIR /app

ENV ASPNETCORE_URLS http://+:80

EXPOSE 80/tcp
ENTRYPOINT ["dotnet", "AspMySQL.dll"]

Above are instructions to create a Docker image that will contain our ASP.NET application. I describe each line below:

FROM mcr.microsoft.com/dotnet/aspnet:7.0	Base image mcr.microsoft.com/dotnet/aspnet:7.0 will be used
COPY dist /app	Contents of the dist directory on the host computer will be copied to directory /app in the container
WORKDIR /app	The working directory in the container is /app
EXPOSE 80/tcp	Port 80 will be exposed in the container
ENTRYPOINT ["dotnet", "AspMySQL.dll"]	The main ASP.NET web app will be launched by executing "dotnet AspMySQL.dll"

We will next compose a docker yml file that orchestrates the entire system which involves two containers: a MySQL database server container and a container that holds our application. In the root folder of your application, create a text file named docker-compose.yml and add to it the following content:

version: '3.8'

volumes:
datafiles:

services:
db:
image: mariadb:10.7.3

volumes:
- datafiles:/var/lib/mysql
#restart: always
environment:
MYSQL_ROOT_PASSWORD: secret
MYSQL_TCP_PORT: 3306

webapp:
build:
context: .
depends_on:
- db
ports:
- "8888:80"
#restart: always
environment:
- DBHOST=db
- DBPORT=3306
- DBPASSWORD=secret
- DBNAME=bingo-db
- ASPNETCORE_ENVIRONMENT=Development

Below is an explanation of what this file does.

We will be having two containers. Each container is considered a service. The first service is named db and will host MySQL. The second service is named webapp and will host our ASP.NET web app.

The most current version of docker-compose is version 3.8. This is the first line in our docker-compose file.

The MySQL Container

Image mariadb:10.7.3 will be used for the MySQL container.

A volume named datafiles is declared that will host MySQL data outside of the container. This ensures that even if the MySQL container is decommissioned, data will not be lost.

restart: always is so that if the container stops, it will be automatically restarted.

The root password will be secret when MySQL is configured. This is set by the MYSQL_ROOT_PASSWORD environment variable.

The ASP.NET Web Application Container

The container will be built using the instructions in the Dockerfile file and the context used is the current directory.

depends_on indicates that the web app relies on the MySQL container (db) to properly function.

Port 80 in the mvc container is mapped to port 8888 on the host computer.

The environment variables needed by the web app are:

DBHOST	points to the MySQL service
DBPORT	Should be set to 3306 because it is the depault port # that MySQL listens on
DBPASSWORD	this is the root password for MySQL
DBNAME	We shall call the database for our web app bingo
ASPNETCORE_ENVIRONMENT	set to Development more. In reality, you should change this to Production one you determine that your web app container works as expected.

Running the yml file

To find out if this all works, go to a terminal window and run the following command:

docker-compose up

Point your browser to http://localhost:8888/ and you should see the main web page.

NOTE: If you cannot view the home page, check that the web container is running. If it is stopped then start it with docker start …

To ensure that the database works properly, register a user by clicking on the Register link in the top right corner.

You will then receive a “Register Confirmation”:

Click on the “Click here to confirm your account” so that the app accepts the email address that was used. On the “Confirm Email” page.

As you can see in the top-right corner, the user with email a@a.a has been successfully registered.

This opens a whole new world for containerizing your ASP.NET web apps.

Cleanup

Hit CTRL C in the terminal window to stop docker-compose, then run the following command:

docker-compose down