Database testing : Check database field design

Check if the database fields are designed with the correct data type and data length.

Language: Java

Framework: JUnit using JDBC

copy
1import java.sql.*;23public class DatabaseTesting {45 Connection con;6 Statement stmt;7 8 @Before9 public void setUp() throws Exception {10 /​/​Assuming database is hosted locally11 Class.forName("com.mysql.jdbc.Driver");12 con = DriverManager.getConnection("jdbc:mysql:/​/​localhost:3306/​myDatabase", "root", "password");13 stmt = con.createStatement();14 }1516 @After17 public void tearDown() throws Exception {18 con.close();19 }2021 @Test22 public void testDatabaseFieldDesign() throws Exception {23 /​/​Assuming the table name is 'users' and expected data type is string with maximum data length of 5024 ResultSet rs = stmt.executeQuery("SHOW COLUMNS FROM users");25 while(rs.next()) {26 String field = rs.getString("Field");27 String type = rs.getString("Type");28 int length = rs.getInt("Length");29 30 /​/​Assuming the expected data type is VARCHAR and expected length is 5031 assertEquals("VARCHAR(50)", type);32 assertEquals(50, length);33 }34 }35 36 /​/​Uncomment this block of code and replace 'seleniumGridURL' with the actual URL to connect to remote client37 /​*38 @Before39 public void setUpRemote() throws Exception {40 DesiredCapabilities capabilities = DesiredCapabilities.chrome();41 capabilities.setPlatform(Platform.MAC);42 RemoteWebDriver driver = new RemoteWebDriver(new URL("seleniumGridURL"), capabilities);43 }44 */​45}

Language: Python

Framework: Pytest

copy
1# Assumption: We are using MySQL database and have access to it.2# Importing the required packages3import mysql.connector4import pytest56# Fixture function to connect to the database7@pytest.fixture(scope="module")8def db_conn():9 # Connecting to the database10 conn = mysql.connector.connect(11 host="localhost",12 user="root",13 password="root",14 database="test_db"15 )16 # Returning the database connection object17 yield conn18 # Closing the connection after use19 conn.close()2021# Test case function to check database field design22def test_database_field_design(db_conn):23 # Creating a cursor object to execute SQL queries24 cursor = db_conn.cursor()25 # Executing SQL query to get the column names, data types and data lengths from the table26 cursor.execute("DESCRIBE table_name")27 # Fetching all the rows from the query output28 res = cursor.fetchall()29 # List to hold the table field names and their corresponding data types and lengths30 field_details = []31 # Looping through the query output rows and storing the field details in the list32 for row in res:33 field_details.append((row[0], row[1], row[2]))34 # Expected column names, data types and lengths35 expected_field_details = [("id", "int", 11), ("name", "varchar", 50), ("age", "int", 11)]36 # Comparing the expected and actual field details37 assert field_details == expected_field_details38

Disclaimer: Following code snippets and related information have been sourced from GitHub and/or generated using AI code generation tools. LambdaTest takes no responsibility in the accuracy of the code and is not liable for any damages.

Accelerate Your Automation Test Cycles With LambdaTest

Leverage LambdaTest’s cloud-based platform to execute your automation tests in parallel and trim down your test execution time significantly. Your first 100 automation testing minutes are on us.

Try LambdaTest

Power Your Software Testing with AI and cloud

Test Intelligently and ship faster. Deliver unparalleled digital experiences for real world enterprises.

Start Free Testing