What tools have you used for working with databases?

For working with databases, I used the following tools:

• SQL clients:
  • DBeaver
  • SQL Developer
  • pgAdmin
• Command line tools:
  • psql
  • mysql
• Frameworks and libraries for test automation:
  • JDBC (for Java)
  • psycopg2 (for Python, PostgreSQL)
  • mysql-connector-python (for Python, MySQL)

Most often, I used SQL clients for manual data exploration, writing and executing queries, as well as command line tools and libraries for automating data checks at the API or integration test level.

Examples of automation usage:

// Check the number of records in a table
public int getCountOfUsers(String username) {
    String sql = "SELECT COUNT(*) FROM users WHERE username = ?";
    try (Connection conn = DriverManager.getConnection(url, user, password);
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, username);
        try (ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace(); // Error logging
    }
    return 0;
}

# Check the value of a field in a record
def get_user_status(user_id):
    conn = None
    cursor = None
    try:
        conn = psycopg2.connect(database="mydatabase", user="myuser", password="mypassword", host="localhost")
        cursor = conn.cursor()
        cursor.execute("SELECT status FROM users WHERE id = %s", (user_id,))
        result = cursor.fetchone()
        if result:
            return result[0]
        return None
    except (Exception, psycopg2.Error) as error:
        print("Error while fetching data from PostgreSQL", error) # Error logging
        return None
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()