Golang CRUD Operations Using Oracle Database: A Step-by-Step Guide

Oracle Database, a highly reliable and robust relational database management system, can be seamlessly integrated with Go (Golang) to build data-driven applications. In this comprehensive guide, we’ll walk you through the essential steps for performing CRUD (Create, Read, Update, Delete) operations using Oracle Database in a Go application. By the end of this article, you’ll have a solid understanding of how to connect to Oracle Database, manipulate data, and work with SQL statements.

Prerequisites

Before we start, make sure you have the following prerequisites in place:

Go Environment: Install Go if you haven’t already. You can download it from the official Go website.
Oracle Database: Oracle Database should be installed and running. You’ll need access to an Oracle Database instance with appropriate credentials. Ensure that the Oracle Database server is reachable from your Go application.
Oracle Go Driver: To interact with Oracle Database from your Go application, you’ll need the “github.com/godror/godror” package. You can install it using go get:
```
go get github.com/godror/godror
```

Step 1: Connecting to Oracle Database

The first step is establishing a connection to the Oracle Database. Create a function to handle this task:

package main

import (
    "context"
    "database/sql"
    "fmt"
    "github.com/godror/godror"
)

func connectToOracleDB(username, password, connectString string) (*sql.DB, error) {
    dsn := fmt.Sprintf("%s/%s@%s", username, password, connectString)
    db, err := sql.Open("godror", dsn)
    if err != nil {
        return nil, err
    }

    // Ping the Oracle Database server to ensure connectivity
    err = db.Ping()
    if err != nil {
        return nil, err
    }

    fmt.Println("Connected to Oracle Database!")
    return db, nil
}

Replace username, password, and connectString with your Oracle Database credentials and connection details.

Step 2: Performing CRUD Operations

Now that we’re connected to Oracle Database, let’s dive into CRUD operations.

Create (Insert) Operation

To insert data into an Oracle Database table, create a function like this:

func insertData(db *sql.DB, tableName string, data map[string]interface{}) error {
    var placeholders []string
    var values []interface{}

    for column, value := range data {
        placeholders = append(placeholders, ":"+column)
        values = append(values, value)
    }

    query := fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s)", tableName, strings.Join(dataColumns, ", "), strings.Join(placeholders, ", "))
    _, err := db.ExecContext(context.Background(), query, values...)
    return err
}

Replace tableName with the name of your Oracle Database table, and data with a map containing column names as keys and corresponding values.

Read (Query) Operation

To retrieve data from an Oracle Database table, create a function like this:

func queryData(db *sql.DB, tableName string) ([]map[string]interface{}, error) {
    query := fmt.Sprintf("SELECT * FROM %s", tableName)
    rows, err := db.QueryContext(context.Background(), query)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    columns, err := rows.Columns()
    if err != nil {
        return nil, err
    }

    var results []map[string]interface{}
    for rows.Next() {
        values := make([]interface{}, len(columns))
        valuePtrs := make([]interface{}, len(columns))

        for i := range columns {
            valuePtrs[i] = &values[i]
        }

        if err := rows.Scan(valuePtrs...); err != nil {
            return nil, err
        }

        rowData := make(map[string]interface{})
        for i, col := range columns {
            rowData[col] = values[i]
        }

        results = append(results, rowData)
    }

    return results, nil
}

Replace tableName with the name of your Oracle Database table.

Update Operation

To update data in an Oracle Database table, create a function like this:

func updateData(db *sql.DB, tableName string, data map[string]interface{}, condition map[string]interface{}) error {
    var setters []string
    var values []interface{}

    for column, value := range data {
        setters = append(setters, fmt.Sprintf("%s = :%s", column, column))
        values = append(values, value)
    }

    var conditions []string

    for column, value := range condition {
        conditions = append(conditions, fmt.Sprintf("%s = :%s", column, column))
        values = append(values, value)
    }

    query := fmt.Sprintf("UPDATE %s SET %s WHERE %s", tableName, strings.Join(setters, ", "), strings.Join(conditions, " AND "))
    _, err := db.ExecContext(context.Background(), query, values...)
    return err
}

Replace tableName with the name of your Oracle Database table, data with a map containing column names as keys and corresponding values for the update, and condition with a map containing column names as keys and corresponding values for the update condition.

Delete Operation

To delete data from an Oracle Database table, use a function like this:

func deleteData(db *sql.DB, tableName string, condition map[string]interface{}) error {
    var conditions []string
    var values []interface{}

    for column, value := range condition {
        conditions = append(conditions, fmt.Sprintf("%s = :%s", column, column))
        values = append(values, value)
    }

    query := fmt.Sprintf("DELETE FROM %s WHERE %s", tableName, strings.Join(conditions, " AND "))
    _, err := db.ExecContext(context.Background(), query, values...)
    return err
}

Replace tableName with the name of your Oracle Database table, and condition with a map containing column names as keys and corresponding values for the delete condition.

Step 3: Putting It All Together

Let’s create a simple Go program that connects to Oracle Database and performs these CRUD operations:

package main

import (
    "database/sql"
    "fmt"
    "context"
    "log"
    "strings"
    "github.com/godror/godror"
)

func main() {
    db, err := connectToOracleDB("username", "password", "connection_string")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Create data
    userData := map[string]interface{}{
        "id":        1,
        "username":  "john_doe",
        "email":     "[email protected]",
    }
    tableName := "users"

    err = insertData(db, tableName, userData)
    if err != nil {
        log.Fatal(err)
    }

    // Query data
    results, err := queryData(db, tableName)
    if err != nil {
        log.Fatal(err)


 }
    fmt.Println("Query Results:")
    for _, result := range results {
        fmt.Println(result)
    }

    // Update data
    updateDataMap := map[string]interface{}{
        "email": "[email protected]",
    }
    updateCondition := map[string]interface{}{
        "id": 1,
    }
    err = updateData(db, tableName, updateDataMap, updateCondition)
    if err != nil {
        log.Fatal(err)
    }

    // Delete data
    deleteCondition := map[string]interface{}{
        "id": 1,
    }
    err = deleteData(db, tableName, deleteCondition)
    if err != nil {
        log.Fatal(err)
    }
}

This program connects to Oracle Database, performs CRUD operations, and displays the results.

Conclusion

In this step-by-step guide, we’ve covered the basics of performing CRUD operations using Oracle Database in a Go application. You’ve learned how to connect to Oracle Database, insert, query, update, and delete records in a table. These fundamental database operations serve as the foundation for building more complex and data-driven applications in Go. Oracle Database’s reliability and Go’s efficiency make them a powerful combination for your projects.