DEV Community: Arctype Team

What is the secure_file_priv Variable in MySQL?

Arctype Team — Tue, 23 Aug 2022 21:22:55 +0000

If you have ever dived deeper into the settings letting you unleash the full power of MySQL, you might already come across a couple of settings letting you perform a variety of different tasks – one of such settings is the secure_file_priv variable.

What is the secure_file_priv Variable and How Does it Work?

The secure_file_priv variable is heavily associated with bulk importing of data inside of your InnoDB-based database instances. Remember how we said that LOAD DATA INFILE is significantly faster than INSERT statements due to the fact that it comes with many so-called “bells and whistles” unique to itself? Part of that magic is load_data_infile. The variable usually resides in your my.cnf file (which itself is located in your /var/bin/mysql directory) and looks something like the following (the following example refers to the variable being used in Windows environments):

secure_file_priv=”d:/wamp64/tmp”

This variable, simply put, puts a restraint on the directories that can be used to load data into your MySQL database instance. In other words, if you run a LOAD DATA INFILE query and the file you load into your InnoDB-based database instance does not reside in this directory, MySQL will come back with an error like so:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

To overcome this error, you either need to remote the --secure-file-priv setting from your my.cnf file, or load your data from a directory specified in the value of the variable. Once you do so, your data should be loaded in without any issues!

Other Concerns

Aside from loading data into your MySQL database instances using LOAD DATA INFILE and making use of this privilege, the value of such a variable might also be relevant when exporting data out of MySQL using SELECT … INTO OUTFILE. this variable can also be used to disable import and export operations using LOAD DATA INFILE and SELECT ... INTO OUTFILE: simply set this variable to NULL in such a case. In general though, you can also run a query like SHOW VARIABLES LIKE ‘secure_file_priv’ or SELECT @@secure_file_priv in order to observe the name of the directory that this variable is set to. Here is what Arctype would come up with:

As you can see, the directory is indeed set to ”d:/wamp64/tmp” as in the example we gave you above.

Summary

The secure_file_priv variable denotes the directory from which data files can be loaded into a given database instance or to which directory data can be written when exporting data when using LOAD DATA INFILE or SELECT ... INTO OUTFILE. MySQL allows you to observe the value of this parameter at all times by running either SELECT @@secure_file_priv queries or queries like SHOW QUERY VARIABLES LIKE ‘secure_file_priv’. The value of this parameter is worth keeping an eye on at all times: even if you think that you would not ever need to load bigger data sets into a given database instance or export such data sets from it.

How to Use SQL to Analyze And Visualize Data?

Arctype Team — Thu, 18 Aug 2022 21:39:25 +0000

The goal of every business is to perform efficiently, maximize profit and make strategically-guided decisions. In the current business world, the competition is high and every business needs every edge and advantage they can get to improve their odds of success.

Companies that want to stay in business and thrive in the current business world must employ data analysis. Data analysis plays a role in making decisions more scientific and helping businesses operate more effectively.

What is Data Analysis?

Data analysis can be described as the process of cleaning, transforming, and processing raw data to extract actionable and relevant information that helps businesses make informed decisions. The process of data analysis provides useful insights and statistics that can be presented in tables, charts, and graphs.

In this article, you will learn how to get useful information from a collection of data by running SQL queries. Also, you will learn how to present that information using graphs and charts. I will be using a dataset of different stores of a supermarket company, PostgreSQL, and the Arctype SQL client.

What is PostgreSQL and Arctype?

PostgreSQL, also known as Postgres, is a free and open-source relational database management system. Arctype is a fast and easy-to-use database management tool for writing SQL queries, building dashboards, and sharing data with your team.

Setting Up PostgreSQL

To use Postgres in this tutorial, navigate to the Postgres download page and download it for your operating system. It is available for Linux, macOS, and Windows.

After installing Postgres on your machine, run the command below to confirm the installation.

psql --version

You should be able to see the version of Postgres installed into your system. Now run the command shown below to open the Postgres prompt.

sudo -u postgres psql postgres

Once Postgres has opened on the command line, run the command below to set a password for postgres.

\password postgres

To quit the Postgres prompt, run the command shown below.

\q

Let us now create a database called “supermarket” in PostgreSQL that will be used to store data from the supermarket stores CSV file. We run the command below.

CREATE DATABASE supermarket;

Then run the command shown below to see if the database you created is on the list of databases in Postgres.

\l

As you can see from the screenshot below, the supermarket database was created in PostgreSQL.

The Arctype SQL Client

To use Arctype, navigate to the Arctype homepage and download the Arctype app on your machine. The application is available on Windows, MacOS, and Linux.

Once the app has been installed, open it and you will be prompted to create an account as shown below.

Integrating the Arctype SQL Client With Postgres

Once you have created an account, you will be prompted to select your database. In this case, select Postgres.

The next step is to add Postgres credentials to connect Arctype to your Postgres database.

Fill in the fields according to the example shown below.

Name: Supermarkets Dataset
Host: 127.0.0.1
Port: 5432
User: postgres
Password: Postgres password
Database: supermarket

Note: Use the credentials you created when creating a database in the Postgres prompt.

Once you have entered the Postgres credentials, click the “Test Connection” button at the bottom to make sure credentials are correct. If the credentials are correct, click the “save” button to the right of the “Test Connection” button to complete Postgres and Arctype SQL client integration as shown below.

Importing CSV Files Into Postgres Using Arctype

We will be importing the supermarkets stores dataset CSV file that you can download by navigating to this Kaggle web page as shown below.

To import the CSV file into Postgres using Arctype, navigate to the “Tables” view. Click on the three dots next to the table search field. Then select “Import CSV to Table” as shown below.

Next use the file selection window that appears to select the CSV file you downloaded. Select your file and click the “Open” button as shown below.

Once you have selected and opened the CSV file, Arctype will display the first ten rows of the CSV file data for preview. If everything looks correct, click the “Accept” button as shown below.

After clicking the “Accept” button, you will be redirected to a configuration window as shown below. The window allows you to determine where you want the data to be stored and to select the schema.

If everything looks good, click the “Import CSV” button to start importing your CSV file. Open the table data was successfully imported,everything should look as shown below.

Running SQL Queries With Arctype

SQL queries allow you to display or modify data from databases. In this section, I will show you some queries you can run to create tables, charts or graphs to visualize the supermarkets data.

Let us assume you are the owner of the supermarkets. You would want to know interesting stats such as the top 10 performing stores by sales. To get such insights, you can run a SQL query that creates a table of top ten performing stores. Then you can visualize the data by creating a graph or a chart.

To run a SQL query using Arctype, click the “Create query” button on the top right of the Arctype window. A screen will then appear where you can run a query as shown below.

Give your query a descriptive name like “Top 10 Stores By Sales.” Then write the following SQL query to determine the top 10 stores by sales from the stores data.

SELECT store_area, store_sales AS Sales FROM stores GROUP BY store_id, store_sales ORDER BY store_sales DESC LIMIT 10;

The query selects data in the column store_area and store_sales from the stores table where store_sales is renamed to Sales. The data then gets grouped by the columns named store_id and store_sales (Sales.) After that, the data is ordered by store_sales where sales are arranged from highest to lowest. The query limits the results to the top 10 selling store areas.

If everything goes well, the query should create a table with two columns named store_area and sales as shown below.

Creating Charts Using Arctype

Let us now create a chart from the table created by the SQL query. To create a chart using Arctype, select the “Chart” option at the bottom right of the SQL editor as shown below.

On the right side of the screen, you can select the type of chart you want. In this case, we select “Bar Chart.” Then below the “Select Chart Type” option, you have the “Specify Columns For The Chart” option. In this section, drag the store_area column to the X-axis column and the sales column to the Y-axis column. Arctype should help you come up with a chart similar to the one shown below.

Conclusion

In this article, you have learned how to create a database in PostgreSQL, import a CSV file into the DBMS and how to run SQL queries to analyse data. Now, I suggest you try to create different SQL queries to come up with some more exciting insights and use Arctype to create charts.

CHAR vs. VARCHAR: What’s the Difference?

Arctype Team — Tue, 16 Aug 2022 22:33:50 +0000

If you’ve found yourself in the MySQL space even for a little while, you already know that MySQL and other flavors of it offer a couple of data types for developers to choose from. A wide majority of the most popular data types offered by MySQL are related to text – in this space, MySQL offers VARCHAR, CHAR, TINYTEXT, TEXT, and other options. In this blog, we are going to go through the similarities and differences between two of some of the most popular options: the aforementioned VARCHAR and CHAR.

What is CHAR and VARCHAR?

Before diving deeper into the differences between the two data types, we must first make you understand what they are, right? Well, everything in this space is pretty simple because the names of the data types themselves suggest what they are able to do – CHAR stands for “character values” and VARCHAR stands for “variable character values.”

Both CHAR and VARCHAR differ from other similar data types by the fact that they both are considered to be fixed-length data types: we create both CHAR and VARCHAR data types with a “fixed” length that can range from 0 to 255 characters in size if CHAR is being used or from 1 to 65,535 characters if VARCHAR is in use. The bigger our data length is, the more space our characters will occupy (this will probably won’t be a very large problem, however, if your data grows, you might want to keep an eye out on both the structure of your database and learn how to work with big data sets), though this is common sense. What’s not sometimes very widely discussed though is the core differences between the two data types – after all, even their names are similar, so what’s the deal? Bear with us while we guide you through their world...

CHAR vs. VARCHAR

At first glance, both CHAR and VARCHAR might seem very similar – after all, they are defined in exactly the same fashion, aren’t they? Demo CHAR(5) would create a column named demo with a CHAR data type with a length of 5 on it, and demo VARCHAR(5) would create a column named demo with a VARCHAR data type.

However, when we dive deeper into these, we would notice that MySQL hides their differences inside of its architecture:

CHAR	VARCHAR
Fixed length	Variable length
Always takes the same amount of space regardless of what's stored	Amount of space taken varies: either 1 or 2 bytes + data
Can hold a maximum of 255 characters	Can hold a maximum of 65,535 characters
Uses static memory allocation	Uses dynamic memory allocation

As you can see, some of the differences are apparent, some are not – for example, we would imply that VARCHAR is a variable-length data storage type because of the name, but did you know that CHAR can only hold 255 characters? One of the reasons for that might be because of the fact that CHAR is known to always take up exactly the same amount of storage space – how can MySQL ensure that a column with a CHAR data type will not take up 5MB of storage if it holds 50,000 characters? With 255, that’s easy to do – the length of a CHAR column is defined when we create a table and it doesn’t change no matter what happens. This has its own downsides though – we can’t store very large text values. For you to imagine what CHAR can handle, head over to the Lorem Ipsum generator and observe the paragraphs. CHAR can handle approximately one-third of one paragraph – in other words, four-five sentences.

We have emboldened the text that CHAR could handle without issues in blue – see the problem? Such a data type might be a fit if we are storing very small amounts of text, but not so if we are working with larger-scale systems or even if we need to handle a couple of login forms (remember, we need to hash our passwords to make them not humanly readable – do we really want to take the risk of not being able to store them?)

In a nutshell, CHAR is a fit if we are strapped on storage space and do not plan on storing much data inside of our column.

With VARCHAR however, that’s a different story altogether – as you can understand, it’s a variable-length data type meaning that its rows can get up to 65,535 bytes of data in size. The amount of characters that this data type can handle is not limited to 255, though that’s what pretty much every developer uses if they’re dealing with data – do note that VARCHAR data types can also easily hold 6,000, 10,000 characters, etc., but it all depends on the collation and character set that is being used – for details on that topic, refer to the docs, but the bottom line is this – we can create tables with VARCHAR data types on columns so long as the row size doesn’t exceed 65,535 – simple as that. For example, if we have 7 columns and 6 of them are of 10,000 characters in size and the seventh has 6,000 we are in trouble, because the row size combined would exceed 65,535 – we would have 66,000 instead. You get the point.

Also, keep in mind that the amount of space such a data type takes up on the disk varies from 1 to 2 bytes – keep that in mind when designing systems with bigger sets of data, but other than that, do not worry too much – modern hard and solid state drives don’t make this a problem at all. Use them and you should be fine.

The last thing is that CHAR differs from VARCHAR in the amount it allocates memory for its data – CHAR allocates memory statically meaning that once the memory is allocated, its size cannot change, and VARCHAR does things the other way around – it allocates memory at runtime.

The bottom line is this though – VARCHAR is mostly used to work with medium-sized to big projects and CHAR is generally only used when we are strapped in size or our project is very small. Sacrificing a couple thousand bytes here or there isn’t an issue that should worry a database administrator – disk space is cheap and easy to acquire. However, with that being said, do keep in mind that if we are working with bigger sets of data, we should take care of the size of our data types accordingly. Other than that though, there shouldn’t be any major issues!

Summary

Both CHAR and VARCHAR are data types that are used very frequently – however, to use them properly, we must understand that CHAR is more of a fit when we deal with fixed-length data that does not exceed 255 characters, and VARCHAR is a fit when we deal with projects of a bigger scale. The amount of space these data types occupy does vary as well, however, it shouldn’t cause many issues when we aren’t working with bigger data sets. If you find yourself working with them, though, head over to our big data tutorial, and we will walk you through everything you need to know.

If you liked what you’ve read so far, we have good news – since Arctype is a SQL client, it can very easily help you take care of the data types you have no matter what they are – simply glance over at the left side of the tool and expand the table you are dealing with.

See how easy everything becomes when Arctype is in use? Give it a try today, run a couple of queries (hint: the “/” sign would return a bunch of statements that you can use to build your queries if you’re not sure where to begin), and we will see you in the next one!

MySQL’s UTF-8 Isn’t Real

Arctype Team — Thu, 11 Aug 2022 21:46:00 +0000

If you are familiar with MySQL, you probably already know that it comes with multiple character encodings. One of the main encodings in the MySQL world and on the web in general is UTF-8 – it is used in almost every web and mobile application, and is widely considered to be the “default” option as far as MySQL is concerned. UTF-8 also supports multiple character sets and has a couple of other features unique to itself: in this blog, we are going to go through them and also we are going to go through one feature in particular – the fact that MySQL’s “UTF-8” is not considered to be the “real” UTF-8. Confused? Read on!

What is UTF-8?

To start with, UTF-8 is one of the most common character encodings. In UTF-8, each character that we have is represented by a range of one to four bytes. As such, we have a couple of character sets:

utf8 which, in previous times, was considered the “de-facto” standard for MySQL in the past. Essentially, utf8 can also be considered to be the “alias” for utf8mb3.
utf8mb3 which uses one to three bytes per character.
utf8mb4 which uses one to four bytes per character.

UTF8 was the default character set in the past when MySQL was starting out and everything was great. However, talk to MySQL database administrators these days, and you will quickly realize that now that is no longer the case. Simply put, utf8, as such, is not the default character set anymore – utf8mb4 is.

utf8 vs. utf8mb4

The core reason between the separation of utf8 and utf8mb4 is that UTF-8 is different from proper UTF-8 encoding. That’s the case because UTF-8 it doesn’t offer full Unicode support, which can lead to data loss or even security issues. UTF-8’s failure to fully support Unicode is the real kicker – the UTF-8 encoding needs up to four bytes per character, while the “utf8” encoding offered by MySQL only supports three. See the issue on that front? In other words, if we want to store smilies represented like so:

We cannot do it – it’s not that MySQL will store it in a format of “???” or similar, but it won’t store it altogether and will respond with an error message like the following:

Incorrect string value: ‘\x77\xD0’ for column ‘demo_column’ at row 1

With this error message, MySQL is saying “well, I don’t recognize the characters that this smiley is made out of. Sorry, nothing I can do here” – at this point, you might be wondering what is being done to overcome such a problem? Is MySQL even aware of its existence? Indeed, it would be a lie to say that MySQL is not aware of this issue – rather, they are, but the MySQL developers never got around to fixing it. Instead, they released a workaround more than a decade ago along with MySQL 5.5.3.

That workaround is called “utf8mb4”. utf8mb4 is pretty much the same as its older counterpart – utf8 – it’s just that the encoding uses one to four bytes per character which essentially means that it’s able to support a wider variety of symbols and characters.

Use MySQL 8.0, work with data a little, and you will quickly notice that indeed, utf8mb4 is the default character set available in MySQL – moreover, it is speculated that in the near future utf8mb4 will become a reference to the utf8 standard in MySQL.

Flavors of utf8mb4

As time goes by and utf8 is being outpaced by utf8mb4 in almost all fronts, it’s natural that there are a couple of variations of collations that can be used. Essentially, these collations act as sort of a “set” of sorting rules that are designed to better fit specific data sets. utf8mb4 has a couple as well:

utf8mb4_general_ci is geared towards a more “general” use of MySQL and utf8. This character set is widely regarded to take “shortcuts” towards data storage which may result in sorting errors in some cases to improve speed.
utf8mb4_unicode_ci is geared towards “advanced” users – that is, it’s a set of collations that is based on Unicode and we can rest assured that our data will be dealt with properly if this collation is in use.

In this case, do note the “_ci” ending towards the collation: that stands for “case insensitive.” Case insensitivity is related to sorting and comparison.

These two “flavors” of utf8mb4 are used more and more – as newer versions of MySQL are also being released, we can also see that the utf8mb4_unicode_ci collation is the collation of choice for most people working with MySQL today. One fact is for certain – not all people using MySQL in this way know the functionalities and the upsides presented by utf8mb4 as opposed to its counterpart utf8, but they will certainly see a difference when they import data having unusual characters! Did we convince you to head over to the realm of utf8mb4 yet?

Properly Working with utf8mb4-based Data

Here’s how some developers go about creating databases and tables based on utf8mb4:

Except that this query gives us an error (below the query) which is a frequent point of confusion to beginners and advanced developers alike – MySQL is essentially saying that when we use a collation based on utf8mb4, we should also use a compatible character set, and in this case, latin1 isn’t valid, so whatever you do, keep these points in mind:

utf8mb4 is not the real utf8 in MySQL and its flavors (MariaDB and Percona Server): utf8 only supports 3 bytes of data, utf8mb4 supports 4 which is what utf8 should do in the first place. If utf8 is in use, some characters may not be displayed properly.
When we elect to use utf8mb4 instead of utf8 in MySQL, we should also make sure that we use an appropriate character set (utf8mb4.) Note the success message underneath the query:

Now we are good to go – we can store all kinds of characters inside of our database and not have MySQL error out with an “Incorrect string value” error! Woohoo!

Summary

UTF-8 in MySQL is broken – it is not able to support four bytes per character as UTF-8 is supposed to. “utf8mb4” can be used to solve this problem and it’s generally pretty easy to work with – simply choose a specific collation (in this case, choose either general if you’re using MySQL for a personal project or a small website, or a Unicode collation or if you’re using it for something more technical or if you want to push MySQL to its limits.)

Before pushing MySQL to its limits, though, be aware that aside from the documentation, there also are a couple of blogs like the one provided by Arctype that provide information on how to work with the relational database management system and all of its flavors. We cover how to work with MySQL and big data, how to optimize your database schemas, and so much more! If you are into databases, make sure to keep an eye out on the Arctype blog and we will see you in the next one!

Building REST Endpoints with Knex and PostgreSQL

Arctype Team — Tue, 09 Aug 2022 21:32:00 +0000

What is Knex and PostgreSQL?

Knex is a versatile, portable, and enjoyable SQL query builder for PostgreSQL, CockroachDB, MSSQL, MySQL, MariaDB, SQLite3, Better-SQLite3, Oracle, and Amazon Redshift, while PostgreSQL is an open-source object-relational database management system with a high degree of flexibility. It is capable of handling a wide range of use cases, including single machines, data warehouses, and web services with multiple concurrent users. It is a relational database management system that uses and extends SQL (hence the name), and it is broadly extensible to a variety of use cases beyond transactional data.

PostgreSQL stores information in tables (called relations) that contain tuples that represent entities (like documents and people) and relationships (such as authorship). Fixed-type attributes that represent entity properties (such as a title), as well as a primary key, are included in relationships. Attribute types can be atomic (for example, integer, floating point, or boolean) or structured (such as an array, nested JSON, or a procedure).

Why Use Knex?

Most developers use Knex as their query builder for the following reasons.

It allows them to create queries like they are writing Javascript code, while it handles the translation to SQL.
It has support for database management systems like PostgreSQL, MySQL, SQLite, and Oracle.
It supports both traditional node-style callbacks and a promise interface for cleaner async flow control, as well as a stream interface.
It is full-featured query and schema builders, transaction support (with savepoints), connection pooling, and standardized responses between query clients and dialects.

Prerequisites

As this is a hands-on demonstration tutorial, to get started, ensure your systems meet the following requirements:

You have Node.js version 14 or later installed
You have installed and set up Arctype
You have set up a PostgreSQL database
Install the Knex CLI (the command to do so is npm i -g knex)

With the above requirements met, let’s create a new database using Arctype. To get started launch the Arctype client, then choose the database you would like to work with:

Then provide the credentials of the database in question. It's all really simple to do, no hassle here!

If you already have a database set up, you can always create a new one by adding a new data source:

After you're done, you should see tables underneath your database on the left hand side in Arctype.

Create the Node.js Server

Now, create a new folder for your project and initialize a new project with the commands below.

mkdir knex-demo && cd knex-demo
npm init -y

Then install the required packages by running the following command:

npm install pg express knex

With the above command, you have installed the PostgreSQL Node.js module, express, and the knex module.

Now create the following folder structure below in the knex-demo folder.

Then in the app.js file, create a Node.js Express server with the code snippet below.

const express = require("express");   

const app = express();

app.use(express.json());

app.listen(3000, () => {
  console.log("Server is running on port 3000");
});

Lastly, modify the package.json file to add the script command.

 "scripts": {
    "start": "node src/app.js"
  },

Setup Knex

Once you’ve created the database, let’s set up Knex and connect to the database. To get started, run the command below on your terminal to initialize knex.

knex init

The above command will create a knexfile.js file in your project root directory with the code snippets to connect your database to different environments (development, staging, production.) By default, it uses the SQLite database in the development environment, you’ll need to modify the code to use your Postgres database.

// Update with your config settings.

/**
 * @type { Object.<string, import("knex").Knex.Config> }
 */
module.exports = {
  development: {
    client: "postgresql",
    connection: {
      database: "blogs",
      user: "postgres",
      password: "1234",
    },
  },

  staging: {
    client: "postgresql",
    connection: {
      database: "<Your Staging DB>",
      user: "username",
      password: "password",
    },
    pool: {
      min: 2,
      max: 10,
    },
    migrations: {
      tableName: "knex_migrations",
    },
  },

  production: {
    client: "postgresql",
    connection: {
      database: "<Your Production DB>",
      user: "username",
      password: "password",
    },
    pool: {
      min: 2,
      max: 10,
    },
    migrations: {
      tableName: "knex_migrations",
    },
  },
};

Your knexfile.js file should look like the code snippet above. You can modify the code to meet any other project requirements you might have.

Creating the Migration File

Now run the command below to create your migration file for the user’s table and define how the table will look like by running the command below.

knex migrate:make users

The above command will create a migrations/timestamp_users file in the root directory of your project. Now let’s define the schema in the ups and down functions.

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.up = function (knex) {
  return knex.schema.createTable("users", (table) => {
    table.increments("id").primary();
    table.string("name").notNullable();
    table.string("email").notNullable();
    table.string("password").notNullable();
    table.string("avatar").defaultTo("https://i.imgur.com/Xq2bZCY.png");
    table.string("bio").defaultTo("I am a new user");
    table.timestamp("created_at").defaultTo(knex.fn.now());
    table.timestamp("updated_at").defaultTo(knex.fn.now());
  });
};

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.down = function (knex) {
  return knex.schema.dropTable("users");
};

The code in the migrations/timestamp_users.js file should look like the above code snippet. We defined the user’s schema. The first one is an id field with autoincrement set to true and a unique constraint, after that we have the fields we need for the user’s table.

Then in the down function, we are dropping any existing table with the name users before we create our new table.

To create this table in your database you need to run the migrations command again, this time you need to add the latest flag to only commit the new changes in the file.

knex migrate:latest

Next, create the blogs schema by running the migration command below on your terminal.

knex migrate:make blogs

Then add the code below in the up function to define the blog’s schema.

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.up = function (knex) {
  return knex.schema.createTable("blog", (table) => {
    table.increments("id").primary();
    table.string("title").notNullable();
    table.string("content").notNullable();
    table.string("image").notNullable();
    table.timestamp("created_at").defaultTo(knex.fn.now());
    table.timestamp("updated_at").defaultTo(knex.fn.now());
  });
};

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.down = function (knex) {
    return knex.schema.dropTable("blog");
};

In the above code snippet, we created a blog schema and defined the fields we need in the blogs table. We are also dropping any existing table with the name blogs

Creating the Table Relationships

Now let’s create a relationship between the user’s schema and the blog’s schema. This way we can associate blogs with the users that create them. To do that, we need to update the code in the timestamps_blogs.js file with the following code:

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.up = function (knex) {
  return knex.schema.createTable("blog", (table) => {
    table.increments("id").primary();
    table.string("title").notNullable();
    table.string("content").notNullable();
    table.string("image").notNullable();
    table
      .integer("author")
      .unsigned()
      .references("id")
      .inTable("users")
      .onDelete("CASCADE");
    table.timestamp("created_at").defaultTo(knex.fn.now());
    table.timestamp("updated_at").defaultTo(knex.fn.now());
  });
};

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.down = function (knex) {
    return knex.schema.dropTable("blog");
};

In the above code snippet, we modified the author field to reference the id of each user in the user’s schema. The OnDelete will ensures the blog is deleted once the user’s account is deleted.

Next, create a db.js file in the config folder and add the code snippet below.

const knex = require("knex");
const config = require("../../knexfile");
module.exports = knex(config.development);

In the above code snippet, we are importing the config from knexfile and initializing the knex object, so since running the application on the development environment we’ll be calling the development config object.

Creating the Service

With our database tables created, let’s create a service that performs CRUD operations in the database tables. Create a userService.js file in the service folder and add the code snippet specified below.

const db = require('../config/db');

module.exports = userService = {
  getAll: async () => {
    const users = await db("users");
    return users;
  },
  getById: async (id) => {
    const user = await db("users").where("id", id);
    return user;
  },
  create: async (user) => {
    const users = await db("users").insert(user);
    return users;
  },
  update: async (id, user) => {
    const users = await db("users").where("id", id).update({
      name: user.name,
      email: user.email,
      password: user.password,
      avatar: user.avatar,
      bio: user.bio,
    });
    return users;
  },
  delete: async (id) => {
    const users = await db("users").where("id", id).del();
    return users;
  },
};

In the above code snippet, we imported the knex config. Then we created userService object and created the methods for the CRUD operations.

Next, create a blogService.js file in the service folder and add the code snippet below.

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

module.exports = blogService = {
  getAll: async () => {
    const blogs = await db("blog")
      .join("users", "users.id", "blog.author")
      .select(
        "blog.*",
        "users.name",
        "users.avatar",
        "users.bio",
        "users.email"
      );
    return blogs;
  },
  getById: async (id) => {
    console.log(id);
    const blog = await db("blog").where({ id });
    return blog;
  },
  create: async (blog) => {
    const blogs = await db("blog").insert(blog);
    return blogs;
  },
  update: async (id, blog) => {
    const blogs = await db("blog").where("id", id).update({
      title: blog.title,
      content: blog.content,
      image: blog.image,
    });
    return blogs;
  },
  delete: async (id) => {
    const blogs = await db("blogs").where("id", id).del();
    return blogs;
  },
};

In the above code snippet, we created the CRUD operations for the service blogService. In the getAll method we are joining the users table with the blogs table, using the select method to select the fields we want to show to the users - if we now call the service we can get the blogs and the users that posted them.

Creating the Controller

Now let’s create the controller to consume the service we just created. Let’s start with the user’s controller. So create a userController.js file in the controller folder and add the code snippet below.

const userService = require("../service/userService");
module.exports = userController = {
  getAll: async (req, res, next) => {
    try {
      const users = await userService.getAll();
      res.json(users);
    } catch (error) {
      next(error);
    }
  },
  getById: async (req, res, next) => {
    try {
      const user = await userService.getById(req.params.id);
      res.json(user);
    } catch (error) {
      next(error);
    }
  },
  create: async (req, res, next) => {
    try {
      const user = await userService.create(req.body);
      res.json(user);
    } catch (error) {
      next(error);
    }
  },
  update: async (req, res, next) => {
    try {
      const user = await userService.update(req.params.id, req.body);
      res.json(user);
    } catch (error) {
      next(error);
    }
  },
  delete: async (req, res, next) => {
    try {
      const user = await userService.delete(req.params.id);
      res.json(user);
    } catch (error) {
      next(error);
    }
  },
};

Now create a blogController.js file in the controller folder to consume the blogService with the code snippet below.

const userService = require("../service/userService");
module.exports = userController = {
  getAll: async (req, res, next) => {
    try {
      const users = await userService.getAll();
      res.json(users);
    } catch (error) {
      next(error);
    }
  },
  getById: async (req, res, next) => {
    try {
      const user = await userService.getById(req.params.id);
      res.json(user);
    } catch (error) {
      next(error);
    }
  },
  create: async (req, res, next) => {
    try {
      const user = await userService.create(req.body);
      res.json(user);
    } catch (error) {
      next(error);
    }
  },
  update: async (req, res, next) => {
    try {
      const user = await userService.update(req.params.id, req.body);
      res.json(user);
    } catch (error) {
      next(error);
    }
  },
  delete: async (req, res, next) => {
    try {
      const user = await userService.delete(req.params.id);
      res.json(user);
    } catch (error) {
      next(error);
    }
  },
};

Creating the API Routes

Next, let’s create the API routes for the controllers. To get started, create a user.js file in the routes folder and add the code snippet below.

const express = require("express");
const router = express.Router();
const userController = require("../controller/userController");

/* GET users listing. */
router.route("/").get(userController.getAll).post(userController.create);
router
  .route("/:id")
  .get(userController.getById)
  .put(userController.update)
  .delete(userController.delete);

module.exports = router;

In the above code snippet, we have imported the userController and created an express router. Using the express router we define the route handlers for the controllers.

Now create another file called blog.js in the routes folder to define the route handlers for the blog controller with the code snippet below.

const express = require("express");
const router = express.Router();
const blogController = require("../controller/blogController");

/* GET home page. */
router.route("/").get(blogController.getAll).post(blogController.create);
router
  .route("/:id")
  .get(blogController.getById)
  .put(blogController.update)
  .delete(blogController.delete);

module.exports = router;

Finally, import the routes in the app.js file and create a middleware for both routes with the code snippet below.

...
const userRouter = require("./routes/users");
const blogRouter = require("./routes/blog"); 

...
app.use('/users', userRouter);
app.use('/blog', blogRouter);\

...

Testing the API

Now let’s test the project to ensure everything works as expected. First, start y0ur server with the command below.

npm start

Then launch Postman or any API testing tool of your choosing.

The User Endpoint

Send a POST request to the endpoint localhost:3000/users with the payload below to create a user.

{
  "name":"name",
  "email":"name@gmail.com",
  "password":"1234",
  "bio":"I am a software dev."
}

Next, send a GET request to the same endpoint to get all the registered users. Go ahead and test out the other user’s endpoints.

The Blog Endpoint

Now send a POST request to the endpoint localhost:3000/blog with the payload below to create a blog for the user with id of 1 denoted by the author field.

{
    "title":"My First Blog",
    "content":"Blah Blah Blah",
    "image":"Image URL",
    "author":"1"
}

Then send a GET request to the same endpoint to get all the blogs.

Viewing User Data with Arctype

We have now successfully created our Blog application. Now, let’s look at the users’ data with Arctype. To begin, launch Arctype, click the Postgres tab, and enter the following Postgres credentials, as shown in the screenshot below (it's all the same as we did with MySQL at the start):

You should see the user, blog table and the knex migrations tables keeping record of the migrations made in the application. Now click on the blogs table to show the user’s blogs, as shown in the screenshot below:

Conclusion

By building a demo project, we’ve learned how to build REST endpoints with Knex and PostgreSQL. We started by introducing PostgreSQL and Knex and why you should use them, then we created a blog project for the demonstration. Now that you’ve gotten the knowledge you seek, how would you use a query builder in your next project? Consider learning more about Knex from their official website and take things even further!

Top 5 Google Cloud Database Services - Part 1 (SQL)

Arctype Team — Thu, 04 Aug 2022 22:17:55 +0000

Google Cloud Platform offers various cloud database tools and services based on what a company needs to accomplish with them. Thus, the different tools have different purposes.

In this article, we’ll be looking at the top 5 Google Cloud Database Services/Tools that support SQL in one form or another. I’ll take you through each of their features, benefits, and provide some code snippets. Above all, I’ll briefly explain when you can use them. Here is what we will cover:

Database Tool	Support	Use Cases
Cloud SQL	MySQL, PostgreSQL, SQL Server	CRM ERP e-Commerce SaaS Apps
Cloud Spanner	Supports two SQL dialects: Google Standard SQL and PostgreSQL.	Used for critical high-load transactions, for SQL and DML support, also used together with JDBC.
AlloyDB	PostgreSQL	Enterprise workloads that require high transaction throughput, large data sizes, or multiple read replicas.
Bare Metal Solution for Oracle	Shift Workloads of Oracle to Google Cloud	Creating new applications and microservices using Google services that connect to an Oracle database. More use cases can be found here.
BigQuery	Serverless, Multi-Cloud Data Warehouse	Multi-Cloud Analytics Real-time Processing Built-in ML

Cloud SQL

Cloud SQL is a tool that enables you to manage all your relational databases, supports MySQL and PostgreSQL, and also offers support for SQL Server. The tool eliminates the need for manual management and has robust features that make your process hassle-free.

Features

Cloud SQL automates all major DBMS-oriented processes such as cloud backups, replication, and encryption patches. It also ensures that your database scales automatically so there’s no downtime and you can perform all your tasks without any form of interruption.
You can easily connect your database systems with App Engine, Google Kubernetes Engine, and the Compute Engine based on your requirements and the platform also allows you to work with on-premise systems and data.
The platform is compliant with major standards such as ISO 27001, SSAE 16, and PCI DSS. This way it will always be in sync with the industry.
Besides cloud-backups, one can also automate the failover process and perform cloud migration with minimal downtime. Since it is serverless, various manual operations such as managing, monitoring, and provisioning aren’t required either.

Benefits

Highly secure.
Easily scalable.
Hassle-free setup.

When to Use

Cloud SQL is used when the storage requirement is less than 10TB. It performs end-to-end relational database management for all your systems as long as it is within this limit.

Sample Code

A nested query in Cloud SQL will look something like the code given below:

SELECT firstname, lastname, total_quantity
    FROM (SELECT buyerid, sum(qtysold) total_quantity
            FROM sales
            GROUP BY buyerid
            ORDER BY total_quantity desc limit 10) Q, users
    WHERE Q.buyerid = userid
    ORDER BY Q.total_quantity desc;

Cloud Spanner

Cloud Spanner provides all the functions of Cloud SQL and goes even further by providing 99.999% availability at all times. Furthermore, it provides higher row consistency and the performance is much more enhanced.

Features

Cloud Spanner enables the availability mentioned above across multiple regions and has much lesser downtime compared to four nines.
The performance is elevated because it shares the data based on the load and size. This way scaling becomes more efficient and requires lesser human intervention.
Data is consistent across multiple regions. This means no matter where the users are from and how many users are simultaneously working on the database, the changes are up-to-date and consistent.
Once you choose a granular instance, you don’t need to re-architecture or worry about scaling it.

Benefits

Automated scaling without any limits.
Available across the world, at any time.
Simple experience and better performance.

When to Use

As mentioned earlier, Cloud Spanner provides better performance and higher availability than Cloud SQL. Furthermore, it has no storage limit like Cloud SQL. So if your infrastructure requires all this and more, you should use Cloud Spanner.

Sample Code

Here instead of selecting or retrieving data from the table, you can emulate a temporary table name using the WITH clause.

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77)

The above command would create this table:

Last Name	SchoolID
Adams	50
Buchanan	52
Coolidge	52
Davis	51
Eisenhower	77

AlloyDB for PostgreSQL

AlloyDB is one of the latest tools launched by Google: the tool sets itself apart from Cloud SQL and Cloud Spanner. This is because the latter two are compatible with PostgreSQL, whereas AlloyDB is a standard PostgreSQL database at its core.

Features

AlloyDB is 4x faster for transactional workloads and 10x faster for queries compared to the standard PostgreSQL. In fact, it is twice as fast as Amazon’s Aurora PostgreSQL for transactional workloads.
Like Cloud Spanner, it has 99.999% availability across all regions.
The tool also allows moving legacy databases to their cloud with ease. This way it tries to make its infrastructure more open-source and eliminates issues such as licensing and other boundaries.
Furthermore, with Cloud SQL and Cloud Spanner, one has to integrate BigQuery for better analytics. With AlloyDB users won’t have to do the same as the functionalities are in-built and readily available.
It also has built-in Vertex AI, which enables users to leverage AI and machine learning models for analytics.

Benefits

The tool showcases elevated performance and efficiency, which is better than Cloud Spanner, Cloud SQL, and Amazon’s Aurora PostgreSQL.
A fit for AI and ML-powered systems.
Pricing is transparent and predictable without charges for licensing or opaque I/O.

When to Use

If your organization and infrastructure needs a more open-sourced way of functioning with maximum performance, efficiency, and features, then AlloyDB is your go-to Google tool.

Sample Code

Since AlloyDB is a PostgreSQL-compatible tool, the sample code is of the same query language. The query below will help you to insert values into a table.

postgres=# insert into dummy_table values('XYZ','location-A',25);
        INSERT 0 1
        postgres=# insert into dummy_table values('ABC','location-B',35);
        INSERT 0 1
        postgres=# insert into dummy_table values('DEF','location-C',40);
        INSERT 0 1
        postgres=# insert into dummy_table values('PQR','location-D',54);
        INSERT 0 1

Bare Metal Solution for Oracle

A Bare Metal Solution is essential for organizations that have specialized workloads and involve the usage of highly sophisticated services, but find it difficult to leverage the usual cloud. Google provides certified equipment for these workloads and places it in the data centers that run the cloud services.

This way organizations can move to the cloud, and use these high-intensive services that are integrated with the usual cloud services.

Features

With a minimal 2ms latency, organizations will be able to use cloud services.
All major Oracle capabilities such as database clustering, replication, and more.
The tool acts as a bridge between on-premises legacy systems and the cloud.
It also enables integration with services such as Ansible Based Toolkit and Kubernetes Operator for Oracle.

Benefits

Organizations can move their legacy-based infrastructure to the cloud without much hassle.
There’s minimal latency while using various services.
Allows access to all Oracle capabilities such as RAC and RMAN.

When to Use

This solution is mainly applicable to organizations with infrastructures that are rooted in Oracle. It enables them to modernize their whole setup by moving it to the cloud. It would also help companies to avoid being locked in and enable them to use functionalities from different vendors.

Sample Code

Since databases are connected with GCP database tools you can use any query language depending on the database tool. Here I’ll share a sample code for those running MySQL.

CREATE TABLE shop (
    article INT UNSIGNED  DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)      DEFAULT ''     NOT NULL,
    price   DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

BigQuery

BigQuery was launched to handle analytics dealing with millions of rows. As mentioned in the previous section it is mainly used with Cloud SQL and Cloud Spanner for the same purpose, since they have little-to-no analytical functionalities for such a scale.

Features

The tool enables natural language processing with the help of integrations such as Data QnA and Analyza. This allows people to work with data from chatbots, spreadsheets, and other custom-built UIs.
Like AlloyDB, even BigQuery integrates Vertex AI. It also has TensorFlow integrated and with these two, organizations can create custom AI/ML models of extensive complexity with just SQL.
BigQuery is one of the fundamental elements of Business Intelligence solutions, that provides operations such as transformation, analysis, visualization, and reporting.
The tool provides real-time data capturing, analytics, and replication, which helps organizations make quicker decisions, thus increasing their performance and efficiency.

Besides all these, there are other robust features that help companies work with petabytes of structured and unstructured data, allowing geospatial analytics, fine-grained data governance, and security on a row-based and column-based level.

Benefits

All data analytics operations reside in one place.
Ability to handle intensive data capturing and analysis for large-scale databases.
Real-time operations across different verticals.

When to Use

The tool can be used by large organizations that require real-time operations on petabytes of data. Its connectivity with other Google Cloud Database solutions such as CloudSQL and Cloud Spanner enables all kinds of operations from the most basic to the most sophisticated ones.

Sample code can be found below:

WITH locations AS
  (SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
  UNION ALL
  SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;

In this case, we’ve used array structures in a nested query. While this is merely a sample, with BigQuery, you perform much more complex operations and hence the queries get complex too.

Conclusion

These are some of the most popular Google Cloud Database tools that organizations use for their infrastructure. The code samples explain one simple task and you can explore further by looking at the official Google Documents and Github. There are more SQL tools offered by Google, which we’ll be covering in the second part of this article.

Handling File Uploads with NestJS and MySQL

Arctype Team — Tue, 02 Aug 2022 22:17:32 +0000

Introduction

Many developers despise dealing with file uploads. This can be attributed to a lack of knowledge about the best approach to take or difficulties determining how to configure their Nest.js application to handle file uploads. Many people may want to save their files directly to a MySQL database, or save image names and have the image saved on disk storage: it all depends on their preferences and the goals they want to achieve. This tutorial will teach you how to build a file uploading functionality using Nestjs and MySQL.

Prerequisites

Before you begin following this tutorial, ensure your system meets the following requirements:

Your system is running Node.js with version 14 or later.
Your system has a MySQL database installed.
You have installed Postman.

Setting Up NestJS

Once the above-mentioned requirements are met, proceed to install the Nestjs CLI and create a new project by running the following commands:

$ npm i -g @nestjs/cli
$ nest new file-upload

These commands will install the Nestjs CLI and create a new Nestjs project with the folder structure below.

After the Nestjs project has been created, move on to the next step - install the required dependencies for your application by running the following command:

npm install --save @nestjs/typeorm typeorm mysql2

In the above command, you’ve installed the TypeORM and mysql2 modules: they will enable you to connect your application to a MySQL database and perform operations on it.

Setup the MySQL Database

With the above dependencies installed, proceed to set up and connect to your MySQL database. To get started, add the code in the app.module.ts file with the code snippet below.

...
import { TypeOrmModule } from '@nestjs/typeorm';
import { Image } from './image.entity';

@Module({
  imports: [TypeOrmModule.forRoot({
    type: 'mysql',
    host: 'localhost',
    port: 3306,
    username: 'root',
    password: '1234',
    database: 'blog',
    entities: [Image],
    synchronize: true,
  }),
  TypeOrmModule.forFeature([Image])
  ],
  ...
})
...

In the above code snippet, we imported TypeOrmModule from the typeorm module we installed earlier. We used the forRoot method to connect the application to a MySQL database and pass in the database credentials. Another thing to point out here is that entities properties, which allowed us to specify the entities in our module and which will give us access to the Image entity you’ll be creating shortly: we also have the synchronize property set to true to automatically migrate the database.

Create Image Entity

Next, let’s create the Image entity we mentioned earlier. To get started, create a image.entity.ts file in the src directory and add the code snippet below.

import { Entity, Column, PrimaryGeneratedColumn, CreateDateColumn, UpdateDateColumn } from 'typeorm';

@Entity()
export class Image {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @CreateDateColumn()
    dateCreated: Date;

    @UpdateDateColumn()
    dateUpdated: Date;
}

In the above code snippet, we imported the decorators we need to create an entity. Using these decorators we defined the properties of the entity. We have the id field to generate random id’s for each record in the database using the @PrimaryGeneratedColumn() decorator, the name field to store the names of the images that will be uploaded using the @Column decorator, the dateCreated and dateUpdate fields to save the date a record was created and updated using the @CreateDateColumn() and @UpdateDateColumn().

Creating the Upload Service

With the Image entity created, let’s create a service to perform the CRUD operations to handle the file uploads. In the app.service.ts file, add the code snippet below.

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Image } from './image.entity';

@Injectable()
export class AppService {
  constructor(
    @InjectRepository(Image)
    private readonly imageRepository: Repository<Image>,
  ) {}

  async getImages(): Promise<Image[]> {
    return this.imageRepository.find();
  }

  async createImage(image: Image): Promise<Image> {
    return this.imageRepository.save(image);
  }

  async getImage(id: number): Promise<Image> {
    return this.imageRepository.findOneBy({ id });
  }

  async deleteImage(id: number): Promise<void> {
    await this.imageRepository.delete(id);
  }
}

In the above code snippet, we have imported the injectRepository decorator to inject the imageRepository into AppService and the Repository which provides you with the methods required to perform some operations on your database. So for the createImage image service, we are saving the name of the image that is been uploaded which will be passed through the controller.

Creating the Upload Controller

Now let’s create the controllers to use the services. In the app.controller.ts file and add the code snippet below.

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Image } from './image.entity';

@Injectable()
export class AppService {
  constructor(
    @InjectRepository(Image)
    private readonly imageRepository: Repository<Image>,
  ) {}

  async getImages(): Promise<Image[]> {
    return this.imageRepository.find();
  }

  async createImage(image: Image): Promise<Image> {
    return this.imageRepository.save(image);
  }

  async getImage(id: number): Promise<Image> {
    return this.imageRepository.findOneBy({ id });
  }

  async deleteImage(id: number): Promise<void> {
    await this.imageRepository.delete(id);
  }
}

In the above code snippet, we imported a couple of decorators like FileInterceptor, UploadedFile, and UseInterceptors. The FileInterceptor() interceptor to the route handler extracts the file from the request using the @UploadedFile() decorator. The FileInterceptor() decorator is exported from the @nestjs/platform-express package. The @UploadedFile() decorator is exported from @nestjs/common. The FileInterceptor() decorator takes two arguments, fieldName which is the string that supplies the name of the field from the HTML form that holds a file, and the options which is an optional object of type MulterOptions. This is the same object used by the multer constructor.

Regarding the createImage function, we have used the aforementioned decorators to handle the file upload using the FileInterceptor() passing the field name for the image and we modified the FileInterceptor() function to upload the image to disk by specifying the storage property using the diskStorage function available in multer. Then we specified the location for the images and generated random names for the images. Also, we added a filter property to restrict the upload of certain image formats. Now we get the file extracted using the @UploadedFile() decorator and get the name and save it to the database. This way we can use the name of each image to get the image from the storage location.

For the above code to work, you need to install multer by running the command below in your terminal:

npm i -D @types/multer

Then, you need to register the multer module in the array of imports in the app.module.ts file:

...
import { MulterModule } from '@nestjs/platform-express';


@Module({
  ...
  MulterModule.register({
    dest: './files',
  }),],
  ...

The above configuration tells multer to handle the file upload and the location to upload the file to. Last but not least, we should create a files folder in the src directory to actually store the files.

Serving Files

To actually serve the images uploaded on your application to the user, you need to install the serve-static module by running the command below.

npm install --save @nestjs/serve-static

Then, register the ServeStaticModule in the array of imports in the app.module.ts file with the code snippet below.

...
import { ServeStaticModule } from '@nestjs/serve-static';
import { join } from 'path';

@Module({
  ...
  ServeStaticModule.forRoot({
    rootPath: join(__dirname, '..', 'files')
  }),],
  ...

In the above code snippet, you’ve specified the location where the files are located and can be served from.

Testing the API

Now open Postman and test the application by sending a POST request to the endpoint localhost:4000/images, and pass in the payload in the request body as a form-data.

If you now look at the files folder, you should see the file you have uploaded. Feel free to go ahead: test and play around with other routes as well.

Conclusion

Through this tutorial, you’ve learned how to handle file upload with NestJS and MySQL. You’ve learned how to connect to a MySQL database using TypeORM and you have also created an entity and uploaded images to the Nestjs application.

You've successfully built a file upload feature with NestJS and MySQL: for the future of your database and application, though, keep in mind that keeping a constant eye on your applications and databases consists of much more than building features for them: SQL clients like Arctype will let you write SQL queries and optimize them as well as visualize the data currently existing in your database, and the content existing on the Arctype blog will let you learn how to optimize all of your MySQL instances for security, availability, and performance at ease as well as provide you with lots of insight in regards to the database world as a whole.

For further reading, you can also read more about uploading files in Nestjs. For an extra challenge, try extending the application by protecting the delete and update routes. What will you build next?

Deriving Ideal Indexes For Your SQL Database: A Guide

Arctype Team — Thu, 28 Jul 2022 22:13:31 +0000

Indexes are there to simplify our work when searching for data: they speed up SELECT queries at the expense of slowing down other kinds of queries like DELETEs, UPDATEs, and INSERTs instead. However, as awesome as indexes might be, they also need a lot of work to get right – in this blog, we will tell you how you should go about deriving ideal indexes for your database. The majority of the examples in this article will focus on MySQL: however, the concept is the same for all major database management systems available on the market today.

What are Indexes?

If you are familiar with database structures, great – because that’s essentially what indexes are! Indexes are database structures that can be used to quickly find rows having specific column values. At the expense of taking up disk space and time if your tables are big and you find yourself adding indexes on top of them, indexes allow databases to skip reading through entire tables and instead, only scan relevant rows which means that databases have less data to scan through.

What are Ideal Indexes?

With that out of the way, what is an “ideal” index? We could define a perfect index like so – such an index is any index that is capable of helping us pursue our data reading goals without putting a toll on time, without occupying much disk space in return and not slowing down INSERTs, UPDATEs, and DELETEs to a point where work is incomprehensible. In other words, an ideal index is any kind of index that allows us to speed up search operations, but at the same time doesn’t put a huge strain to maintain a database.

Before choosing an “ideal” index for our database instance, we must decide what kind of an index type we are going to employ. For that, it is always useful to employ a questionnaire:

What kind of searches are we running? Do we deal with wildcards? A B-Tree index would do.
Do we need exotic search operations (think queries involving wildcards and the like)? A FULLTEXT index will do.
Do we work with data that needs to be unique (think about data without duplicates)? We should head over to the UNIQUE INDEX space.

Once we have chosen our index type, it’s type to derive an ideal index.

Deriving Ideal Indexes

To not bore you with theory, we are going to get straight to the point – how do you derive an ideal index? It must be different for every scenario, no?

In most cases, the approach outlined below would do:

Pick an index that most suits your use case. In most cases, ordinary indexes would do. In others when we find ourselves searching for Boolean and other data, we employ a FULLTEXT index. For more details, refer to our indexing for high performance post.
Ensure your queries are designed properly – ensure that your SELECT query only selects the fields that need to be returned instead of using SELECT * and employ a WHERE clause – if it’s not there, indexes are generally useless and won’t be used.

The entire situation could look something like this.

a) Suppose you have five columns and you need to index three of them.

You decide to use a B-Tree index and index multiple columns at the same time.
Put the column that you use straight after the WHERE clause into the index first.
If you use ORDER BY, you should add the columns that you use this statement on afterwards. Do not use the columns that you’ve used in the previous step and do not change their order: that will make your query performance a little worse.
If we want our query to be as fast as possible, we add all other columns that were left out after completing the third step.

The steps above describe a covering index. Covering indexes are a type of index where all of the columns required for a query to execute are included in the index and it's frequently added to a table when using ALTER TABLE queries. Here's how one ALTER TABLE query would look in such a scenario:

ALTER TABLE demo_table ADD INDEX demo_idx(demo_c1, demo_c2, demo_c3);

demo_c1 is the column that goes straight after the WHERE clause, demo_c2 is the column that goes straight after the ORDER BY statement, and demo_c3 is our “remaining” column. That’s it: we now have a covering index! Now we will tell you what your SELECT query would look like. In most cases, it would look something like this:

SELECT demo_c3 FROM demo_table WHERE demo_c1 = ‘Demo’ [ORDER BY demo_c2 ASC|DESC];

demo_c1, in this case, would be the column that the search is being run on: demo_c2 would have integer values or dates, so MySQL could sort it. You get the idea.

Designing indexes in such a way might not always be possible, however, always consider how much data you have and what kind of queries you run. If you work with bigger data sets and indexing the entire column is simply not feasible, you might also find that it might be feasible to index a prefix of the data to make your query performance a little better: such an index won't occupy a big chunk of hard drive space and it will offer you slightly better performance as well.

For many other cases, a mix of performance optimization and indexing will be required and your steps will require work with a B-Tree index. Follow these steps:

Get an approximate number of the rows that are being held in your table that you need to index and ensure the table is running InnoDB as the primary storage engine (MyISAM is known to be unreliable as it is prone to crashes and similar things.)
Make sure your InnoDB engine is optimized for high performance by modifying your my.cnf file (modify my.ini if you intend to run Windows.)
Only add the index on columns that you run SELECT queries on. If you intend to ALTER the table, ensure to do it after you load data into it and not before as MySQL and its flavors make a copy of the table when altering it.

Follow these steps, ensure you read on our indexing and optimizing for high performance tutorials, and you should be good to go in regards to your database performance, but if you see that your database performance is still not up to par, it might be time to glance into your database structure.

Arctype and Your Database Structure

In case you’re not aware of it already, an ideal index is not really “ideal” if your database structure is messed up. If you fail to utilize basic concepts of normalization and don’t know the difference between normalization forms, you might face some problems as well. Thankfully, you don’t need to search far and wide in this space – Arctype is the tool that can help you take care of your database structure and indexes as well. Launch Arctype and you will instantly be suggested the steps you should take:

Click on one of your tables, and you will be able to see their structure!

Once you see the structure of a given table, you will no longer be hard-pressed to decide what kind of indexes you want to employ. You will also be able to better choose the length of a given data type: you will have the columns and their length already displayed in front of you. Can it get easier than that? Arctype will also display a list of tables on the left-hand side: expand those and you will be able to better understand what other tables consist of as well.

Once you write your queries, do not forget that the Arctype client also has a capability to share them amongst your team and colleagues and even enables you to edit your table data on-the-spot. Click on any index that is added onto a table, and you will see a query that will let you recreate it. Awesome, right?

However, we will not spoil all the fun for you. Try Arctype yourself and you will see what it can do: the features we have covered here are merely starting points, the client has a lot of other features to offer too.

Summary

Deriving ideal indexes in any kind of database management system is not a very straightforward process, however, with enough knowledge about queries, database structure, and data sets in general we can make it work – we hope that this blog post has provided you with some information on that front.

Keep in mind that we publish a wide variety of topics ranging from web development to analytics and dashboards, so if this article hasn’t yet hooked you into the Arctype world, make sure to have a read through our other articles inside the Arctype blog and you will be surprised with what you can find!

Use Django and the Dev.to API to Display Posts

Arctype Team — Tue, 26 Jul 2022 20:50:37 +0000

As a developer, you may be wondering if you need a blog. To be honest, I have been blogging for a while, and all I can tell you is that having a blog on your website has immense benefits. Let's just say that you have a website where you have a portfolio and you sell products or services. How can you drive more traffic to your website so that potential recruiters or customers can discover your products or services?

The best way to drive traffic to your website is by having a blog with useful content. A blog with useful content shows your audience and customers that you are a trusted source of information.

However, to have a blog on your website, you need a Content Management System which is abbreviated as CMS. A CMS is a software solution that helps someone create, manage, and modify content on a website without the need for specialized technical knowledge.

Most CMSs are paid and if you want to save a few dollars, a paid CMS is the last thing you want. In this article, I will show you how to add a blog to your website without having to spend a cent. I will be using Django, the Dev.to API, PostgreSQL, and the Arctype SQL Client.

What is Dev. to, Django, and Postgres?

Dev.to is a blogging platform where developers publish articles on topics that help other developers solve a problem or learn something new. Dev.to serves as a community of software developers getting together to help one another out. Django is a free and open-source Python-based web framework that follows the model-template-views architectural pattern. Django encourages rapid development and clean, pragmatic design.

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing SQL compliance.

Prerequisites

First things first, to be able to follow through with this tutorial, you need a text editor installed on your machine. In this case, I will be using Visual Studio Code editor but you can use the code editor of your choice. Also, make sure that Python and PostgreSQL are installed on your machine.

Project Setup

To get started with this tutorial, create a folder named Blog on your machine. Right-click on the folder and open it with the Visual Studio Code text editor or a text editor of your choice. When using Visual Studio Code, click the terminal option at the top and select New Terminal as shown below.

Let us now install Django in our Blog Django project. To install Django, we will use a packaging tool for python called Pipenv. Pipenv is used to manage virtual environments in Django projects. You can install Pipenv to your machine by using this guide. Now run the command below to install Django in your project:

pipenv install django

Then create a virtual environment in the project by running the command shown below.

pipenv shell

Now you should see parentheses around Blog on your command line as shown below.

((Blog))

The parentheses around Blog indicates that the virtual environment has been activated.

Let us now create a new Django project called Articles by running the command shown below. Don’t forget the period (.) at the end.

django-admin startproject Articles .

Now run the command shown below and then visit http://127.0.0.1:8000:

python manage.py runserver

You should see the Django welcome page as shown below.

Let us now create an app called posts in our Django project. From the command line, quit the server with control+c. Then run the command shown below.

python manage.py startapp posts

On your Visual Studio Code editor window, click the Articles folder and open the settings.py file as shown below.

In the settings.py file, scroll down to the INSTALLED_APPS section where you will see six built-in Django apps already there. Add our new posts app at the bottom as shown below.

INSTALLED_APPS = [
   'django.contrib.admin',
   'django.contrib.auth',
   'django.contrib.contenttypes',
   'django.contrib.sessions',
   'django.contrib.messages',
   'django.contrib.staticfiles',
   'posts',  # new
]

Setting Up Postgres

After installing Postgres in your machine, run the command below to confirm the installation.

psql --version

You should be able to see the version of Postgres installed into your system. Now run the command shown below to open Postgres prompt.

sudo -u postgres psql postgres

Once PostgreSQL has opened on command line, run the command below to set a password for it.

\password postgres

To quit the PostgreSQL prompt, run the command shown below.

\q

To open the Postgres prompt again, run the command shown below and enter the password you set for postgres.

psql -U postgres -W

Let us now create a database in Postgres that will be used to store posts for our blog project by running the command below.

CREATE DATABASE posts;

Then run the command shown below to see if the database you created is on the list of databases in Postgres.

\l

As you can see from the screenshot below, the post database was created in PostgreSQL.

Integrating Postgres With Django

Let us install the psycopg2 package that will allow us to use the Postgres database we created by running the command below.

pipenv install psycopg2

Open the settings.py file in the Articles folder and scroll down to DATABASES section that looks as shown below.

DATABASES = {
   'default': {
       'ENGINE': 'django.db.backends.sqlite3',
       'NAME': BASE_DIR / 'db.sqlite3',
   }
}

The project is configured to use SQLite as a database. What you need to do is change this so that the project can use the Postgres database we created instead.

Change the engine so that it uses the Postgres adaptor instead of the sqlite3 adaptor. For the NAME, use the name of your database where in this case it is posts. You also need to add login credentials for your database.

The USER should be postgres and PASSWORD should be the password you set for postgres. The DATABASES section of the settings.py file should now look as shown below.

DATABASES = {
   'default': {
       'ENGINE': 'django.db.backends.postgresql',
       'NAME': 'posts',
       'USER': 'postgres',
       'PASSWORD': 'password',
       'HOST': 'localhost',
       'PORT': '',
   }
}

Django Models

Let us now create a Django model that defines fields of data that we want stored in the Postgres database. To create the model, navigate to the models.py file in the posts folder as shown below.

Your models.py file should now look as shown below.

from django.db import models
import datetime


class Article(models.Model):
   title = models.TextField(blank=True)
   description = models.TextField(blank=True)
   cover_image = models.TextField(blank=True)
   article_body = models.TextField(blank=True)
   published_at = models.DateTimeField(
       default=datetime.date.today, blank=True, null=True)

   def __str__(self):
       return self.title

Let us create and apply migrations to our database by running the commands below.

python manage.py makemigrations

python manage.py migrate

Let us now register the model by opening the admin.py file in the posts folder. The admin.py file should now look as shown below.

from django.contrib import admin
from django.contrib.auth.admin import UserAdmin

from .models import Article


class ArticleAdmin(admin.ModelAdmin):
   list_display = ('id', 'title')


admin.site.register(Article, ArticleAdmin)

The next step is to create an admin account by running the command below. You will be asked to to select a username, provide an email address, choose and confirm a password for the account.

python manage.py createsuperuser

Django Views

Now visit Dev.to API documentation to learn how to get the API key together with various API endpoints. On the left side of the web page, select the Authentication section to learn how to get an API key.

Then below the authentication section, select articles section. Scroll down the drop-down menu to User’s published articles. This is the endpoint that allows the client to retrieve a list of published articles on behalf of an authenticated user.

On the right side of the page, you can see the request samples that you can make to the Dev.to API.

Let us now create a python view function on the views.py file that makes http requests to Dev.to API as shown below.

from django.shortcuts import render
import requests
from .models import Article


def get_articles(request):
   all_articles = {}

   API_KEY = 'Your_API_KEY'

   url = 'https://dev.to/api/articles/me/published'

   headers = {'api-key': API_KEY}

   response = requests.get(url, headers=headers)

   data = response.json()

   for i, item in enumerate(data):
       article_data = Article(
           title=data[i]['title'],
           description=data[i]['description'],
           cover_image=data[i]['cover_image'],
           article_body=data[i]['body_markdown'],
           published_at=data[i]['published_at']
       )

       article_data.save()

       all_articles = Article.objects.all().order_by(
           '-published_at').distinct('published_at')

       return render(request, "blog.html", {"all_articles": all_articles})

In order to display the body of the article, we will need to create another view function that queries the article by id.

def blogBody(request, id):
   article = Article.objects.get(id=id)

   return render(request, "blogBody.html", {"article": article})

Your views.py file should now look as shown below.

from django.shortcuts import render
import requests
from .models import Article


def get_articles(request):
   all_articles = {}

   API_KEY = 'Your_API_KEY'

   url = 'https://dev.to/api/articles/me/published'

   headers = {'api-key': API_KEY}

   response = requests.get(url, headers=headers)

   data = response.json()

   for i, item in enumerate(data):
       article_data = Article(
           title=data[i]['title'],
           description=data[i]['description'],
           cover_image=data[i]['cover_image'],
           article_body=data[i]['body_markdown'],
           published_at=data[i]['published_at']
       )

       article_data.save()

       all_articles = Article.objects.all().order_by(
           '-published_at').distinct('published_at')

       return render(request, "blog.html", {"all_articles": all_articles})


def blogBody(request, id):
   article = Article.objects.get(id=id)

   return render(request, "blogBody.html", {"article": article})

Django URLs

Now we need to configure the urls of our project by creating a new urls.py file in the posts folder. The urls.py file should now look as shown below.

from django.urls import path
from .views import blogBody


urlpatterns = [
   path("blog", blog, name="blog"),
   path("article/<int:id>", blogBody, name="article"),
]

The last step is to update the Articles/urls.py file - the file should look as follows.

from django.contrib import admin
from django.urls import path, include

urlpatterns = [
   path('admin/', admin.site.urls),
   path("", include("posts.urls"))
]

Django Templates

Create a directory called templates, then create two HTML files called blog.html and blogBody.html like so.

mkdir templates

touch templates/blog.html

touch templates/blogBody.html

Let us now update Articles/settings.py file to tell Django the location of our new templates directory. This is a one-line change to the setting ‘DIRS’ under the Templates section as shown below.

TEMPLATES = [
   {
       'BACKEND': 'django.template.backends.django.DjangoTemplates',
       'DIRS': [os.path.join(BASE_DIR, 'templates')], #new
       'APP_DIRS': True,
       'OPTIONS': {
           'context_processors': [
               'django.template.context_processors.debug',
               'django.template.context_processors.request',
               'django.contrib.auth.context_processors.auth',
               'django.contrib.messages.context_processors.messages',
           ],
       },
   },
]

Don’t forget to specify import os at the top of the settings.py file or you will get an error that os is not defined. Your templates/blog.html file should now look as shown below.

{% for article in all_articles %}
<img src="{{article.cover_image}}" alt="">
<h4>{{article.title}}</h4>
<p>{{article.description}}</p>
<a href="{% url 'article' article.id %}">Read More...</a>
{% endfor %}

Navigate to 127.0.01:800/blog and you should see articles from Dev To as shown below.

The Article Body

When we make the API call, the article body is fetched as article markdown. The markdown is received from the API as a string. This means it will look like the raw content you enter on DEV To, rather than the preview/published version (i.e. with all the markdown syntax like ## for headings). To display it on your website as it appears on DEV To, you'll need to add an extra step to turn the string markdown into HTML. There are a lot of markdown parsing libraries that can do this for you but in this project, we will use a markdown parsing library for Django called Markdown.

To use Markdown we will need to install it into our project using the command below:

pipenv install markdown

Let us create a custom template filter that uses Markdown. Create a templatetags directory within our posts app and then a markdown_extras.py file as shown below.

mkdir posts/templatetags

touch posts/templatetags/markdown_extras.py

The file itself will import the markdown package and use the fenced code block extension.

# posts/templatetags/markdown_extras.py
from django import template
from django.template.defaultfilters import stringfilter

import markdown as md

register = template.Library()


@register.filter()
@stringfilter
def markdown(value):
   return md.markdown(value, extensions=['markdown.extensions.fenced_code'])

Now let us load the custom filter into our template so that content written in Markdown will be outputted as HTML. Our blogBody.html file should now look like this:

{% load markdown_extras %}

<h4>{{article.title}}</h4>
<img src="{{article.cover_image}}" alt="">
<span>{{article.published_at }}</span>
<p>{{article.article_body | markdown | safe}}</p>

Navigate to 127.0.01:800/blog and click the read more button at the bottom. You should be redirected to the article body as shown below.

Querying Postgres Data Using Arctype SQL Client

To view or interact with blog data stored in PostgreSQL, you can use a SQL client and database management tool like Arctype. To use Arctype, navigate to Arctype homepage and download the Arctype app onto your machine. At present, Arctype supports Linux, Windows, and MacOS:

Once the app has been installed, open it and you will be prompted to create an account as shown below.

Once you have created an account, you will be prompted to select your database. In this case, select Postgres.

The next step is to add Postgres credentials to connect Arctype to your Postgres database. Use the credentials you created when created a database in the Postgres prompt.

Once the database connection is successful, you should be able to access tables in the posts database in Postgres as shown below.

If you want to learn how to create and run queries in Arctype, check out this guide.

Conclusion

In this article, you've learned how to create a blog on your website without having to use a paid content management system or creating one from scratch. I've walked you through the steps to successfully display Dev.to posts on your blog using Django and the Dev.to API. All of the code provided in this blog is available on GitHub, if you've enjoyed this article make sure to stick around the Arctype blog for more content, and until next time.

When to Use JSON in Your Database

Arctype Team — Thu, 21 Jul 2022 21:17:00 +0000

In this blog, you will learn when you should consider adding JSON data types to your tables and when you should avoid it.

Most relational database technologies can now store JSON data and perform queries on it. The introduction of the JSON data type to relational databases represented a turning point and opened up many new possibilities. Considering the innovative nature of this feature, you might have several doubts about whether adopting JSON data is the right choice. In particular, the risk of abusing JSON and losing the benefits of the relational model is high. That is why having a clear understanding of the advantages and disadvantages that JSON data can bring to a relational database is so important.

In this blog, we will explore everything you need to know about JSON columns and tell you when best to rely on them.

What Is JSON?

JSON stands for JavaScript Object Notation and represents a lightweight, easy-to-understand, human-readable data format. In a relational database, the JSON data type is a special binary or text column that allows you to store data in JSON format. Here's an example of JSON:

{
  "user": {
    "id": "1",
    "name": "Maria",
    "surname": "Williams",
    "hobbies": [
      {
        "name": "Tennis",
        "since": 1985
      },
      {
        "name": "Reading",
        "since": 1974
      }
    ]
  }
}

The relational databases that support JSON generally come with JSON-specific functions and operators to query and perform operations on this data type. For example, you can learn more about what PostgreSQL has to offer when it comes to JSON data here.

Why Do Relational Databases Support JSON?

Over time, the features offered to users have become increasingly complex. As a result, it is unlikely that the right data structure to support an application can be purely relational. The importance of data is increasing, and the way to represent it is evolving accordingly. Those who develop relational database technologies know this and have introduced the JSON data type to support the new way of thinking about data.

MySQL introduced JSON support starting from version 5.7 which became available to the public in 2015. PostgreSQL supports JSON data type since version 9.2, released in 2012. SQL Server has supported JSON since SQL Server 2016. As you can see, the majority of the most popular RDBMSes have the capability of storing JSON data. This does not mean that the relational model is dying or is no longer useful. On the contrary, it is simply evolving and adapting to new needs.

After all, relational database technologies support JSON only as a data type. This means that you can add one or more JSON columns to your relational tables. This does not radically disrupt the relational approach and represents just a possibility, a useful and powerful feature to have.

When To Store JSON Data in a Relational Database?

Embracing the JSON data type and adding JSON columns to your tables can bring several advantages to your relational schema. This is especially true when accomplishing a couple of goals defined below.

Using JSON for Logging Purposes

You should be able to easily read and understand your log data, and the JSON format is a great way to store it. Therefore, you should consider turning your log data into JSON format and storing it in JSON columns. In addition, having a JSON column containing log data is an effective approach to keeping track of what happened just by looking at a row. With this approach, you do not have to define new tables, perform JOINs, or spend time retrieving the log data.

JSON columns are also useful for logging where the data came from, especially when importing data via API. Considering that API responses are generally in JSON format, storing them in a JSON column is an effortless solution to not lose this data and make use of it when needed.

To Store Permissions and Configurations

Not all users may have access to the same features and sections of your application. Similarly, each user might configure your application based on their preferences. These are two common scenarios and involve data that changes a lot over time. This is because your application is likely to evolve, involving new configurations, views, features, and sections. As a result, you have to continuously update your relational schema to match the new data structure. This takes time and energy.

Instead, you can store permissions and configurations in a JSON column directly connected to your user table. Also, JSON is a good data format for your permissions and configuration. In fact, your application is likely to treat this data in JSON format.

To Avoid Slow Performance on Highly Nested Data

If performance is a key requirement and your application involves data that is highly nested, you should consider JSON columns. This is particularly true when you have to use it as one piece. In a relational approach, you would have to structure your data in several tables. To retrieve it, you would have to nest several JOINs, which would make queries very slow.

On the contrary, by storing this nested data in a JSON column, you can retrieve it with a simple SELECT query. This would make your data retrieval process faster while keeping your data structure easy.

When To Avoid JSON Data in a Relational Database?

Some consider using the JSON data type as an antipattern and something to adopt sparingly. The reason is that using a format without constraints such as JSON can undermine your relational schema. In detail, you should avoid JSON in the following cases.

You are Not Sure what Data to Store In the JSON Column

The JSON format is flexible, but this does mean that you can use it to store whatever you want. Before adding JSON columns to your database, you must clearly define their purpose. Otherwise, they are likely to become junk drawers. If this happens, the consequences for the future of your application can be deadly. The only way to avoid it is by designing a data format for your JSON columns.

Avoid using JSON columns if you do not know how to use them. Having JSON columns, but not storing JSON data in them means having no data validation on the database level, which can lead to no consistency or integrity at the application level.

You Do Not Want to Deal With Complex Queries

Storing data in JSON format is cool, but do not forget that it comes with additional costs and complications. One of these is that querying data in JSON columns leads to more complicated-looking queries.

Here is what a query involving JSON columns looks like in PostgreSQL:

And here's how the equivalent query looks like in a traditional scenario:

The second one is much easier to read and understand - avoid using JSON if the benefit brought by JSON is not enough to justify the complexity of the new queries.

You Have a Strongly Typed ORM

When mapping your database with a strongly typed ORM technology, you may encounter problems involving data types. This is because data types at the database level are different from data types at the application level. When you want to deal with the data stored in JSON columns at the application level, you have two approaches.

The first one is to define JSON columns in your ORM mapping as a string and then transform them into an object. Otherwise, if your ORM supports this option, you can map the column with the custom structured type. In either case, the application or the ORM must perform the data conversion behind the scenes. This comes at a cost and can compromise backend performance.

Conclusion

Data has evolved, and the relational model might no longer be enough to represent data efficiently. This is why the vendors of some of the most popular relational database technologies introduced ways to deal with unstructured data by using the JSON data type. Thanks to this, you can introduce the benefits of NoSQL into your relational database. Here, we looked at what JSON data type is, why it was introduced, and when to use or avoid it. Make sure to stick around our blog for more content, and until next time.

How To Design a Multi-Language Database

Arctype Team — Tue, 19 Jul 2022 21:28:32 +0000

Today we look at three best-practice database designs to store data in multiple languages and easily scale to new markets.

Reaching millions of users with an application is every developers dream. Achieving this goal becomes easier if users from all over the world can use your application. Since not all users know English or your target language, you need to design your application to be multilingual. This would not be possible without a multi-language database.

Designing a multi-language database that can easily scale to new languages is not easy. This is why you should rely on best practices. Here, you will see why you need a multi-language database, three great multi-language database designs, and how to choose the best one for you.

Let’s now learn everything you need to know about multi-language database design.

Why Do You Need a Multi-Language Database?

You never know how much your project will grow, and your small app for your friends could become an international service used by millions of people. If you want your product to be able to scale internationally, you need to design it so that you can easily adapt it to different cultures and markets. That is what internationalization is about.

Building a database ready for internationalization means designing a database that can store multilingual data. In other words, the backend should be able to provide data in multiple languages. To do this, the backend should connect and retrieve this data from a multi-language database.

Notice that giving users the possibility to switch between several languages is a nice feature to have. This is especially useful for polyglots or non-native speakers. So, even if your project is small and targets a local market, you should consider a multi-language database. After all, you cannot know in advance how successful your project will be and which users will use it. Let’s now learn why choosing the right multi-language database design for your needs is crucial.

Why You Should Design Your Multi-Language Database Carefully

Changing a database is a very costly operation in terms of time and energy because it triggers a ripple effect. Modifying the structure of the database involves changing the backends connected to it. This may also require you to adapt the frontends that rely on those backends accordingly. As you can see, changing a database structure is not an operation without consequences. For this reason, you should design a database that can scale easily and you do not have to change frequently.

As you can imagine, there are several ways to design a multi-language database, and each solution has its pros and cons. Considering the importance of the database structure for an application, you have to design your multi-language database wisely. This is why you should not start from scratch, but rely on best practices.

Let’s now take a look at some multi-language designs based on best practices.

3 Multi-Language Database Designs

Let’s delve deeper into the pros and cons of three multi-language designs I personally used in my experience as a full-stack web developer working for startups all over the world.

1. Column Approach

In this approach, each field in a multilingual table has a number of columns equal to the number of languages supported by the database.

Specifically, this is what the column name template looks like: columnName_languageCode

Pros

Simple: it is easy to implement.
Fast: it does not involve JOIN or slow queries.
Easy to deal with non-translated fields: if the translation for a field is missing, you can just use COALESCE. E.g. COALESCE(name_it, name_en) → returns name_it if it is not NULL, otherwise the name_en default value.

Cons

Hard to maintain: adding a new language requires updating all multi-language tables in the database. This also means that you need to change your ORM mappings accordingly.
Not scalable: the number of columns in tables grows with the number of languages supported by the application.
Complex SELECT conditions: forget about SELECT *. You need to specify each column in your SELECT clause.

2. Row Approach

In this approach, there is one row for each language. The key to identify a multi-language entity is the following a composite primary key:

<id, languageCode>

Pros

Simple: it is easy to implement.
Fast: retrieving the translated content only requires a WHERE condition on languageCode.

Cons

Complex keys: using a composite primary key makes identifying an element and JOIN queries more complex.
Duplicated content: to simplify things, non-translated columns generally store the same content saved in the columns of the default language row. This means that you will have a lot of duplicate content in multi-language tables.

3. Translation Table Approach

In this approach, each column of a multi-language table is an external key to a translation table. In other words, there is a translation table for each table that involve multi-language fields.

Pros

Scalable: adding a new language does not involve changes to the database structure.
Supports historical data: you can use the translation table to keep track of the translation history for each field in an entity.
Centralized: all translations for an entity are stored in one place.

Cons

Complex queries: queries become more complex because the information associated with a multi-language entity is spread over two tables.
Slow queries: retrieving all the info associated with an entity requires as many JOINs as the number of columns. Considering the burden that JOINs have on performance, this can easily become a problem.
Duplicated tables: it increases the size of your database in terms of tables. Plus, you need two tables to define each multi-language entity.

What Is the Best Multi-Language Database Design for You?

As you can see, every multi-language database design has the same amount of pros and cons. This means that there is no preferred approach in absolute terms. You have to choose carefully based on your requirements, needs, and goals. I have personally used all three approaches in different projects and learned the lessons I've shared.

The column approach is particularly useful when dealing with Big Data especially from the perspective of having a database with tables with several millions of rows when you simply cannot afford JOIN queries or duplicated content. Although it is not the most scalable solution, the column approach is the only viable design with large data.

On the other hand, the row-by-row approach is useful when the branches of your company branches operating in the local market have freedom of action. In this case, duplication of content can even be an advantage.

Finally, the tabular approach is the right design if you are looking for an elegant and scalable solution and do not mind the performance drawback. The table approach is best for projects where you know that the data will not grow too large.

Conclusion

Designing a database that can store multilingual data is essential to help your business scale internationally. Also, it enables non-native speakers to use your application. Changing a database is a complex and time-consuming operation with non-negligible consequences on the application architecture. This is why you should design your database as multi-language right from the start. Here, we have looked at three different approaches to designing a multilingual database, studying their pros and cons, and going into detail about which one is best for you.

The Darker Side of ALTER TABLE: A Guide

Arctype Team — Thu, 14 Jul 2022 20:02:53 +0000

If you frequently find yourself immersed into the MySQL world, there’s probably no need to explain to you what SQL queries are and what they do. Chances are, you know them like your five fingers. SELECT queries allow us to read data, INSERT queries allow us to insert data into a given database, UPDATE queries allow us to update data for certain rows or for the entire table if we wish, DELETE queries let us delete records from a table, etc. If you’ve ever dug deeper into the world of MySQL though, you must know that there is one more query that is very important for both database administrators and developers – that query is ALTER TABLE.

What is ALTER TABLE?

Simply put, ALTER TABLE is a query in MySQL that allows us to modify (or ALTER) the structure of a table. The things that ALTER TABLE can do are numerous – ALTER TABLE can be used to change names of tables or columns, or add or modify columns as well. On its end though, ALTER TABLE is mostly used when indexes are involved – when developers need to add indexes, they change the structure of their tables, and that's where ALTER TABLE comes into play once again.

The Speed of ALTER TABLE

The speed of ALTER TABLE statements is, obviously, directly dependent on the size of the tables or columns we want to modify – however, there’s one caveat. ALTER TABLE performs all of its operations on a copy of the table, and not on the table itself. In other words, when MySQL is ordered to modify a table and an ALTER TABLE statement is run, MySQL makes a copy of the table that is being modified, inserts the data that we currently have inside of our table into it, performs all of the required operations there, and copies the data back into our table – only then we see the results.

ALTER TABLE might work a little differently if we are not using InnoDB as our primary storage engine of choice – MyISAM will not show the rows that are being inserted into table until all operations have been completed, but nonetheless, the core premise remains the same. The speed of such a statement is directly dependent on the factors outlined below.

The structure of our database tables.
The size of our tables.
Our web and database server.
Our MySQL configuration.
The way we run queries.

First off, the structure of our tables is important because queries involving columns that were not “cared for” (indexed, etc.) properly will be slower than they should be. Also, keep in mind that if our tables were not using normalization, our queries will be slower than they should be, especially if our tables are big. Imagine copying possibly millions of unnecessary rows into another table…: sounds slow, doesn't it?

Secondly, the size of our tables does indeed matter because the bigger our tables are, the longer time they will take to copy over. The second point is also heavily related to all of the other points because we also have to think about how our web server is configured and about the way we run our queries for them to complete without issues and as be as quick as possible: for example, if our database server is configured to use 80% of available operating memory, chances are that our queries would probably complete a whole lot faster than with the default configuration. Also, if we run multiple ALTER TABLE queries one after another and are low on disk space, we would risk running out of it altogether. Allow us to illustrate.

The Dark Side of ALTER TABLE

Let’s say we have a dedicated server with 16GB RAM and 100GB of hard drive space. A good DBA would probably configure it to occupy 8GB or more of operating memory, and suppose that the table that we want to run ALTER TABLE queries on consists of 100 million records and isn’t normalized. All in all, everything that this table consists of occupies 20GB of space on the disk.

At this point, we might probably have a couple of other tables we work with – say, they occupy another 50GB of hard drive space, but you get the point. This is the query we run: feel free to adapt, then copy and paste it into your Arctype client.

ALTER TABLE demo_table MODIFY demo_column VARCHAR(150) NOT NULL AFTER another_column;

First, MySQL would make a copy of the table on the disk, then insert the current data into it, perform all of the necessary operations, and only then swap the old and new tables together: remember that we have 30GB of disk space left? After the temporary table would be created, we would have 10GB left. By then, we would probably start praying that the index occupies less space than 10GB and our queries complete successfully. If we run out of disk space, our queries get interrupted. Do we really want to see results like this? Of course not.

Avoiding Problems with ALTER TABLE

As you can see, once ALTER TABLE gets deeper involved in our database processes, we may very well be faced with multiple problems. How do we solve them?

One way would be to use tools that make a copy of the tables and modify it using triggers to keep it accessible while the new table is being prepared: in other words, to change the structure of tables without locking them up: for a full guide, head over here.

Another way we can accomplish such a task would be manual, meaning that we can also use an approach like so:

We need to ensure that INSERT statements would be quick – the table that we create should not have any indexes, and our MySQL instances should be configured properly.
We need to create a new table.
Once our table is created, we need to copy the data over from our old table into our newly created table using INSERT INTO demo2 SELECT * FROM demo where demo2 is the new table and demo is the name of the old table. Before doing that though, ensure that the structure of both tables are the same (see image below point #5.)
We remove (drop) the old table: DROP TABLE demo.
We rename the new table to the name of the old table: ALTER TABLE demo2 RENAME TO demo.

If we prefer to complete steps manually, such an approach will do. However, do keep in mind that such an approach is pretty much what ALTER TABLE does in the first place, and that in some corner cases, DROP TABLE might become awfully slow to execute – in that case, kill the query by using SHOW PROCESSLIST and then KILL a specific query ID. Then, execute a TRUNCATE TABLE statement – truncating tables is usually way faster than removing them altogether.

That’s it – problem solved!

You might also have noticed that we’ve used the Arctype client in the majority of examples in this blog – and that’s not without a reason. Arctype builds an advanced SQL client that can help us run SQL queries, complete parts of them automatically, share the results of queries with a team, modify our table structure “on-the-fly” and even build custom dashboards. Can it get better? It really does not. Try the Arctype client today, take care of your data, and normalize your databases and schemas, of course, use ALTER TABLE queries cautiously, and we will see you in the next blog!