Check if database table exists using golang

Question

I am trying to do a simple thing, check if there is a table, if not then create that table in database.

Here this is the logic I used.

    test := "June_2019"
    sql_query := `select * from ` + test + `;`

    read_err := db.QueryRow(sql_query, 5)
    error_returned := read_err.Scan(read_err)
    defer db.Close()

    if error_returned == nil {
        fmt.Println("table is there")

    } else {
        fmt.Println("table not there")
    }

In my database I have June_2019 table. But still this code returns me not nil value. I used db.QueryRow(sql_query, 5) 5 as I have five colomns in my table.

What am I missing here? I am still learning golang.

Thanks in advance.

Offtopic; Consider if making a table from application code is logical as your MySQL user has to be more powerfull have CREATE and maybe DROP rights, which can be unsafe.. Also seeing metadata (Year) used as table separator, why are you doing this? ideally you should have one table where you have a year column — Raymond Nijland, Jul 06 '19 at 14:42
.. As MySQL can handle milllion or even in the billions numbers of records just fine in one table when indexes are involved and when in doubt deploy [Partitioning](https://dev.mysql.com/doc/refman/8.0/en/partitioning.html) which does more or less the same thing you are doing but better in every way.. — Raymond Nijland, Jul 06 '19 at 14:44
@RaymondNijland true. but here I did this to learn `golang` and satisfy a simple need. — Sachith Muhandiram, Jul 06 '19 at 15:02
SQL is a language. `go` is a language. When trying to solve a problem, it's best to use the language best suited for the task at hand. Checking for table existence should be left to the SQL side of things e.g. [CREATE TABLE IF NOT EXISTS](https://dev.mysql.com/doc/refman/5.5/en/create-table.html) — colm.anseo, Jul 06 '19 at 15:37
See this answer for an SQL query for [table existence](https://stackoverflow.com/a/8829109/1218512). — colm.anseo, Jul 06 '19 at 15:40
I know this sql syntax would solve the problem. but I asked this question more specific to golang than mysql — Sachith Muhandiram, Jul 06 '19 at 15:51

score 4 · Accepted Answer · answered Jul 07 '19 at 03:04

4

I have solved the problem using golang and MySQL.

_, table_check := db.Query("select * from " + table + ";")

    if table_check == nil {
        fmt.Println("table is there")
    } else {
        fmt.Println("table not there")
    }

I have used db.Query() which returns values and and error, here I checked only error.

I think most people thought I want to do it in MySQL way, I just wanted to learn how to use golang to do MySQL operations.

answered Jul 07 '19 at 03:04

Sachith Muhandiram

2,819
10
45
94

5

You forgot rows.Close() (the first return variable) in case of table_check == nil and that will probably leak memory – k3a Jul 03 '20 at 19:43
1

@k3a does that mean that you shouldn't assign the result to `_` so that you can close the object? – financial_physician Jun 27 '22 at 19:27
Yes, every Query() call with nil error returns *sql.Rows. Those rows need a full for loop iteration with Next() until Next() returns false (which would call Close() internally) or an explicit call to Close() to free the memory used by the resultset. Close() is idempotent and can also be called multiple times safely. – k3a Jun 28 '22 at 19:48

Check if database table exists using golang

1 Answers1