API Database Integration

Relational Databases

Relational databases have been the backbone of data storage for many years. They store data in a structured format, using tables with rows and columns. Each row represents a unique record, and each column represents a field of that record. This structure allows for efficient storage and retrieval of data and supports complex operations, like joining data from multiple tables.

Relational databases use Structured Query Language (SQL) to interact with the data. SQL is a powerful language that can perform a variety of operations, including creating, reading, updating, and deleting records (known as CRUD operations).

Because CRUD operations are so common, many web frameworks provide an Object Relational Mapping (ORM) layer that abstracts the database interactions. This allows developers to interact with the database using objects and methods, rather than writing SQL queries directly.

This course will not go into the details of relational databases. We'll leave that for your database courses. Instead, we'll focus on how to use relational databases in your applications.

Serverless Relational Databases

Serverless databases are a relatively new concept. They are fully managed databases that scale automatically and charge based on usage. This means you don't have to worry about provisioning servers, configuring backups, or scaling your database. You simply create a database and start using it. Some common serverless relational databases include:

• PlanetScale (opens in a new tab)
• Supabase (opens in a new tab)
• Vercel Postgres (opens in a new tab)
• Microsoft Azure SQL (opens in a new tab)
• Google Cloud SQL (opens in a new tab)

PlanetScale MySQL

PlanetScale MySQL is a serverless relational database that is fully managed by PlanetScale. It is based on MySQL, which is one of the most popular open source relational databases. PlanetScale MySQL is a good choice for students because it is free to use for up to 1B row reads/month, 10M row writes/month, and 5 GB of storage. It also supports unlimited tables, unlimited queries, and unlimited users. (Vercel Postgres is also free but it very limited.)

Getting Started with PlanetScale MySQL

Use this quickstart guide to get started with PlanetScale MySQL.

https://planetscale.com/docs/tutorials/planetscale-quick-start-guide (opens in a new tab)

Using SQL with PlanetScale MySQL

PlanetScale MySQL uses the MySQL protocol, so you can use any MySQL client to connect to it.

The first step is setting up the .env.local file with the database credentials. You can find these credentials in the PlanetScale dashboard.

DATABASE_HOST=aws.connect.psdb.cloud
DATABASE_USERNAME=xxxxxxxxxxxxxxxx
DATABASE_PASSWORD=************

Next, you'll need to install the PlanetScale database library. This library provides a wrapper around the MySQL client that handles the connection and authentication for you.

npm install @planetscale/database

Finally, you can use the library to connect to the database and run queries.

import { connect } from "@planetscale/database";
 
const config = {
  host: process.env.DATABASE_HOST,
  username: process.env.DATABASE_USERNAME,
  password: process.env.DATABASE_PASSWORD,
};
 
async function getUsers() {
  const conn = connect(config);
  const { rows } = await conn.execute("SELECT * FROM users;");
  return rows;
}

In the example above, we're using the @planetscale/database library to connect to the database and run a query. The connect function takes a configuration object with the database credentials. It returns a connection object that can be used to run queries.

The execute method takes a SQL query and returns an object with the results. The results object contains information about the query, including the number of rows affected, the insert ID, and the time it took to run the query. It also contains the rows returned by the query.

{
  "headers": ["id", "email", "name", "age", "role"],
  "types": {
    "id": "INT32",
    "email": "VARCHAR",
    "name": "VARCHAR",
    "age": "INT32",
    "role": "VARCHAR"
  },
  "fields": [
    {
      "name": "id",
      "type": "INT32",
      "table": "users",
      "orgTable": "users",
      "database": "webdev2-demo",
      "orgName": "id",
      "columnLength": 11,
      "charset": 63,
      "flags": 49667
    },
    ... more fields ...
  ],
  "rows": [
    {
      "id": 1,
      "email": "cprg352+abe@gmail.com",
      "name": "Abe",
      "age": 20,
      "role": "admin"
    }
  ],
  "rowsAffected": 0,
  "insertId": "0",
  "size": 1,
  "statement": "SELECT * FROM users;",
  "time": 4.839211000000001
}

📖 Further Reading

• PlanetScale (opens in a new tab)