Blog

Details
Category: Databases

MySql Workbench is a great tool for Database Administration and visualization. You can easily view all the Tables, Views, Stored Procedures, etc. available in a Schema and also, view the Data each table is handling and run queries. Workbench can be connected to any DB Server creating a SSH Tunnel. For local development this are the steps to follow if you are using a Vagrant Virtual Machine.

Prerequisites:

 

 Step 1. Turn on your vagrant virtual machine:

~/devzone/cool_vagrant_project$ vagrant up

Step 2. Get from the Vagrantfile the ip you  set for private network:

  # Use private network
  config.vm.network "private_network", ip: "192.168.56.10"

Step 3. Open MySql Workbench and create new Connection.

  • Enter that ip in the SSH Hostname followed by the :22 port
  • Use vagrant as SSH Username
  • Leave SSH Password
  • Provide the absolute path to:
/home/.../.../cool_vagrant_project/.vagrant/machines/default/virtualbox/private_key

 

Step 4. Click on Test Connection Button. If ok, then close the dialog and your new connection is ready to be used

 

From now on, you just need to click on that connection button to open a query editor:

 

Close the session closing the tab.

 

 

Details
Category: Databases

MySql Workbench is a great tool not only for managing a Database but for Modeling Schemas. With the modeler you can very easily generate all the code required for creating Tables, Indexes and Table Associations.

A Schema is basically a collection of Tables, Views, Indexes, Users allowed to access, Triggers, Stored Procedures, etc. It is common to confuse a Database with a Schema and more when you only have one schema in your Database.

Prerequisites:

  • Have downloaded and installed MySql workbench
  • Have a MySql / MariaDB server installed and running.For local development you may refer to:  Configure MariaDB in a Vagrant Virtual Machine
  • Have a connection available in Workbech to the Db Server running. For local development you may refer to: Connect MySql Workbench to a running instance in a Vagrant Machine

 

Step 1. Create a new Schema model.

Click on the model tab then the plus sign:

 

And a new schema page is opened:

 

Step 2. Right click on mydb tab and select edit.

Change the name of the schema and also choose character set. Recommended is utf8mb4 and general_ci for collation:

 

Step 3. Double click on the Add Diagram icon at the top.

A new model diagram page is opened:

 

 

 Step 4. Click on the table icon then click on the page.

Once the new table is placed, double click on it to edit its properties:

 

Give a name to this new table and then click on the Columns Tab. Then start adding all the columns you need. For each column you may want to choose data type, if nullable, etc.

Important: don't forget to save your model. just Click on File > Save Model as...

Step 5. If the next table is similar to the first one.. just copy - paste it to duplicate it. Then double click on it and change the name and update columns.

 

If you need to define table associations, just click on the type you want and then click on one table and then on the other:

 

 

Step 6. Once you have placed all Tables, Indexes, Associations, etc. then click on the Database menu option and select Forward Engineer.

A dialog opens up showing the details of the connection:

 

click on Next choosing the required options for each step. most of the time accept the defaults. once you reach the review Sql Script, you may want to save it for sharing or later use:

 

 Click on next and Workbench will take care of applying this sql script to the new created schema. Close the dialog window and close the diagram tab. You will see that the tables are already in your schema:

 

 The visual modeler is a great tool and it allows you to get a better picture of how the data is persisted.

 

 

 

Details
Category: Distributed Systems within an Organization

Service Oriented Architecture is a Software Design Pattern that allows you to Distribute a System into smaller more manageable Applications. In this pattern the big monolithic system is split into domain based units called Services.

Each Service is independent from the other, and they communicate between each other by some sort of REST Api calls.

The common part here is that all of them share the same Database, where each one has its own schema. This way, each service can scale differently in terms of application layer, while maintaining only one Database Server.

There are many variants to this pattern, one is shown in the figure above, where the users can make requests to each one of these Services. If the called Service requires anything from other, it can make a call to it.

This pattern is recommended for decomposing a big Monolithic System, and for the sake of development costs, i recommend to use the same Stack for all Services, in my case I work with Nginx as Web Server and PHP as Backend Language.

Its also, very simple to add a common Nginx Ingress Server to act as Load Balancer and prevent possible malicious attacks, filtering only allowed incoming requests from whitelisted IPs, or special headers, etc. and I'd recommend for the long run to consider to put these Services in Docker containers and use Kubernetes as Container Orchestrator.

With Kubernetes is fairly simple to implement and maintain a pool of Services for the whole CI/CD Pipelines. Just imagine that this monolith got broken into 5 Services, now for each one, you will need a local environment where the developers will be working, then a Dev environment where the work of each team will be joined and unit, integration and functional testing will be executed, then a QA Environment where Q&A team will be testing each single use case to make sure everything continues working fine, then a Stage Environment that has  the same nmber of replicas with the exact same data  as in Production, where a round of key testing is done, and then Production Environment.

So if we consider one container for each one of these Services, and assuming you have no more than 3 replicas per service in Stage and in Production, plus the Sql proxy ones to connect to the DB, plus backup services, log and monitoring, etc. most likely for these 5 Services, you will end having a Kubernetes cluster with around 100 deployments.

A typical Kubernetes cluster for a 5 Service workload, with this configuration, will require you to consider a virtual environment capable of providing 10 to 30 virtual cores and 20 to 40 GB Ram divided into at least 5 to 10 nodes.

A typical Kubernetes Engine for a workload as mentioned above with a Cloud SQL DB up to 100 GB no HA in Google Cloud may be in the range of US $500 ~ 1,000 per month.

The benefits of using a cloud provider like Google Cloud for a Kubernetes Cluster, is that you can be up and running in no time and you have tech support available all the time. 

 Next Up: Microservices Architecture

 

 

Details
Category: Symfony Framework

When you are using Symfony PHP Framework, the most common way to interact with Databases is using Doctrine. And if you are using a relational Database like Oracle, MSSql Server, MySql or MariaDb, you will need to handle Table Associations. One to Many means "One of these can have many of those"

You may refer to this posts:

 

In the Article How to Implement Many to Many Relation in Symfony with Doctrine in easy steps I explained how to easily make the code to handle the Many to Many relation between Animals and Food Entitites.

Now we want to place Many Animals in One Zoo. For doing that we need a One to Many relation.