Right approach to testing code that makes calls to the database

Given I have a code that generates a query and then uses the database, how can I test it? (I believe the test I need to make here isn't unit, but integration testing, I'm not sure of the naming).

Real-world example, In the software I'm developing, there's the following class which manages a CRUD for blog articles:

<?php
class Article
{
    private $mysql;
    public function __construct(mysqli $mysql)
    {
        $this->mysql = $mysql;
    }
    public function add(string $title, string $content): void
    {
        $insertArticle = $this->mysql->prepare('INSERT INTO articles (title, content) VALUES(?,?);');
        $insertArticle->bind_param('ss', $title, $content);
        $insertArticle->execute();
    }
    public function remove(string $id): void
    {
        $removeArticle = $this->mysql->prepare('DELETE FROM articles WHERE id = ?');
        $removeArticle->bind_param('s', $id);
        $removeArticle->execute();
    }
    public function showAll(): array
    {
        $result = $this->mysql->query('SELECT id, title, content FROM articles');
        $articles = $result->fetch_all(MYSQLI_ASSOC);
        return $artigos;
    }
    public function findById(string $id): array
    {
        $selectArticle = $this->mysql->prepare("SELECT id, title, content FROM articles WHERE id = ?");
        $selectArticle->bind_param('s', $id);
        $selectArticle->execute();
        $article = $selectArticle->get_result()->fetch_assoc();
        return $article;
    }
    public function edit(string $id, string $title, string $content): void
    {
        $editArticle = $this->mysql->prepare('UPDATE articles SET title = ?, content = ? WHERE id = ?');
        $editArticle->bind_param('sss', $title, $content, $id);
        $editArticle->execute();
    }
}

But I don't have any clue on how can I test this code, for example, I believe I can write the following test:

public function testArticleInsertion()
{
    $this->add('Test Title', 'Test Content');

    //verify if the article was inserted into the database;
    $verifyInsertArticle = $this->mysql->query('SELECT * FROM articles WHERE title = "Test Title"');
    $article = $verifyInsertArticle->fetch_all(MYSQLI_ASSOC);
    $this->assertEquals('Test Title',$article['title']);
    $this->assertEquals('Test Content',$article['content']);
}

But I'm not sure that this is a reasonable approach, I've saw people stubbing the 'add' function to see if they were called with the right parameters only, but this sound like I'm only testing the surface and doesn't sound like the right approach either.

I know that I shouldn't test with production data and I need a testing database, for this I use a factory for creating my database connection object, that way I can create a method for it to return my testing database instance too, so no problems there.

What is the approach I should be using when I encounter these kind of code?