{"id":4129,"date":"2024-10-31T09:27:55","date_gmt":"2024-10-31T02:27:55","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=4129"},"modified":"2024-10-31T16:45:28","modified_gmt":"2024-10-31T09:45:28","slug":"create-table","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-go\/create-table\/","title":{"rendered":"SQLite Go: Creating Tables"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a new table in the SQLite database.<\/p>\n\n\n\n

How to create a new table in Go<\/h2>\n\n\n\n

To create a new table in an SQLite from Go, you follow these steps:<\/p>\n\n\n\n

First, connect to the SQLite database file<\/a>:<\/p>\n\n\n

db, err := sql.Open(\"sqlite\"<\/span>, \".\/my.db\"<\/span>)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
sql := \"CREATE TABLE table...\"<\/span>;<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, call the Exec()<\/code> method of the DB<\/code> instance to execute the CREATE TABLE<\/code> statement:<\/p>\n\n\n
_, err = db.Exec(sql)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Creating a sample table<\/h2>\n\n\n\n
The following example shows how to create a new table countries<\/code> with four columns:<\/p>\n\n\n\n
    \n
  • id <\/code>– the id of the country.<\/li>\n\n\n\n
  • name <\/code>– country name.<\/li>\n\n\n\n
  • population<\/code> – the population of the country.<\/li>\n\n\n\n
  • area <\/code>– the area of the country.<\/li>\n<\/ul>\n\n\n\n
    Step 1. Create a new file country.go<\/code> within the project directory:<\/p>\n\n\n\n
    Step 2. Define a function called CreateTable<\/code> that creates the countries<\/code> table:<\/p>\n\n\n
    package<\/span> main\n\nimport<\/span> (\n    \"database\/sql\"<\/span>\n    _ \"github.com\/glebarez\/go-sqlite\"<\/span>\n)\n\nfunc<\/span> CreateTable<\/span>(db *sql.DB)<\/span> ( sql.Result, error)<\/span><\/span>  {\n    sql := `CREATE TABLE IF NOT EXISTS countries (\n        id INTEGER PRIMARY KEY,\n        name     TEXT NOT NULL,\n        population INTEGER NOT NULL,\n        area INTEGER NOT NULL\n    );`<\/span>\n\n    return<\/span> db.Exec(sql)\n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    How it works.<\/p>\n\n\n\n
    First, import the database\/sql<\/code> and go-sqlite<\/code> packages:<\/p>\n\n\n
    import<\/span> (\n    \"database\/sql\"<\/span>\n    _ \"github.com\/glebarez\/go-sqlite\"<\/span>\n)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    Second, construct a CREATE TABLE<\/code> statement:<\/p>\n\n\n
    sql := `CREATE TABLE IF NOT EXISTS countries (\n        id INTEGER PRIMARY KEY,\n        name     TEXT NOT NULL,\n        population INTEGER NOT NULL,\n        area INTEGER NOT NULL\n    );`<\/span><\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    Third, call the Exec()<\/code> method of the DB<\/code> struct’s instance to execute the CREATE TABLE<\/code> statement, and return the sql.Result<\/code> and Error<\/code>:<\/p>\n\n\n
    return<\/span> db.Exec(sql)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 3. Call the CreateTable()<\/code> function inside the main()<\/code> function:<\/p>\n\n\n
    package<\/span> main\n\nimport<\/span> (\n    \"database\/sql\"<\/span>\n    \"fmt\"<\/span>\n    _ \"github.com\/glebarez\/go-sqlite\"<\/span>\n)\n\n\nfunc<\/span> main<\/span>()<\/span><\/span> {\n    \/\/ connect to the SQLite database<\/span>\n    db, err := sql.Open(\"sqlite\"<\/span>, \".\/my.db?_pragma=foreign_keys(1)\"<\/span>)\n    if<\/span> err != nil<\/span> {\n        fmt.Println(err)\n        return<\/span>\n    }\n\n    defer<\/span> db.Close()\n\n    \/\/ create the countries table<\/span>\n    _, err = CreateTable(db)    \n    if<\/span> err != nil<\/span> {\n        fmt.Println(err)\n        return<\/span>\n    }\n\n    fmt.Println(\"Table countries was created successfully.\"<\/span>)\n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 4. Execute the Go program to create a new table:<\/p>\n\n\n
    go run main.go country.go<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    Table countries was created successfully.<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
    Verifying tables<\/h2>\n\n\n\n
    Step 1. Open your terminal and navigate to the project directory.<\/p>\n\n\n\n
    Step 2. Connect to the my.db<\/code> SQLite database file using the sqlite3<\/code> shell:<\/p>\n\n\n
    sqlite3 my.db<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 3. List all the tables in the my.db<\/code> database:<\/p>\n\n\n
    countries<\/code><\/span><\/pre>\n\n\n
    The output shows the countries<\/code> table in the my.db<\/code> database.<\/p>\n\n\n\n
    Step 4. Show the schema of the countries<\/code> table:<\/p>\n\n\n
    CREATE<\/span> TABLE<\/span> countries (\n                id<\/span> INTEGER<\/span> PRIMARY KEY<\/span>,\n                name<\/span>     TEXT<\/span> NOT<\/span> NULL<\/span>,\n                population INTEGER<\/span> NOT<\/span> NULL<\/span>,\n                area INTEGER<\/span> NOT<\/span> NULL<\/span>\n        );<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 5. Quit the sqlite3<\/code> tool:<\/p>\n\n\n
    .quit<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Call the Exec()<\/code> method of the DB<\/code> struct to execute a CREATE TABLE<\/code> statement to create a new table.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful ?<\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
Second, construct a CREATE TABLE<\/a> statement:<\/p>\n\n\n