How to use AI for database schema generation?

Learn

Designing a database for an efficient backend solution is challenging. Figuring out all the required classes, relationships, and so on can be extremely time-consuming.

Moreover, making wrong decisions in the design phase can have detrimental consequences later in production.

In this article, we will discuss AI usage for database schema generation. We’ll look at the benefits of using AI and its pitfalls and provide a few practical examples.

On top of that, you’ll learn how to utilize AI further to generate a fully-fledged backend without writing any code!

Benefits of using AI for database design

Let’s first look at the benefits of using AI.

Automation & Cost Effectiveness

The first and probably the most apparent advantage of incorporating AI into database design is the ability to automate various tasks.

That includes generating the database schema, database optimization, setting indices, writing advanced queries, etc.

All of the mentioned above results in significant time and cost savings.

Optimization

AI plays a crucial role in optimizing database structures. Through advanced algorithms and machine learning, AI can analyze data usage patterns and recommend enhancements to the database schema.

This optimization can lead to improved performance and responsiveness of your application.

Integration with Existing Databases

Another great perk of using AI is that it can help you upgrade your existing databases.

All you have to do is describe the current database to the AI agent or generate a database dump and then describe the desired upgrades.

This becomes even easier if you’re using Back4app Agent. Instead of generating a database dump, you can tell the agent to hook into an existing app:

Connect to my "movie-reviews" app and create a new database class named `Watchlist`.

The AI agent will gather all the required context and upgrade your database accordingly.

Less Error Prone

Integrating AI into database design reduces the likelihood of human errors.

Traditional database design and optimization processes often involve manual intervention, increasing the risk of mistakes such as incorrect schema designs, inefficient queries, or suboptimal index choices.

Easy Visualization

By leveraging AI, you can easily visualize your database structure. To visualize the database prompt the AI agent to generate visualization code for your favorite ER diagramming tool.

Here’s an example for Mermaid:

Generate Mermaid ER diagram visualization code for my database.

Result:

erDiagram
    USER ||--o{ REVIEW : writes
    MOVIE ||--o{ REVIEW : has
    MOVIE ||--o{ MOVIEGENRE : has

    USER {
        username String
        email String
        emailVerified Boolean
        displayName String
    }

    MOVIE {
        title String
        releaseDate Date
        duration Number
        director String
        cast Array
        synopsis String
    }

    // ...

Pitfalls of using AI for database design

Hallucinations

AI agents sometimes provide plausible-sounding but incorrect or nonsensical information. This is known as hallucinations, and it happens with most large language models (LLMs).

Due to this, developers new to databases shouldn’t use AI for database schema generation.

Performance

AI-generated database schema is not guaranteed to be optimal. The AI agent might misinterpret what you’re trying to build and provide you with a suboptimal or flawed database schema.

Database design using AI is an iterative process. If you notice that the AI agent made a mistake, point it out. It might be able to fix it or improve the schema.

Edge Cases

Using AI might not be the best idea if your business requires an atypical database design. Most AI agents were trained on large number of generic applications.

They have no knowledge of how to go about your specialized database.

If that’s the case, you’re better off relying on experts than general AI solutions.

How to use AI to generate a database schema?

In this article section, we’ll look at three practical examples of using AI to generate a database schema. The examples include a simple blog app, an e-commerce site, and a movie review site.

After that, we’ll look at some of the other things Back4app AI agent is capable of.

The following steps will require you to have a Back4app account. If you don’t have one yet, register for free.

Objectives

Generate a database schema
Create a database using the schema
Analyze the auto-generated RESTful and GraphQL API
Review auto-generated documentation
Utilize Cloud Code functions

What is Back4app Agent?

Back4app Agent is an AI-powered agent that allows you to perform cloud-related tasks with the power of conversation.

That includes creating apps, deploying apps, designing the database, and writing Dockerfiles or front-end code.

By leveraging Back4app Agent, you’ll have your own personal DevOps assistant on your side 24/7. The great thing about it is that it can learn on the fly and improve as you go.

The agent is tightly integrated with Back4app and Back4app Containers. The agent is a must-have if you’re already using any of Back4app’s services!

Keep in mind that AI agent isn’t a magical tool. It’s a large, sophisticated language model (LLM) that might make mistakes. If that happens, it’ll be up to you to fix them. Additionally, submitting the same prompts (as me) might yield different responses.

Case 1: Blog Website

As the first example, we’ll prompt the AI agent to design a blog database schema. With this example, we’ll provide as little information as possible to see if the AI agent can puzzle out the missing pieces.

Navigate to the Back4app Agent page, create a new agent, and prompt it with the following:

Create a database schema for a simple blog. Each article in the blog should have a `title`, `shortContent`, `content`, `tags`, and other administrative data. The articles should be filterable by `tags`.

Entity-relationship (ER) diagram of the generated schema:

As you can see, the agent successfully created a valid database schema. It included all the required classes and handled the relationships between them. Additionally, it included Parse administrative fields such as createdAt, updatedAt, and ACL.

Case 2: E-commerce Website

Moving along, let’s try a more complex database schema. This time, an e-commerce website.

Submit the following prompt to the AI agent:

Create a database schema for an e-commerce website. The database should allow sellers 
to post listings. After a listing is posted, a user can open an order for it. The 
order should be associated with the user's shipping address. Amazon is a good example 
of what I'm looking for.

The agent generated the following database schema:

Again, the agent returned a valid database schema.

The only thing I’d change here is to flip the Order to Address relationship. It would make more sense for an order to be associated with a single address and an address reusable across orders.

Case 3: Review Website

For the last example, we’ll generate a movie review database schema. Here, we’ll provide as much context as possible to see if the AI agent can create a schema that matches our detailed instructions.

Prompt the AI agent with the following:

Create a database schema for a movie review website. The database should contain 
the following models: `Movie`, `MovieGenre`, `Review`,  and `User` (Parse).

Notes:
- A `Movie` can have multiple genres
- The `Review` should contain at least `title`, `content`, `rating`, and `is_critic`
- Each `Review` should be associated with a `User`

Make sure to include all the Parse administrative fields, such as `createdAt`,
`updatedAt`, and `ACL`.

The AI Agent generated the following database schema:

Another great schema.

All the classes were taken care of, and the relationships look good. Here, the AI agent even generated an intermediary model that handles the M:N relationship between Movie and Genre class.

Database Generation

As the introduction mentions, Back4app Agent is tightly integrated with other Back4app services.

Generating the database schema is just one of the things the AI agent can do. Once we have the schema, we can quickly create a database.

Prompt the agent with the following:

Create a Back4app app called "back4app-reviews" using the generated database schema.

Back4app AI Agent Create Database Response

Great, it looks like the agent successfully created an app and the desired database structure.

Next, ask the agent to populate the database with some data:

Populate the database with sample users, movie genres, and reviews.

Ensure the database has been created and populated by navigating to the Back4app dashboard, selecting your app, and checking the database entries.

Back4app Generated and Populated Database

That’s it!

We now have a fully functional database for a movie review site.

Automatically-generated APIs

Back4app allows you to communicate with your backend via:

Auto-generated RESTful API
Auto-generated GraphQL API
Parse SDK

Let’s examine each of them.

RESTful API

REST is based on the HTTP protocol and utilizes different HTTP methods such as GET, POST, PUT, and DELETE to manipulate the resources.

These operations are frequently called CRUD (Create, Retrieve, Update, Delete). REST supports multiple data formats, but its preferred format is JSON.

To test the REST API, navigate to your app and select “API > Console > REST” on the sidebar.

Fill out the form with the following information:

Request type GET
Endpoint: classes/Movie
Master key: True

Next, click “Send Query” to execute the query.

You should get a response similar to this one:

{
    "results": [
        {
            "objectId": "AD1r4b9Oie",
            "title": "The Fast Saga",
            "duration": 120,
            "director": "Justin Lin",
            "cast": [
                "Vin Diesel",
                "Michelle Rodriguez"
            ],
            "synopsis": "Street racing, heists, and espionage.",
            "createdAt": "2024-02-03T21:53:54.724Z",
            "updatedAt": "2024-02-03T21:53:54.724Z",
        },
        {
            "objectId": "Sr6FQjnckj",
            "title": "The Serious Case",
            "duration": 140,
            "director": "Christopher Nolan",
            "cast": [
                "Christian Bale",
                "Michael Caine"
            ],
            "synopsis": "An intricate story of human emotions and relationships",
            "createdAt": "2024-02-03T21:53:54.724Z",
            "updatedAt": "2024-02-03T21:53:54.724Z",
        },
        // ...
    ]
}

The list operation works well. The movies were successfully serialized and returned as JSON. To get a feel for how the RESTful API works, I suggest you test the other operations as well.

To learn more about RESTful APIs, check out How to build a RESTful API?

GraphQL API

GraphQL serves as both a query language and a server-side runtime to facilitate the development of application programming interfaces (APIs).

This technology empowers clients to specify the data they require from the API, eliminating the need to depend on the backend for a predefined data set.

To test GraphQL queries, navigate to “API > Console > GraphQL” on the sidebar.

Next, run the following GraphQL query:

{
  reviews {
    count
    edges {
      node {
        objectId
        rating
      }
    }
  }
}

As you can see, the query fetched all the reviews but only included their objectId and rating. Again, feel free to test different queries and manipulations.

To learn more about GraphQL, check out How to build a GraphQL API?

Parse SDK

Parse SDK is the recommended way of connecting your frontend with the backend. It is the most robust and least error-prone.

Parse SDK supports multiple programming languages and frameworks, including JavaScript, TypeScript, Objective-C, etc.

It allows you to perform CRUD operations, advanced querying, and more.

To learn how to utilize Parse SDK, check out How to host frontend and backend?

Automatically-generated Documentation

Another great thing about Back4app is that it auto-generates documentation for all your database models.

You get simple, easy-to-understand text explanations and handy code snippets for various programming languages, such as JavaScript, TypeScript, Objective-C, Swift, and more.

To access the docs, navigate to your app and select “API > API Reference” on the sidebar:

Here’s a screenshot of how the docs look like:

Cloud Code

Back4app lets you execute custom JavaScript code through so-called Cloud Code functions.

These functions can help perform complex operations, such as data aggregation, export, etc.

A Cloud Code function can be triggered by Parse or HTTP requests or scheduled to run in the future.

Cloud Code Function

Suppose we want a Cloud Code function that calculates a movie’s average rating. We could write and deploy it ourselves, but it’s easier to ask the AI agent to do it.

Navigate to the AI Agent screen and prompt it with the following:

Please write me a Cloud Code function that calculates a movie's average rating. The the function should take in the movie's `objectId` and return a float (average rating).

Go ahead and test it using the following cURL command:

$ curl -X POST \
   -H "X-Parse-Application-Id: <your_app_id>" \
   -H "X-Parse-REST-API-Key: <your_rest_api_key>" \
   -H "Content-Type: application/json" \
   -d '{"movieId": "<movie_object_id>"}' \
   https://parseapi.back4app.com/functions/calculateAverageRating

Make sure to replace the placeholders (<your_app_id>, <your_rest_api_key>, and <movie_object_id>) with the actual values. To get your “Application ID” and “REST API Key”, navigate to your app and select “App Settings > Security & Keys” on the sidebar.

You should get a similar response:

{
  "result": 4.25
}

To see the code generated in the background, navigate to “Cloud Code > Function & Web Hosting” on the sidebar. Then select main.js within the cloud folder.

Cloud Code Scheduling

Lastly, prompt the AI agent to schedule a weekly job:

Create and deploy a Cloud Code job named `clearRatings()`, which deletes all 
the ratings. Schedule it to run every week.

The agent has successfully created a job and scheduled it on a weekly basis.

Conclusion

In summary, we’ve successfully utilized AI to generate a database schema.

We’ve also learned how to create a Back4app app and database, generate sample data, and implement custom Cloud Code functions, all with the power of conversation.

Remember that creating a database schema using AI is an iterative process. If you think something could be implemented better, explain it to the agent, and it’ll modify your schema accordingly.

Additional article resources are accessible on back4app-ai-agent-schema repo.