March 2023 Summaries

The PlanetScale team has released four official GitHub Actions that can be used to automate common operations on their databases. These actions allow developers to create a branch of a database, create a password for the new branch, create a deploy request between two branches, and attempt to merge changes defined in an existing deploy request. The actions are available on the GitHub Actions Marketplace and can be easily integrated into developer workflows. By using these actions, developers can automate tasks such as creating new database branches when a feature branch is created or merging database schema changes from one branch to another when a pull request closes.

PlanetScale and Netlify have collaborated to demonstrate the integration of PlanetScale with Netlify Functions, focusing on typical Software as a Service (SaaS) application scenarios. This collaboration aims to provide developers with an efficient solution for building SaaS applications using both platforms' features.

The text discusses the use of EXPLAIN, a keyword in MySQL used to gain information about query execution. It explains how to utilize EXPLAIN to remedy problematic queries and provides an overview of its features, including SELECT_TYPE, table on which the query was running, partitions accessed by the query, types of JOINs used, indexes from which MySQL could choose, and actual costs of individual iterators in the execution plan. The text also introduces EXPLAIN ANALYZE, a new concept built on top of regular EXPLAIN, which provides additional information such as estimated execution cost, estimated number of returned rows, time to return first row, and time spent executing each iterator. It highlights when to use MySQL EXPLAIN or EXPLAIN ANALYZE, including using it for query optimizations, designing indexes, and identifying potential performance issues. The text also covers the limitations of EXPLAIN, such as not providing information about triggers, stored procedures, or UDFs, and discusses the EXTRA column in EXPLAIN output, which contains extra information that doesn't fit into other columns. Finally, it provides a hands-on example of how to use MySQL EXPLAIN for query optimizations using a multi-column index.

Connection pooling is a technique used to manage database connections efficiently by creating a cache of connections that can be reused as needed. This approach reduces the overhead of establishing new connections, improves performance, and enhances scalability by reusing existing connections. However, it has limitations in solving all scalability problems and may not inherently scale the database to handle increased load. To address these challenges, YouTube developed Vitess, a connection pooling system that manages connections centrally and eliminates the need for application-level connection pooling. Vitess uses lock-free algorithms and atomic operations to efficiently manage large numbers of concurrent requests. Additionally, MySQL provides settings that can be modified on connections, but this requires careful management to avoid degrading performance. To address this, PlanetScale introduced a "settings pool" feature in Vitess 15, which tracks and manages connections with modified system settings without compromising the benefits of connection pooling.

MySQL 8 was released in 2018, but many MySQL servers are still running MySQL 5.x. Oracle has ended support for MySQL 5.7 and plans to phase out its support by October 2023. Upgrading from MySQL 5.7 to MySQL 8 is recommended due to performance improvements and security enhancements. Before upgrading, it's essential to ensure a backup of the database, as the upgrade process may require downtime or changes to character sets, collations, obsolete data types, authentication mechanisms, reserved words, SQL modes, and other settings. Upgrading with no downtime can be achieved using tools like PlanetScale's import tool, which allows importing a live production database without data loss or downtime. It's crucial to test the database thoroughly after the upgrade to ensure it functions correctly.

The planetscale_rails gem provides a collection of Rake tasks that help manage the schema of a Rails application, allowing developers to ship changes without downtime. The workflow uses PlanetScale's git-like database management system to introduce a new branch for making schema changes, which are then deployed as "deploy requests" to production with no downtime risk. This approach separates schema changes from code changes and automates the process, reducing the risk of errors or downtime. By using this workflow, developers can ensure that their Rails application is more reliable and efficient, while also saving time in the long run.

This summary provides an overview of the key points from the provided text, which covers setting up a PlanetScale MySQL database and building a product listing application with Golang. The tutorial begins by creating a new PlanetScale database and obtaining its connection string. It then sets up a Golang project using the Gin framework and connects to the database using GORM. The code creates models for the products table and defines CRUD operations for the database. Finally, it builds a Gin router to serve API endpoints for creating, reading, updating, and deleting products. The application is served on port 8000, allowing users to test its endpoints via Postman or cURL.

To improve internal process workflow, the Developer Education team at PlanetScale created an integration for GitHub projects to visualize scheduled content on a calendar, utilizing the GitHub GraphQL API and open-source React libraries. The Issues Calendar allows users to view issues tied to GitHub projects as a calendar in the browser, integrating with GitHub's OAuth system for easy sign-in and project import. This tool has been used internally for several months, providing significant value, and is now being released for public use, providing instructions on configuration within its README.

The End of Life (EOL) for MySQL 5.7 is approaching, which means users need to consider upgrading their databases to version 8.0. PlanetScale offers a solution that enables seamless migration without any downtime or data loss.

Nest.js is being used to build an efficient Node.js application, paired with MySQL for the database. This tutorial guides users on how to connect a Nest.js RESTful API to a PlanetScale MySQL database, enabling them to perform CRUD operations such as creating, retrieving, editing, and deleting users. The connection process involves setting up a free Planetscale account, cloning an existing Nest.js project, installing dependencies, and defining the database parameters in a `.env` file. Users can test their API using Postman and experience the safety measures provided by PlanetScale, such as branching workflow and deploy requests, to protect against data loss and ensure database availability.

"DevOps with PlanetScale" offers a comprehensive exploration of how PlanetScale's features integrate into the DevOps cycle, enhancing the database experience through seamless continuous integration and deployment. It breaks down the typical DevOps model into phases—Plan, Code, Build, Test, Release, Deploy, Operate, and Monitor—detailing how PlanetScale aids each stage. The focus is on database schema branching and Data Branching®, enabling isolated environments for development and testing, ensuring production data safety. The PlanetScale CLI and API facilitate automation, while features like Deploy requests and schema revert allow for safe, zero-downtime deployments. The article also discusses PlanetScale's capabilities in backup and restore, horizontal scaling, and insights for performance monitoring. A fictional case of Mechanica Logistics illustrates practical application, highlighting PlanetScale's role in a real-world DevOps workflow to improve efficiency and reliability in deploying new features.

SQLAlchemy is a popular Python library used to interact with SQL databases. It provides tools for sending raw queries, constructing SQL statements, and mapping Python classes to database tables using the object-relational mapper (ORM). The library has flexibility in its approach to working with databases, allowing it to support various approaches and be used in different types of projects. To use SQLAlchemy, you need to set up an engine object, which manages connections to your database. This is typically done by installing a database driver, creating a connection string, and then using the create_engine function to create the engine object. Once the engine object is created, you can start sending raw SQL statements or using the ORM to interact with your database. The ORM allows you to define classes that map to your tables, making it easier to work with your data in Python. With this library, you can perform various operations such as creating and inserting data into tables, querying data, and managing relationships between tables.

The recently released updates improve the experience of navigating through branch information, with production and development branches having their own unique features. The schema page is now visible on a single page, allowing users to easily search for tables and view table sizes in production branches. Development branches display a diff of changes relative to its parent, making it easier to reference while working on deploy requests. These updates are part of an ongoing effort to make branches more configurable for various use cases, with the community encouraged to provide feedback through Twitter or the GitHub discussion board.