CODEWITHSUNDEEP
mysqlgo
xaos_xv
xaos_xv

Reputation: 769

Go - MySQL inserts even when if NOT NULL set

I'm writing simple API in Go using MySQL but my MySQL code inserts data to row even the column is set to NOT NULL. Here is my code:

schema.sql

CREATE TABLE IF NOT EXISTS seller (
    seller_id int NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL UNIQUE,
    contact varchar(255) NOT NULL,
    street varchar(255) NOT NULL,
    city varchar(255) NOT NULL,
    nip varchar(255) NOT NULL UNIQUE,
    bank varchar(255) NOT NULL,
    account_number varchar(255) NOT NULL UNIQUE,
    PRIMARY KEY (seller_id)
);

database/mysql/invoice.go

func (r *invoiceRepository) CreateSeller(seller *invoice.Seller) error {
        query := "INSERT INTO seller (name, contact, street, city, nip, bank, account_number) VALUES (?, ?, ?, ?, ?, ?, ?)"
        stmt, err := r.db.Prepare(query)
        if err != nil {
                log.Errorf("Unable to create seller %s: %v", seller.SellerName, err)
                return err
        }

        res, err := stmt.Exec(&seller.SellerName, &seller.SellerContact, &seller.SellerStreet, &seller.SellerCity, &seller.SellerNIP, &seller.SellerBank, &seller.SellerAccountNumber)
        if err != nil {
                log.Errorf("Error while creating seller: %v", err)
                return err
        }

        newID, err := res.LastInsertId()
        if err != nil {
                log.Errorf("Error while getting inserted id: %v", err)
                return err
        }

        log.Infof("Created seller %s [id=%d]", seller.SellerName, newID)
        return nil
}

Now when I'm making a POST request (using httpie) with the only seller_name my code creates row even with null columns. It looks like this: http POST localhost:8000/api/seller/create seller_name=testt

Here is how my row looks in table:

+-----------+-------+---------+--------+------+-----+------+----------------+
| seller_id | name  | contact | street | city | nip | bank | account_number |
+-----------+-------+---------+--------+------+-----+------+----------------+
|         1 | testt |         |        |      |     |      |                |
+-----------+-------+---------+--------+------+-----+------+----------------+

I don't know where I've made a mistake.

EDIT (added model)

type Seller struct {
        SellerID            int64  `json:"seller_id"`
        SellerName          string `json:"seller_name"`
        SellerContact       string `json:"seller_contact"`
        SellerStreet        string `json:"seller_street"`
        SellerCity          string `json:"seller_city"`
        SellerNIP           string `json:"seller_nip"`
        SellerBank          string `json:"seller_bank"`
        SellerAccountNumber string `json:"seller_account_number"`
}

Upvotes: 0

Views: 107

Answers (2)

Grigoreas P.
Grigoreas P.

Reputation: 2472

Yes, because when it comes to Mysql: emtpy string (e.g '') is NOT a null. It treats it like being a string (only empty).

Upvotes: 0

mu is too short
mu is too short

Reputation: 434665

Go isn't inserting nulls in your table, it is inserting empty strings. Your code is effectively doing this:

seller := Seller{}
seller.SellerName = "testt"

to create the seller that you're inserting. So all the fields in your seller except SellerName will have the zero value for their type; the fields causing you trouble are strings and the zero value of a string is "" (i.e an empty string) so the SQL ends up looking like:

INSERT INTO seller (name, contact, ...)
VALUES ('testt', '', ...)

and there isn't a NULL in sight.

You could validate the incoming data in Go so that empty strings are rejected, you could use NullString instead of string and let the database deal with NULLs (assuming MySQL is properly configured), or do both.

Upvotes: 4

Related Questions