SQLite Go: Creating Tables

Summary: in this tutorial, you will learn how to create a new table in the SQLite database.

How to create a new table in Go

To create a new table in an SQLite from Go, you follow these steps:

First, connect to the SQLite database file:

db, err := sql.Open("sqlite", "./my.db")Code language: Go (go)

Second, construct a CREATE TABLE statement:

sql := "CREATE TABLE table...";Code language: Go (go)

Third, call the Exec() method of the DB instance to execute the CREATE TABLE statement:

_, err = db.Exec(sql)Code language: Go (go)

Creating a sample table

The following example shows how to create a new table countries with four columns:

• id – the id of the country.
• name – country name.
• population – the population of the country.
• area – the area of the country.

Step 1. Create a new file country.go within the project directory:

Step 2. Define a function called CreateTable that creates the countries table:

package main

import (
    "database/sql"
    _ "github.com/glebarez/go-sqlite"
)

func CreateTable(db *sql.DB) ( sql.Result, error)  {
    sql := `CREATE TABLE IF NOT EXISTS countries (
        id INTEGER PRIMARY KEY,
        name     TEXT NOT NULL,
        population INTEGER NOT NULL,
        area INTEGER NOT NULL
    );`

    return db.Exec(sql)
}Code language: Go (go)

How it works.

First, import the database/sql and go-sqlite packages:

import (
    "database/sql"
    _ "github.com/glebarez/go-sqlite"
)Code language: Go (go)

Second, construct a CREATE TABLE statement:

sql := `CREATE TABLE IF NOT EXISTS countries (
        id INTEGER PRIMARY KEY,
        name     TEXT NOT NULL,
        population INTEGER NOT NULL,
        area INTEGER NOT NULL
    );`Code language: Go (go)

Third, call the Exec() method of the DB struct’s instance to execute the CREATE TABLE statement, and return the sql.Result and Error:

return db.Exec(sql)Code language: JavaScript (javascript)

Step 3. Call the CreateTable() function inside the main() function:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/glebarez/go-sqlite"
)


func main() {
    // connect to the SQLite database
    db, err := sql.Open("sqlite", "./my.db?_pragma=foreign_keys(1)")
    if err != nil {
        fmt.Println(err)
        return
    }

    defer db.Close()

    // create the countries table
    _, err = CreateTable(db)    
    if err != nil {
        fmt.Println(err)
        return
    }

    fmt.Println("Table countries was created successfully.")
}Code language: Go (go)

Step 4. Execute the Go program to create a new table:

go run main.go country.goCode language: Shell Session (shell)

Output:

Table countries was created successfully.Code language: Shell Session (shell)

Verifying tables

Step 1. Open your terminal and navigate to the project directory.

Step 2. Connect to the my.db SQLite database file using the sqlite3 shell:

sqlite3 my.dbCode language: Shell Session (shell)

Step 3. List all the tables in the my.db database:

countries

The output shows the countries table in the my.db database.

Step 4. Show the schema of the countries table:

CREATE TABLE countries (
                id INTEGER PRIMARY KEY,
                name     TEXT NOT NULL,
                population INTEGER NOT NULL,
                area INTEGER NOT NULL
        );Code language: SQL (Structured Query Language) (sql)

Step 5. Quit the sqlite3 tool:

.quitCode language: CSS (css)

Summary

• Call the Exec() method of the DB struct to execute a CREATE TABLE statement to create a new table.