Serverless databases are a modern approach to database technology. Unlike traditional databases, where users have to manage server infrastructure and capacity planning, serverless databases remove those complexities. Users can focus solely on their data and applications, leaving the database infrastructure provisioning, scaling, and maintenance to the service provider.
The development of databases has seen a significant shift with the emergence of serverless MySQL. This technology streamlines server management, automates scaling, and integrates seamlessly with cloud platforms, offering cost-effective and reliable database solutions. PlanetScale, a prominent player in this space, provides a managed database service built on MySQL and Vitess, catering to businesses seeking advanced serverless MySQL capabilities at scale.
Contents
- What is serverless MySQL? A new era of database solutions
- Introduction to PlanetScale: Your gateway to scalable and reliable databases
- PlanetScale advantages
- Launching your serverless MySQL: How to set up a PlanetScale database
- Connect to PlanetScale database via GUI tool for MySQL
- Import sample data to serverless database
- MySQL compatibility: PlanetScale limitations
- AWS Aurora vs PlanetScale: Comparative analysis of serverless databases
- Conclusion
What is serverless MySQL? A new era of database solutions
Serverless MySQL is a modern approach to managing and using MySQL databases in a cloud computing environment, particularly in the context of serverless computing. It represents a shift in how traditional relational databases like MySQL are provisioned, scaled, and managed.
Serverless MySQL is like a special cover for the mysql Node.js module. Usually, when people use the mysql module in their Node.js programs, everything runs smoothly. However, issues can arise when dealing with serverless functions like AWS Lambda, Google Cloud Functions, and Azure Functions.
The thing is that these serverless functions can handle multiple users simultaneously, much like having numerous waiters in a restaurant, with each user having a dedicated waiter. While this is generally advantageous, it can become problematic when working with databases like MySQL. It’s like having too many customers in a restaurant, and the waiters can’t keep up with all the orders.
MySQL has a limit on the number of connections it can manage concurrently (think of them as customers in the restaurant). When all these serverless functions create connections, this can quickly exhaust MySQL’s capacity. It’s like having too many people trying to squeeze through a door simultaneously.
This is where serverless MySQL comes to the rescue. It’s like having a super-smart manager in the restaurant who makes sure that all the customers are served properly and that the restaurant never gets too crowded. In essence, serverless MySQL efficiently manages connections, preventing the system from becoming overwhelmed.
Introduction to PlanetScale: Your gateway to scalable and reliable databases
PlanetScale is a MySQL-compatible serverless database platform designed to provide scalable and highly available database solutions for modern applications — without compromising on developer experience. Let us look at PlanetScale in greater detail. Here’s a detailed overview of what it offers to its users.
Vitess-based architecture
At the core of PlanetScale is the Vitess database clustering system. Vitess combines many important MySQL features with the scalability of a NoSQL database.
Horizontal scaling
PlanetScale allows you to horizontally scale your database resources, which means you can add more capacity and handle larger workloads as your application grows. This is crucial for applications with rapidly changing or unpredictable traffic patterns.
High availability
The platform offers built-in high-availability features to ensure that your database remains accessible even in the face of hardware failures or other disruptions. This is achieved mainly through automated replication and failover mechanisms.
Multi-cloud support
PlanetScale is designed to work across multiple cloud providers, including AWS, Google Cloud, and Azure. This rich compatibility provides flexibility and allows you to choose the cloud infrastructure that best suits your needs.
Managed service
With PlanetScale, you don’t need to worry about infrastructure management. It’s a managed service, which means it takes care of tasks like provisioning, scaling, backups, and patching, allowing you to focus on developing your applications.
Developer-friendliness
The platform aims to be developer-friendly, providing tools and interfaces that make it easy for developers to work with their databases. You can typically use standard MySQL tools and libraries to interact with your data.
Security and compliance
Data security is a priority, and PlanetScale typically offers security features like encryption at rest and in transit, access controls, and auditing to help you protect your data and meet compliance requirements.
Scalable pricing
PlanetScale often follows a scalable pricing model, where you pay for the resources you use. This means you can start small and expand as your application’s demands grow without incurring unnecessary costs.
PlanetScale advantages
PlanetScale brings the advantages of serverless computing to the database world, offering limitless scaling, consumption-based pricing, seamless schema migrations without downtime, and a generous free tier.
Additional advantages of PlanetScale include:
- High availability
- Rich cloud support
- Managed service
- High-security features
- Affordable and scalable pricing
- Active community
- Rapid prototyping
- Global reach
Non-blocking schema changes with PlanetScale
PlanetScale allows you to make modifications to your database schema without disrupting ongoing operations or causing downtime. Its architecture and tools are designed to deploy these changes seamlessly, ensuring that your applications can continue running smoothly even as you change your database structure. This feature is especially valuable for businesses that need to evolve their databases to meet changing requirements without impacting user experience or service availability.
Database branches
PlanetScale’s Database branches is an innovative feature that enables developers to work with databases in a manner similar to how software developers use version control systems like Git for code.
Database branches or isolated database instances in PlanetScale provide you with the flexibility needed during the development of your application. When your database is initially set up, a single production branch called main is created, serving as the default branch. However, you can create additional branches if needed.
When you create a new branch, the schema from the source branch is duplicated to the new one. This gives you a separate MySQL instance that you can work with independently. Any changes made within one branch, whether they involve schema modifications or data alterations, do not impact other branches within the same database. This isolation ensures that your development efforts remain separate and do not affect the stability or functionality of other parts of your application.
Monitoring and debugging at your fingertips
With PlanetScale Insights, an in-dashboard query statistics tool, you can seamlessly analyze and debug the performance of individual queries within designated time frames, all without leaving your PlanetScale dashboard. This tool provides a comprehensive view of all queries executed in your database over the last 24 hours. Within the Insights graph, you can effortlessly monitor key metrics such as Rows Read, Rows Written, Query Latency, and Queries per Second.
Launching your serverless MySQL: How to set up a PlanetScale database
You can create and configure a PlanetScale database in just a few easy steps.
Step 1: Create a database with PlanetScale
1.1 Log in to your PlanetScale account.
1.2 Click New database > Create a database on your organization’s start page.
1.3 Select a name for your new database.
1.4 Select a geographic region.
1.5 Select your preferred subscription plan.
1.6 Provide valid credit or debit card details (no charge for the Hobby plan).
1.7 Click Create Database to deploy your database.
Step 2: Set up branches
With PlanetScale, you can branch database schemas in a manner similar to how you branch your code. There are two types of database branches:
- Development branches: These provide isolated copies of your production database schema for experimentation and CI. Keep in mind that development branches do not contain data unless restored from a backup.
- Production branches: These are robust, highly available databases designed for production traffic. They come with an extra replica for outage resilience, allowing seamless failovers. Additionally, production branches offer optional safe migrations to protect against accidental schema changes and enable non-disruptive schema updates.
Create a development branch
2.1 Navigate to the database overview page within your PlanetScale dashboard.
2.2 Click New branch.
2.3 Assign a name to your development branch.
2.4 Choose the region that is geographically closest to either you or your application for optimal performance.
2.5 Specify the production branch from which you want to create this development branch. Alternatively, you can select another development branch.
2.6 Click Create branch.
Change the default branch
When you create a database, the main branch is automatically set as the default branch. However, you can change the default branch if needed.
To change the default branch:
1. From the database overview page, navigate to the Settings tab.
2. Under General, click to open the Default branch dropdown.
3. Select the branch you want to set as the default one.
4. Scroll down and click Save database settings.
Step 3: Configure backups
PlanetScale allows you to create, schedule, and restore backups for production and development database branches.
View backups
To view your backups and backup configurations from the database overview page, navigate to the Backups tab.
Create a backup
To create a manual backup, click Create new backup.
Then, in the dialog that opens, select a branch to backup, provide a name for your backup, and select how long to keep it for.
Finally, click Create backup and close the dialog.
Note
Manual backups are not free; you will be billed for each manual backup.
Schedule backups
You can schedule extra backups for your branches with a charge of $0.023 per GB per month.
To schedule additional backups, on the Backups tab of your database, select the type of branch you want to schedule backups for and click Add new schedule.
In the dialog that opens, specify the backup frequency, select how long you want to keep the backup, and provide a name for the backup.
Finally, click Save schedule to save your configurations and close the dialog.
Connect to PlanetScale database via GUI tool for MySQL
You can establish a connection to your MySQL database hosted on PlanetScale using a GUI tool. In this article, we demonstrate to you how to connect to PlanetScale using dbForge Studio for MySQL, an advanced all-in-one solution for MySQL and MariaDB database development, management, and maintenance.
Get connection details
To begin with, you need to obtain your connection details and create a password to ensure a secure connection.
1. On the Overview tab of your database, click Connect.
2. In the dialog that appears, click New password.
3. Next, select the branch for which you want to create a password, select a password role, and assign a name to the new credentials. Once done, click Create password.
4. After you have created the password, you can browse the connection string in different formats by selecting the required framework in the Connect with dropdown.
We select General and get all the credentials required for establishing a connection from dbForge Studio for MySQL.
Connect to PlanetScale using a GUI tool
1. Launch dbForge Studio for MySQL.
2. Navigate Database > New Connection.
3. In the Database Connection Properties dialog that opens, provide the host name, user name, password, and the name of the database.
4. Optional: Click Test Connection to validate the connection details.
5. Click Connect.
Import sample data to serverless database
There are several ways you can import sample data to your PlanetScale database in dbForge Studio for MySQL:
Let us try to import sample data to our PlanetScale database using the data import utility that comes with dbForge Studio.
For this, first, we will create the actor table. Then, in Database Explorer, right-click this newly created table and select Import Data.
Next, in the Data Import wizard that opens, select the required import format and follow the wizard’s steps to configure the import process. Once done, click Import.
For more detailed information, refer to How to Import CSV Data into MySQL/MariaDB Database.
Let us check whether our import efforts have been successful.
As you can see, our PlanetScale database now contains data as intended.
MySQL compatibility: PlanetScale limitations
PlanetScale databases run on MySQL version 8.0.23. When importing an existing database into PlanetScale, you can use MySQL database versions ranging from 5.7 to 8.0.
PlanetScale creates new databases using the character set utf8mb4_0900_ai_ci by default. The platform also supports utf8, utf8mb4, and utf8mb3 character sets. Additionally, PlanetScale also offers support for latin1 and ascii character sets, although they are not recommended due to potential limitations and compatibility issues.
MySQL compatibility limitations
SQL features and commands:
- Renaming columns and tables using ALTER TABLE…RENAME COLUMN is not supported.
- Creating databases (CREATE DATABASE) and dropping databases (DROP DATABASE) using MySQL command line are not supported, but they are available in the PlanetScale CLI.
- The JSON_TABLE function is not supported.
- Stored routines, including PROCEDURE, FUNCTION, TRIGGER, and EVENT, are not supported.
- Loading data via LOAD DATA INFILE is not supported.
- Killing queries or shards from the command line using KILL is not supported.
- The := assignment operator is not yet supported.
- Modifying the global time zone using SET GLOBAL time_zone is not allowed; it’s set to UTC.
- Changing the global SQL mode permanently using SET GLOBAL sql_mode is not supported; you should set each new session’s mode with SET sql_mode.
- Enabling SQL modes like PIPES_AS_CONCAT and ANSI_QUOTES can interfere with Vitess’ evalengine parsing SQL queries, so they are not recommended.
- In an INSERT… ON DUPLICATE KEY UPDATE statement, aliasing columns or rows is not supported.
Further restrictions:
- Databases with empty schemas are not valid; you cannot deploy a schema change to production if no tables exist.
- Only the InnoDB storage engine is supported; other storage engines are not compatible with PlanetScale.
- All tables must have a unique, non-null key that remains unchanged during migrations.
- Direct DDL (Data Definition Language) on production branches, including TRUNCATE statements, is not allowed.
- Binary logs must be enabled when importing a database using the database importer tool.
- While MySQL supports JSON documents up to 1 GB in size, storing very large JSON documents (more than a few MB) in PlanetScale is not recommended for performance reasons.
AWS Aurora vs PlanetScale: Comparative analysis of serverless databases
Another notable serverless database solution in the market is AWS Aurora Serverless, a cloud-native database service from Amazon Web Services (AWS). It dynamically scales its capacity according to real usage, enabling users to pay solely for the resources they use. AWS Aurora Serverless supports both MySQL and PostgreSQL, offering a cost-effective and adaptable solution suitable for applications with fluctuating workloads and unpredictable traffic patterns.
AWS Aurora and PlanetScale are two serverless database options with distinct features and use cases. Aurora, provided by Amazon Web Services (AWS), is a popular relational database service known for its scalability and compatibility with MySQL and PostgreSQL. PlanetScale, on the other hand, is built on the open-source Vitess project and offers advanced capabilities for scaling and branching databases.
Use cases
AWS Aurora is an excellent choice for traditional web applications, enterprises with MySQL or PostgreSQL workloads, and applications with variable workloads that benefit from Aurora Serverless’ automatic scaling and high availability. In contrast, PlanetScale, built on Vitess, shines in scenarios requiring advanced horizontal scaling, data branching, microservices architectures, and startups or SaaS companies seeking cost-effective and flexible scaling solutions. Its open-source foundation also appeals to open-source enthusiasts and those looking for innovative approaches to database-as-a-service solutions. The choice between AWS Aurora and PlanetScale depends on your specific application needs and scalability requirements.
AWS Aurora vs PlanetScale: Comparative Table
| Feature | AWS Aurora | PlanetScale |
| Database type | Relational | Relational |
| Serverless option | Aurora Serverless | Based on Vitess DB |
| Scaling options | Manual and Auto | Auto |
| Data consistency | Strong | Strong |
| SQL dialects supported | MySQL, PostgreSQL | MySQL |
| Multi-region support | Yes | Yes |
| Backup and restore | Automated | Automated+Manual |
| Data branching (Git-like) | N/A | Supported |
| Automatic failover | Yes | Yes |
| Pricing model | Pay-as-you-go | Pay-as-you-go |
| Performance scaling | Vertical Scaling | Horizontal Scaling (Vitess) |
| Replication | Aurora Replicas | PlanetScale Replicas |
| Community support | Widespread | Growing |
Conclusion
The future of serverless MySQL is promising as there is a growing demand for flexible, cost-effective database solutions that can scale seamlessly. As its adoption continues to rise, PlanetScale’s unique offerings position it as a key player in shaping the landscape of serverless MySQL databases.
In such a dynamic landscape, dbForge Studio stands out as an essential tool for modern developers aiming to stay at the forefront of the rapidly evolving software development industry. Its broad connectivity options, encompassing conventional MySQL and MariaDB servers, cloud-based servers, and now serverless MySQL, make it a versatile choice. With its rich feature set and compatibility with various database types, dbForge Studio empowers developers to streamline database management, maintenance, and innovation across a wide array of database environments.
To discover the advantages of dbForge Studio, don’t hesitate to download a 30-day free trial today and enhance your database development and management workflows.
