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.