Building a Basic ORM in Go

This challenge focuses on integrating Object-Relational Mapping (ORM) principles into a Go application. You will create a simplified ORM layer to interact with a database, demonstrating how to map Go structs to database tables and perform common CRUD (Create, Read, Update, Delete) operations. This is a fundamental skill for building robust Go applications that interact with relational databases.

Problem Description

Your task is to implement a basic ORM functionality in Go that allows you to interact with a SQLite database. You will define Go structs that represent database entities and then build methods to persist these entities to the database and retrieve them.

Key Requirements:

1. Database Setup: Use a SQLite in-memory database for simplicity.
2. Struct Definition: Define at least two Go structs that will be mapped to database tables. These structs should have fields corresponding to database columns. Include basic types like int, string, and time.Time.
3. Table Creation: Implement a function that automatically creates the necessary tables in the database based on the defined structs if they don't already exist.
4. Save Operation: Implement a method to save (insert or update) a struct instance to its corresponding table. If the struct has an ID field (and it's zero), it should be an insert. If it has a non-zero ID, it should be an update.
5. FindByID Operation: Implement a function to retrieve a single record from a table by its primary key (ID).
6. FindAll Operation: Implement a function to retrieve all records from a table.
7. Delete Operation: Implement a method to delete a record from a table by its ID.

Expected Behavior:

• The ORM should abstract away much of the SQL query writing.
• When saving a struct, the ORM should generate the appropriate INSERT or UPDATE SQL statement.
• When finding records, the ORM should generate SELECT statements and map the returned rows back to Go structs.
• Error handling should be robust, returning meaningful errors when database operations fail.

Edge Cases to Consider:

• Handling of NULL values in the database.
• What happens if you try to find a record that doesn't exist?
• What happens if you try to delete a record that doesn't exist?
• The Save operation needs to correctly differentiate between inserting a new record and updating an existing one.

Examples

Example 1: Saving and Retrieving a User

// Assume User struct is defined and ORM is initialized with a database connection.

// Create a new user
newUser := User{Name: "Alice", Email: "alice@example.com", Age: 30}
err := orm.Save(&newUser) // ORM should assign an ID to newUser upon successful insert

// Retrieve the user by ID
foundUser, err := orm.FindByID(1, &User{}) // Assuming the new user got ID 1

// Expected Output (values for ID might vary based on insertion order):
// newUser.ID should be 1 (or the assigned ID)
// foundUser.Name should be "Alice"
// foundUser.Email should be "alice@example.com"
// foundUser.Age should be 30

Explanation:

The Save method inserts a new User record into the users table. The ORM automatically assigns a primary key to newUser. The FindByID method then queries the users table for the record with the specified ID and populates a new User struct with the retrieved data.

Example 2: Updating and Deleting a Product

// Assume Product struct is defined and ORM is initialized.

// Create a new product
newProduct := Product{Name: "Laptop", Price: 1200.00}
err := orm.Save(&newProduct) // Assume it gets ID 5

// Update the product
newProduct.Price = 1150.00
err = orm.Save(&newProduct) // ORM should perform an UPDATE

// Retrieve the updated product
updatedProduct, err := orm.FindByID(5, &Product{})

// Delete the product
err = orm.Delete(&updatedProduct) // Deletes the product with ID 5

// Attempt to find the deleted product
deletedProduct, err := orm.FindByID(5, &Product{})

// Expected Output:
// updatedProduct.Price should be 1150.00
// The FindByID call after deletion should return a "record not found" error.

Explanation:

The first Save inserts the product. The second Save, because newProduct now has a non-zero ID, performs an UPDATE. FindByID fetches the updated data. The Delete method removes the product from the database. Subsequent attempts to find it result in an error.

Example 3: Handling FindAll and Non-existent IDs

// Assume `User` struct and ORM are initialized.
// Let's say there are users with IDs 1 and 2 in the database.

// Retrieve all users
allUsers, err := orm.FindAll(&User{})

// Attempt to find a non-existent user
nonExistentUser, err := orm.FindByID(999, &User{})

// Expected Output:
// allUsers should be a slice of User structs containing the users with IDs 1 and 2.
// The call to FindByID(999, ...) should return a specific error indicating "record not found".

Explanation:

FindAll retrieves all entries from the users table. FindByID with an ID that doesn't exist correctly signals that no record was found.

Constraints

• The ORM must work with a database/sql compatible driver (SQLite is recommended, github.com/mattn/go-sqlite3 is a good choice).
• Struct field names should ideally map to snake_case database column names (e.g., FirstName maps to first_name). You might need to use struct tags for mapping or implement a convention.
• The ORM should handle basic data types (string, int, float, time.Time) for struct fields.
• Performance is not the primary concern for this challenge, but avoid extremely inefficient query generation.
• Primary keys are assumed to be an integer type named ID (or similar) and auto-incrementing.

Notes

• Consider using reflection to inspect struct fields and their types for mapping to database columns and generating SQL.
• Struct tags (e.g., db:"column_name") are a common way to define field-to-column mappings explicitly and can be very helpful.
• You'll need to handle the conversion of Go types to SQL types and vice-versa.
• Think about how your Save method will generate INSERT versus UPDATE statements. A common approach is to check if the primary key field is zero or not.
• For error handling, consider defining custom error types or using standard library errors effectively to distinguish between different types of database failures.
• This challenge is designed to give you a practical understanding of how ORMs work under the hood. You don't need to replicate the full feature set of mature ORMs like GORM or SQLBoiler.