Categories
Cloud Entrepreneurship General Topics and tips Sotfware & Developers & DevOps Storage Tools & How-Tos

Picking the SQL Database For You

A Comparison of SQL Database Flavors: How To Pick The Right SQL Database Flavor For You?

SQL, or Structured Query Language, is a database that has widespread uses with many flavors– which we will explore. As such, in our data-driven world, SQL is fast becoming an essential skill. Therefore, in this article, we will look at the most popular flavors of SQL databases individually and then compare the database flavors together. In addition, we’ll help you figure out the right choice for you; in case you want to learn more about some key basic fundamental query building structure– CRUD Operations.

sql database code
SQL code

Two Types: SQL vs NoSQL

There are two main types of databases: SQL and NoSQL. Before we begin, it’s important to know the differences between the two.

While SQL is a relational database that employs a table-based structure, NoSQL is a non-relational database that employs a variety of data models for accessing, retrieving and modifying database. Moreover, SQL has a predefined database schema, whereas NoSQL has a dynamically defined schema.

Additionally, the most popular flavors are mainly based on SQL rather than NoSQL— only MongoDB makes the cut in the top 5 databases in 2021—with other examples like Elastisearch, DynamoDB not used as often by developers. MongoDB is so popular because of the flexibility in offers in database design and the high performance it delivers.

The Most Popular SQL Database Flavors in 2021:

  • MySQL
  • PostgreSQL
  • Microsoft SQL Server
  • SQLite
  • MongoDB

Moreover, the Stack Overflow Developer Survey 2020 has placed MySQL as the most popular database:

sql database flavors popularity rankings 2021
Stack Overflow
FeaturesMySQLPostgreSQLMS SQLSQLite
SELECT QuerySELECT col1, col2…SELECT col1, col2…SELECT [col1], [col2]…SELECT col1,col2…
Current Date FunctionSELECT CURRENT_DATE();SELECT CURRENT_DATE;SELECT GETDATE()’;SELECT date(‘now’);
Case Sensitive DataNo; WHERE city = ‘London’ and WHERE city = ‘london’ are the sameYes. WHERE city = ‘London’ and WHERE city = ‘london are differentYes. WHERE city = ‘London’ and WHERE city = ‘london are differentYes. WHERE city = ‘London’ and WHERE city = ‘london are different
Column AliasSELECT col1 AS COLSELECT col1 AS COLSELECT col1= COLSELECT col1 AS COL
Natural JoinNoYesNoYes
ORDER ByAllows ORDER BY; NULLs are considered lower than non-NULL valuesAllows ORDER BY; NULLs are considered higher than non-NULL valuesAllows ORDER BY; NULLs are considered lower than non-NULL valuesAllows ORDER BY; NULLs are considered lower than non-NULL values
Inserting Multiple RowsYesYesYesYes
BOOLEAN Data TypeNo; uses TinyInt(1)YesNo; uses BIT(1)No; uses Int
Timestamp FunctionYesYesNoNo
Concatenationconcat(String 1, String 2)String 1 || String 2String 1 + String 2String 1 || String 2
List of DatabasesSHOW DATABASESSELECT datnameFROM pg_catalog.pg_databaseEXEC SP_HELPDB.databases
Dummy TablesNoNoNoNo
Window FunctionsYesYesYesNo; subqueries used.
Auto IncrementAUTO_INCREMENTSERIALIDENTITY()AUTOINCREMENT 
Key features and syntax comparison of SQL databases

MySQL

MySQL was created in 1995 to build on the original SQL, as an open source relational database management system (RDBMS). In addition, its queries are run on the MySQL Command Line Interface (CLI) or on MySQL Workbench.

Key Features & Advantages of MySQL:

In addition, MySQL has gained immense popularity for its efficacy, ease of use, performance and powerful execution and database performance:

  • Extremely fast for read-heavy workload
  • Capable of replication
  • Cloud-scalable database

Subsequently, let’s look at some advantages below:

  • Easy to use
  • Low memory leakage; memory efficient
  • Runs on all OS: Windows, Mac OS, Linux, etc.
  • High performance

Disadvantages of MySQL:

MySQL is one of the most used databases around the world. Still, it has some disadvantages you want to keep in mind. Let’s look at some below:

  • It does not handle transaction statements effectively
  • Lack of debugger tools

PostgreSQL

postgresql release years
Photo by Alibaba-cloud

PostgreSQL, or commonly known as Postgres, is also an open source object-based relational database management system (RDBMS) initially released in 1996. Moreover, its queries are natively run on pgAdmin, its client. In addition, PostgreSQL is a great database language for beginners because of its broad applicability.

Key Features & Advantages of PostgreSQL:

PostgreSQL focuses on SQL compliance; in other words, it is growing into one of the most popular flavor of SQL in recent years:

  • Client-server model architecture
  • Offers geo-location queries
  • Data integrity
  • Support for JSON data types

After that, let’s take a look at some advantages now:

  • Larger data storage for databases
  • Multi-version concurrency control (MVCC) without read locks
  • Creates views that are updatable
  • Creation of user-defined functions, triggers, data types, etc.
  • Runs on all operating systems: Windows, Mac, Linux, etc.
  • Robust and powerful
  • Huge community support
  • Compatible with many programming languages like C, Python, etc.

Disadvantages of PostgreSQL:

Despite all these advantages and features, Postgres, like any other language, also has its own disadvantages. Therefore, let’s take a look at some below:

  • PostgreSQL struggles in performance factors
  • It is responsible for more memory utilization of computer
  • Data replication is hard

Microsoft SQL Server

Microsoft SQL Server, or MS SQL, is a relational database management system developed by Microsoft in May 1989. Similarly, its queries are natively run on SQL Service Management System (SSMS). In addition, SQL Server is an important part of the Microsoft Data and Business Intelligence platforms.

Key Features & Advantages of MS SQL:

MS SQL is a language that’s growing in userbase with its widespread use. MS SQL is used by companies like Dell, Yahoo, etc. Therefore, let’s look at some key features:

  • Data compression
  • MS SQL is highly secure
  • Its installation process is streamlined
  • It offers different versions to suit personalized needs

Consequently, MS SQL is one of the most popular database languages today, so it makes sense to see some of its key advantages:

  • Free for developers
  • Useful online documentation
  • Debugging tools on SMSS
  • Great community support
  • Highly secure
  • Easy installation with automatic upgrades
  • Easy maintenance

Disadvantages of MS SQL:

In the end, there is no database language that is perfect. Therefore, let’s explore some disadvantages of MS SQL. In other words, like any other database, MS SQL also has negative factors that may affect your choice:

  • Performance tuning is hard
  • There can be a bit of learning curve
  • May be overwhelmed with the huge number of features offered alongside

SQLite

SQLite is a RDBMS built in C library with its primary use being in mobile applications. Unlike other databases. it is not based on client-server architecture, but rather makes use of the embedded architecture.

Key Features & Advantages of SQLite:

SQLite is gaining popularity for its features, customer service and quality. Let’s see its key features in the list below:

  • Configuration doesn’t exist– no installation required
  • Has an easy to use API

SQLite has a lot of advantages that we will look at now. Let’s see what these benefits are below:

  • Runs on multiple OS: Windows, Linux, Mac OS etc.
  • Installation not required
  • Server-less database
  • Flexible and fast database
  • Uses less amount of memory on runtime

Disadvantages of SQLite:

In the end, SQLite has the following disadvantages that we will look at now:

  • Performance compromised because of file system configuration
  • Write operations are recorded
  • Database file size is smaller (15MB)

MongoDB

MongoDB is a document-based database, unlike all the others on this list. As a result, it uses JSON-documents for storing data instead of tables. In addition, you can start writing query from its official editor available at this link.

Key Features & Advantages of MongoDB:

MongoDB is the most popular non-relational database because of these features:

  • Data stored in JSON-documents
  • Indexing used for traversal
  • Horizontally scalable database

In addition, the following are its advantages:

  • Flexible database schema, dynamically defined
  • High speed of access time
  • Easy to setup environment

Disadvantages of MongoDB:

After that, let’s look at some disadvantages:

  • JOINS not allowed
  • Data size is limited in MongoDB

The Right SQL Database Flavor For You Is…

sql database graph question ranknig
A graph from Datacamp

In fairness, it’s difficult to choose one database generically, since each database serves a goal or purpose.

SELECT e.emp_name, AVG(s. salary) AS average_salary
FROM employees AS e
LEFT JOIN salary AS s
ON e.emp_id = s.empid
WHERE e.city = 'USA'
GROUP BY e.emp_name
ORDER BY s.salary;

For instance, this code example above shows that the syntax is actually quite similar. However, it can be seen because the query output is same for MySQL, PostgreSQL and SQLite, there is not much syntactical differences.

However, Microsoft SQL Server is the one that consists of the most differences between all of them.

In other words, the right database for me may not necessarily fit or be the right one for you. As a result, each database is optimized for a specific purpose or need. Therefore, how do you know which database fits for you?

Above all, which database can you learn or base your backend in?
Then, let’s explore some key factors in helping you determine the right SQL database flavor for your needs.

Speed Of Databases:

  • MySQL is fast for concurrent activities
  • PostgreSQL is quite fast
  • Microsoft SQL Server is slower
  • SQLite is fast but not the fastest as compared to others
  • MongoDB is extremely fast for data retrieval since it uses indices to traverse

Performance Of Databases:

  • MySQL has limited performance optimization
  • PostgreSQL is responsible for high performance database
  • Microsoft SQL Server offers high performance and data compression
  • SQLite runs smoothly
  • MongoDB has a great performance because it relies on jsons

Database Sizes Of Databases:

  • MySQL supports limited size database in some scenarios.
  • PostgreSQL supports large database
  • Microsoft SQL Server supports large databases
  • SQLite supports big databases, but with limited file size
  • MongoDB supports large databases

Eventually, you need to ask yourself what factors matter most in your needs before you can aptly choose.

Which Database Should You Choose?

If you’re looking for a fast, high performance database that will be easy to use, MySQL may be the right choice. But, if you’re working on a much larger database or complex queries— PostgreSQL may be the right choice for you.

On the other hand, if you are working with highly sensitive data, then the security, integrity and sturdiness of Microsoft SQL Server may be the perfect choice for your needs. Similarly, if you want fast, portable, and reliable database that runs on multiple operating systems, SQLite may be great.

Furthermore, if your business and data requirements are evolving over time and you need to scale accordingly, the document-based database, MongoDB could be a wonderful selection.

Conclusion to Comparison of SQL Flavors:

SQL consists of multiple flavors of databases used around the world with varying popularity. In addition, these flavors of databases carry plenty of similar traits and syntactically are akin.

Similarly, we proceeded to look at the top 5 databases in the world today individually and then compared the results together; it’s hard to pick one generically since each database serves a goal or purpose.

In conclusion, I hope you will find this post useful. Please share your thoughts in the comments below. Also, like the post if you learned something new here. Subscribe to our blog. Stay updated on all related content. Lastly, let us know your thoughts about this post in the comments. Thanks for reading. Stay safe and stay healthy.

Hits: 13

Categories
Cloud Entrepreneurship NiHao Cloud Documentation Products and services Sotfware & Developers & DevOps Storage Tools & How-Tos

Active Directory AD and OpenLDAP for Security and SSO

You might be please to know that the Sesame Disk by Nihao Cloud storage now supports AD and OpenLDAP for Security and SSO. The idea is for you to be able to bring your own LDAP users from your server and thus secure your accounts using the same credentials as the ones people regularly use for day to day work (SSO) on their computers at the office, etc. Let’s explain how the system works.

Allow me to stat by saying that the system we use is very mature and has been working for many years now. We can not complain about the customers we have and the growth we have experienced along these year. From the start we have been about providing Security, convenience and reliability. Well for that purpose is that we are releasing this new feature.

NiHao cloud Storage Logo

LDAP the who?

Just for your own benefit LDAP/ OpenLDAP and Windows Active Directory are the same family of Tree like Database system Protocol and tools. They help keep information stored in the form of a hierarchical tree. It can be used for many purposes, but storing organizations users databases and directories are one of the main usages they have. The more popular forms of this software are Windows Active Directory (AD) made by Microsoft and OpenLDAP which as defined on the official website:

“OpenLDAP Software is an Open Source suite of directory software developed by the Internet community.

OpenLDAP Software is based upon prior work by the University of Michigan.”

Taken from: https://www.openldap.org/software/ on July 2021.

Finally on this regard, just to mention how popular it actually is in the market. Nowadays is used by millions of organizations and billions of users if you count the people who do not know they are using it. For instance: Aside from the windows implementations there are various software that use it and/or integrate with it; Zentyal, Samba, Exim, postfix, MySQL and many more. Those are just a small list of packages or technologies that integrate or are able to work with LDAP. Aside you can develop extensions using LDAP in almost every major programming language out there; Python, PHP, C#, Golang, java, etc.

Why would you use this feature?

If you happen to have existing users in your own Windows Active Directory or OpenLDAP running on Linux/unix systems. Well now you can bring those same users as they are and use then to access your files Cloud Storage. By the way The LDAP SSO integration works with all multi user plans and storage sizes in Sesame Disk. Meaning from a few GB to petabytes unlimited of Cloud Storage. No mater if it is for two users to thousands of users.

The system can be used via LDAP from anywhere in the world. Including Chinese Cloud Storage Users as well as of course any where else in the world.

Products supporting AD and OpenLDAP for Security and SSO

While doing all this you can also benefit for all of the standard features. For instance the Seafile Client for synchronization of files. Second the new generation of Pay as you go products Ondemand. Also check our you can find details about products in this link.

How to get AD and OpenLDAP for Security and SSO working?

To start you need to singup for one of the paid plans to be able to use the feature. once this is done you when you go to the billing dashboard, you will see one additional button for LDAP as per the following image.

Billing dashboard with LDAP SSO integration.
Billing dashboard with LDAP SSO integration.

Here you Click on the button External LDAP Users (SSO). Then you will see a New Window like the following image.

LDAP users dashboard
LDAP dashboard for sync

At this point another pop window will open. If this is your first time opening the LDAP connect you will also see the help message with a light blue background. as per the image bellow.

AD and OpenLDAP for Security and SSO Integration.
Help for Active Directory AD and OpenLDAP for Security and SSO Integration.

Here you can close the help pop up window, and it will not be displayed by default anymore. Now, let’s go over that help message as it has important instruction for the integration to work.

Help for Active Directory AD and OpenLDAP for Security and SSO Integration.

For the system to be able to integrate with your LDAP or Active Directory to give your users SSO on our system. You need to make sure our system is able to read your LDAP/AD Controller. Before you start, please make sure you complete these steps:

First buy the users.

Make sure you have purchased enough users capacity in our system to integrate the users you need. Meaning that if you need to have 10 users with storage, by as many. Now, with that said you can always start small and add more as you need. Keep present that our teas system will apply here for the pricing.

Second give us access

Give our IPs (servers) access to the server and/or relevant firewalls in your network. This servers need to be able to reach the LDAP servers via TCP. We have various ways to secure the communications over internet like SLL/TLS encryption. That should be more than enough for most organizations. If you have higher security requirements, we can also create VPN connections to the network of our VIP customers. Your security is very important to use, therefore on this regards we are very flexible.

Third create a user in your own LDAP.

Create a user in your LDAP/DC with access to read the users you intend to integrate with our system. This is the user that our system’s LDAP reader needs to read and keep your users updated. It does not need to be admin of you directory, just having read of ID, name(s), email and password should suffice in most cases.

Fourth and final configure and test.

Fill up the config form and make sure you press “Test connection”. The system will tell you if it can connect to your DB or not. Lastly in case of error will try to tell you why it failed.

After this Four steps you should be good to go!

Here is the form to configure with the above parameters:

LDAP connect config form.
LDAP connect config form.

Once the test is successful, you should be good to go and then you should be able to import your own user. You can click on the “Back” Button and proceed to synchronize users. There you will be given options to chose from all the users that your LDAP user has access to read. there you will have options to manage what users to integrate, etc.

If you find any issues reach-out to support and we will get you sorted.

Hits: 10