Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Database to go! The perfect database for developer

When building a new project, we don't need a big database that scales and has lots of data, but we do still need some kind of data source. Of course, it is possible to fake it and have some hardcoded value returned by an API but that takes time to create and it's not a database. In this post, I want to share a solution to have a portable, self-healing, disposable, disconnected database that doesn't require any installation.

The solution? Put the database in a container! It doesn't matter what database you are planning to use or on which OS you are developing. Most databases will have an official image available on Docker Hub and Docker runs on all platforms. If you feel uncomfortable with containers, have no fear, this post is beginner-friendly.

This post is part of a series where I share my experience while building a Dungeon crawler game. The code can be found on GitHub.


The Plan

Have a database ready at the "press of a button". By "ready", I mean up and running, with data in it, and accessible to all developer tools.

Preparation for the Database

We need a script to create the database schema and some data. There are many ways to achieve this. A beginner-friendly way is to create an empty database and use a tool like Azure Data Studio to help create the SQL scripts. Doing it this way will validate that the script works.

The Docker command to create the database's container will change a little depending on the database you are using but here what's a MySQL one look like:

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD='rootPassword' -p 3306:3306 -d mysql 

Where some-mysql is the name you want to assign to your container, rootPassword is the password to be set for the MySQL root user and -d means that the container will run detached. The -p option is used to map the port 3306 of the container to the port 3306 of the host. This is required to be able to connect to the database from the host.

output of the docker run command


Now, a MySQL server is running inside the container. To connect to the server with Azure Data Studio use the extension MySQL extension for Azure Data Studio. Microsoft has a QuickStart: Use Azure Data Studio to connect and query MySQL if needed. Create a new connection in Azure Data Studio, then create a database (ex: 2d6db).

Create a new connection in Azure Data Studio

You can use the MySQL command-line tool if you prefer, but Azure Data Studio offers a lot of help when you are not that familiar with SQL. You can even use the Copilot extension and ask it to write the SQL statement for you. It's pretty good!

If you want to learn more about this, check the Open at Microsoft episode: Copilot is now in Azure Data Studio and this is how it can help you! to see it in action.

It's fantastic to generate a first draft of the create statements and to make queries.

Copilot writing SQL

Let's create two SQL scripts. The first one will be to create the schema with all the tables. The idea here is to write the script and execute it to validate the results. Here is an example creating only one table to keep the post simple.

-- schema.sql

CREATE TABLE IF NOT EXISTS 2d6db.rooms (
  id int NOT NULL AUTO_INCREMENT,
  roll int DEFAULT 0,
  level int DEFAULT 1,
  size varchar(10) DEFAULT NULL,
  room_type varchar(255) DEFAULT NULL,
  description varchar(255) DEFAULT NULL,
  encounter varchar(255) DEFAULT NULL,
  exits varchar(255) DEFAULT NULL,
  is_unique bool DEFAULT false,
  PRIMARY KEY (id)
);

Now that there are tables in the database, let's fill them with seed data. For this, the second SQL script will contain insert statement to populate the tables. We don't need all the data but only what will be useful when developing. Think about creating data to cover all types or scenarios, it's a development database so it should contain data to help you code.

-- data.sql

INSERT INTO 2d6db.rooms(roll, level, room_type, size, description, exits, is_unique)
VALUES (2,1,'Empty space', 'small','There is nothing in this small space', 'Archways',false);

INSERT INTO 2d6db.rooms(roll, level, room_type, size, description, exits, is_unique)
VALUES (3,1,'Strange Text', 'small','This narrow room connects the corridors and has no furniture. On the wall though...', 'Archways',false);

INSERT INTO 2d6db.rooms(roll, level, room_type, size, description, exits, is_unique)
VALUES (4,1,'Grakada Mural', 'small','There is a large mural of Grakada here. Her old faces smiles...', 'Archways',true);

Note: You can now stop the database's container with the command: docker stop some-mysql. We don't need it anymore.

Putting All the Pieces Together

This is when the magic starts to show up. Using a Docker Compose file, we will start the database container and execute the two SQL scripts to create and populate the database.

# docker-compose.yml

services:
  
  2d6server:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_DATABASE: '2d6db'
      MYSQL_ROOT_PASSWORD: rootPassword
    ports:
       - "3306:3306"
    volumes:
      - "../database/scripts/schema.sql:/docker-entrypoint-initdb.d/1.sql"
      - "../database/scripts/data.sql:/docker-entrypoint-initdb.d/2.sql"

The docker-compose.yml file are in YAML and usually are used to start multiple containers at once, but it doesn't need to. In this scenario, the file defines a single container named 2d6server using just like the previous Docker command and MySQL image and configuration. The last command volumes is new. It maps the path where the SQL scripts are located to /docker-entrypoint-initdb.d inside the container. When MySQL starts it will execute the files in that specific folder in alphabetic order. This is why the scripts are renamed 1.sql and 2.sql, as the table must be created first.

Do get the database up and ready, we will execute the docker compose up.


# start the database
docker compose -f /path_to_your_file/docker-compose.yml up -d 

# stop the database
docker compose -f /path_to_your_file/docker-compose.yml down -d 

By default, the command looks for a docker-compose.yml file. If you have a different name use the argument -f to specify the filename. Optionally, to free the prompt you can pass the argument -d to be in detached mode.

Docker Compose commands

When you are done coding and you don't need the database anymore, execute the docker compose down command to free up your computer. Compared to when the server is installed locally, a container will leave no trace; your computer is not "polluted".

When you need to update the database, edit the SQL script first. When the scripts are ready, execute the docker-compose restart to get the database refreshed.

To Conclude

Now, you only need to execute one simple command get a fresh database, when you want. All the developers don't need to have a database server installed and configured locally. And you don't need to be worried when deleting or modifying data, like when using a shared database. After cloning the repository all developers will have everything they need to start coding.

In a next post, I will share how I used Azure Data API Builder to generate a complete API on top of the database using the same docker compose method.

Video version!

If you prefer watching instead of reading here the video version of this post!

Reading Notes #518


Good Monday, Already time to share new reading notes. Here is a list of all the articles, blog posts, and podcast episodes that catch my interest during the week.

If you think you may have interesting content, share it!

Cloud

Programming

Podcast

  • Measuring DevSecOps with Victoria Almazova (.NET Rocks!) - DevSecOps, what's that? Security, yes, but to what limit, so many great questions are discussed in this nice episode.

  • Photographing Home with Mylo Fowler (Wild Ideas Worth Living) - This post is a great example that there are so many completely different journeys to reach your destination. Follow yours and enjoy it as much as you can.


~frank

Reading Notes #379


Cloud

Programming

~

Reading Notes #319

kUz2asfCloud


Programming


Databases


Miscellaneous



Reading Notes #292

cloudheight


Suggestion of the week


Cloud


Programming


Databases


Miscellaneous



From a Docker container to MySQL as a Service in Azure in 5 minutes

Hello MySQL! It's been a while eh? You were at version 3 something, I was just getting stated with my professional career. We had fun for years... Then you know things changed, and I did something else. I was really happy when Microsoft announced, at the MSBuild,  the availability of MySQL as a Servive in Azure.
 
SearchMySQL

Creating a MySQL database with the portal is extremely simple. As usual, you enter the server name, database name and the Admin's password. At the time I'm writing this post, it was not possible to use any CLI, but I'm sure it will be available shortly. For the ones who are not used at Database as Service in Azure, one thing you will need to do to get access to your database from your computer is white listed your IP. It's very easy to do from the Azure Portal, just select the Connection Security tab on the left menu and add your address. Oh! And don't forger to click the save button. ;)

Firewall

During my tests, I've tried different applications (WordPress, Azure WebApp, custom on-premise app.) that use MySQL as backend database, I didn't notice any problem, and performance were great. It was just... simpler; no server to configure, no VM to configure, no update. The only "issue" I got was trying to connect Power BI Desktop to a MySQL, but I think it more related to the drivers since the service was still in early preview. I notified Microsoft, and I'm sure it will be available shortly.

Since it's been a while since I did some reel work with MySQL I didn't have any client install on my laptop. In fact, I had no idea which one I should take.

I knew we can run some CLI inside a Docker container with an interactive interface. So I decided to give it a try. A quick docker search mysql shows me that an image existed. Here are the steps to get setup.

First, let's download the image, and create an instance named mySQLTools of MySQL 8.0:

docker run --name mySQLTools --env "MYSQL_ROOT_PASSWORD=Passw0rd" -d mysql:8

Then using the -it let's bring the bash prompt to our terminal.

docker exec -it mySQLTools bash -l 

Finally we connect to our client using the usual settings (note that you must have no space between -p and your password):

mysql -h _ServerName.database.windows.net_  -u _UserName@ServerName_ -p_MyPassword_ _DatabaseName_

result

Voila! That's all what it takes to get started. And by the way, it will also work great with a Azure SQL Database.


docker pull shellmaster/sql-cli 

docker run -it --rm --name=sqlTools shellmaster/sql-cli mssql -s ServerName.database.windows.net  -u UserName@ServerName  -p YourPassword -d DatabaseName -e





Reading Notes #246

IMG_20160826_115405Cloud


Programming


Databases


Miscellaneous


Reading Notes #188


I was really happy to ear about Microsoft Azure Data Center that will be built in Canada. And it was definitely not only good thing that was published this week...

Suggestion of the week


Cloud


Programming


Database


Miscellaneous