CRUD operations with MSSQL Server

Golang give us a generic interface to work with SQL Databases.

But it’s crucial to use a database driver altogether, check the drivers list: Drivers List

In this article it will be used this driver MSSQL Server Driver (denisenkom)

Setting the environment up

It’s necessary to create 3 tables on the database in order to run the examples.

    create table movie (
        id int identity(1, 1) not null,
        title nvarchar(255),
        running_time int,
    
        constraint PK_MOVIE primary key(id)
    )
    
    create table actor (
        id int identity(1, 1) not null,
        name nvarchar(255),
    
        constraint PK_ACTOR primary key(id)
    )
    
    create table movie_actor (
        id int identity(1, 1) not null,
        movie_id int,
        actor_id int,
    
        constraint PK_MOVIE_ACTOR primary key(id),
        constraint FK_MOVIE foreign key(movie_id) references movie(id),
        constraint FK_ACTOR foreign key(actor_id) references actor(id)
    )

It’s necessary to have these 3 structs too

    type Movie struct {
        Id          int
        Title       string
        RunningTime int
    }
    
    type Actor struct {
        Id   int
        Name string
    }
    
    type MovieActor struct {
        Id      int
        MovieId int
        ActorId int
    }

Building up the connection string

In order to help us to build the connection string with MSSQL Server, lets use the package net/url*.

Further information about the package net/url click here

	query := url.Values{}
	query.Add("app name", "myAppName")
	query.Add("database", "movies")

	u := &url.URL{
		Scheme:   "sqlserver",
		User:     url.UserPassword("golang", "gopass"),
		Host:     fmt.Sprintf("%s:%d", "192.168.1.34", 1433),
		RawQuery: query.Encode(),
	}

Then we can open the connection with the database:

	db, err := sql.Open("sqlserver", u.String())

	if err != nil {
		panic(err)
	}
	defer db.Close()

Inserting a row on the database

We’ll come up with a function to insert an actor on the database:

    func insertActor(db *sql.DB, actor *Actor) (int, error) {
        stmt, err := db.Prepare("INSERT INTO ACTOR(NAME) OUTPUT INSERTED.id VALUES (@p1)")
        if err != nil {
            return 0, err
        }
        defer stmt.Close()
    
        var lastInsertedId int
        err = stmt.QueryRow(actor.Name).Scan(&lastInsertedId)
        if err != nil {
            return 0, nil
        }
    
        return lastInsertedId, nil
    }

In this instruction “INSERT INTO ACTOR(NAME) OUTPUT INSERTED.id VALUES (@p1)”, we use OUTPUT INSERTED.id to get the id that has been generated by the database, since it’s a generated automatically sequential field.

Having this function ready, we’ll be able to call it.

	lastId, err := insertActor(db, a)
	if err != nil {
		panic(err)
	}

	fmt.Printf("Last actor inserted Name: %s  Id: %d", a.Name, lastId)

>> Last actor inserted - Name: Alfredo Pacino  Id: 1

Function to insert a movie

    func insertMovie(db *sql.DB, movie *Movie) (int, error) {
        stmt, err := db.Prepare("INSERT INTO MOVIE(TITLE, RUNNING_TIME) OUTPUT INSERTED.id VALUES (@p1, @p2)")
        if err != nil {
            return 0, err
        }
        defer stmt.Close()
    
        var lastInsertedId int
        err = stmt.QueryRow(movie.Title, movie.RunningTime).Scan(&lastInsertedId)
        if err != nil {
            return 0, err
        }
    
        return lastInsertedId, nil
    }

Now we are able to call the function to insert a movie

    m := &Movie{
		Title:       "Scent of a Woman",
		RunningTime: 142,
	}

	lastId, err := insertMovie(db, m)
	if err != nil {
		panic(err)
	}

	fmt.Printf("Last movie inserted - Title: %s - Running Time: %d - Id: %d", m.Title, m.RunningTime, lastId)

>> Last movie inserted - Title: Scent of a Woman - Running Time: 142  Id: 1

Altering a database value

The update function will return the number of updated rows and if there was some error:

In order to get the number of updated rows we can use the RowsAffected() function

    func updateActor(db *sql.DB, actorId int, name string) (int64, error) {
        stmt, err := db.Prepare("UPDATE ACTOR SET NAME = @p1 WHERE ID = @P2")
        if err != nil {
            return 0, err
        }
        defer stmt.Close()
    
        result, err := stmt.Exec(name, actorId)
        if err != nil {
            return 0, nil
        }
    
        rowsAffected, err := result.RowsAffected()
        if err != nil {
            return 0, err
        }
    
        return rowsAffected, nil
    }

	updatedRows, err := updateActor(db, 1, "Al Pacino")
	if err != nil {
		panic(err)
	}

	fmt.Printf("Number of updated rows: %d", updatedRows)

>> Number of updated rows: 1

Retrieving values from the database

    func getActor(db *sql.DB, actorId int) (*Actor, error) {
        stmt, err := db.Prepare("SELECT ID, NAME FROM ACTOR WHERE ID = @p1")
        if err != nil {
            return nil, err
        }
        defer stmt.Close()
    
        var a Actor
        err = stmt.QueryRow(actorId).Scan(&a.Id, &a.Name)
        if err != nil {
            return nil, err
        }
    
        return &a, nil
    }

	a, err := getActor(db, 1)

	if err != nil {
		panic(err)
	}

	fmt.Printf("Actor Id: %d - Actor Name: %s", a.Id, a.Name)

>> Actor Id: 1 - Actor Name: Al Pacino

Deleting rows from the database

    func deleteActor(db *sql.DB, actorId int) (int64, error) {
        stmt, err := db.Prepare("DELETE FROM ACTOR WHERE ID = @p1")
        if err != nil {
            return 0, err
        }
        defer stmt.Close()
    
        result, err := stmt.Exec(actorId)
        if err != nil {
            return 0, err
        }
    
        rowsAffected, err := result.RowsAffected()
        if err != nil {
            return 0, err
        }
    
        return rowsAffected, nil
    }

	deletedRows, err := deleteActor(db, 5)
	if err != nil {
		panic(err)
	}

	fmt.Printf("Number os deleted actors: %d", deletedRows)

>> Number of deleted actors: 1

Joining an actor to a movie

    func insertActorMovie(db *sql.DB, movieId int, actorId int) (int, error) {
        stmt, err := db.Prepare("INSERT INTO MOVIE_ACTOR(MOVIE_ID, ACTOR_ID) OUTPUT INSERTED.id VALUES(@p1, @p2)")
        if err != nil {
            return 0, err
        }
        defer stmt.Close()
    
        var lastInsertedId int
        err = stmt.QueryRow(movieId, actorId).Scan(&lastInsertedId)
        if err != nil {
            return 0, err
        }
    
        return lastInsertedId, nil
    }

	lastInsertedId, err := insertActorMovie(db, 1, 1)
	if err != nil {
		panic(err)
	}

	fmt.Printf("Last ID inserted: %d", lastInsertedId)

>> Last ID inserted: 1

Retrieving all actors who participated in a movie

    func getMovieAndActors(db *sql.DB, movieId int) (*MovieActors, error) {
        stmt, err := db.Prepare(`
                                        select 
                                            b.id ActorId,
                                            b.name ActorName,
                                            c.id MovieId,
                                            c.title MovieTitle,
                                            c.running_time RunningTime
                                        from       movie_actor a
                                        inner join actor       b on a.actor_id = b.id
                                        inner join movie       c on a.movie_id = c.id
                                        where a.movie_id = @p1
                                `)
        if err != nil {
            return nil, err
        }
        defer stmt.Close()
    
        var a Actor
        var m Movie
        var movieActors MovieActors
        rows, err := stmt.Query(movieId)
        if err != nil {
            return nil, err
        }
        defer rows.Close()
    
        for rows.Next() {
            err = rows.Scan(&a.Id, &a.Name, &m.Id, &m.Title, &m.RunningTime)
    
            if movieActors.Movie.Id == 0 {
                movieActors.Movie = m
            }
    
            movieActors.Actors = append(movieActors.Actors, a)
        }
    
        return &movieActors, nil
    }

	m, err := getMovieAndActors(db, 1)
	if err != nil {
		panic(err)
	}

	for _, v := range m.Actors {
		fmt.Printf("Movie info Id: %d - Title: %s - Running Time: %d - Actor Info Id: %d - Name: %s\n", m.Id, m.Title, m.RunningTime, v.Id, v.Name)
	}

>> Movie info Id: 1 - Title: Scent of a Woman - Running Time: 142 - Actor Info Id: 1 - Name: Al Pacino
>> Movie info Id: 1 - Title: Scent of a Woman - Running Time: 142 - Actor Info Id: 2 - Name: Chris O'Donnel
>> Movie info Id: 1 - Title: Scent of a Woman - Running Time: 142 - Actor Info Id: 3 - Name: James Rebhorn
>> Movie info Id: 1 - Title: Scent of a Woman - Running Time: 142 - Actor Info Id: 4 - Name: Gabrielle Anwar