Golang | SQLite cant create Database -> Syntax Error

Issue

i am trying to create an SQLite Database with Golang for local Installations of the program. But my problem now is, that for some reason it dosent seem to work, but i have no idea why. It always says that when i want to create an Database with an SQL Query (CREATE DATABASE [name]) it says the syntax is invalid. And since this query dosent seem to exist for SQLite i tried only creating the Table, but then i get an Error that i use an unknown database. I have tried many things now but nothing seems to work. First i thought that maybe the error occurs bc the queries are executed before an Database Connection was created/open. But thats now 100% not the case bc i tried sleeping before the sql queries are executed, what changed nothing. The error i get when i run this code:

panic: near "DATABASE": syntax error

goroutine 1 [running]:
git.lambya.com/lucwol/coreflare/database.checkError(...)
        /home/lambya/Dev/piflare/coreflare/database/db.go:37
git.lambya.com/lucwol/coreflare/database.deploy()
        /home/lambya/Dev/piflare/coreflare/database/db.go:28 +0xdc
git.lambya.com/lucwol/coreflare/database.Connect({0x832adf, 0x9})
        /home/lambya/Dev/piflare/coreflare/database/db.go:23 +0xbb
main.main()
        /home/lambya/Dev/piflare/coreflare/main.go:102 +0x29
exit status 2

Thanks for the support 🙂

Heres the code.

package database

import (
    "database/sql"
    "os"
    "time"

    _ "github.com/mattn/go-sqlite3"
)

var DB *sql.DB

func Connect(path string) {
    if _, err := os.Stat(path); err != nil {
        file, err := os.Create(path)
        checkError(err)
        file.Close()
    }
    db, err := sql.Open("sqlite3", path)
    checkError(err)
    time.Sleep(time.Millisecond * 2000)
    DB = db
    deploy()
}

func deploy() {
    query, err := DB.Prepare("CREATE DATABASE test")
    checkError(err)
    query.Exec()
    query2, err := DB.Prepare("CREATE TABLE test.user(id int NOT NULL AUTO_INCREMENT, name varchar(50), PRIMARY_KEY(id))")
    checkError(err)
    query2.Exec()
}

func checkError(err error) {
    if err != nil {
        panic(err)
    }
}

Solution

Hope this helps

1. the database name created with os.create(path) when you create the file itself

2. AUTOINCREMENT key is one word

3. if you want to make user.id primary key it should be integer

   package main
   
   import (
       "database/sql"
       _ "github.com/mattn/go-sqlite3" // Import go-sqlite3 library
       "os"
       "time"
   )
   
   var DB *sql.DB
   
   func Connect(path string) {
       if _, err := os.Stat(path); err != nil {
           file, err := os.Create(path)
           checkError(err)
           file.Close()
       }
       db, err := sql.Open("sqlite3", path)
       checkError(err)
       time.Sleep(time.Millisecond * 2000)
       DB = db
       deploy()
   }
   func deploy() {
       query2, err := DB.Prepare("CREATE TABLE user(id integer NOT NULL PRIMARY KEY AUTOINCREMENT, name varchar(50) )")
       checkError(err)
       query2.Exec()
   }
   
   func checkError(err error) {
       if err != nil {
           panic(err)
       }
   }
   
   func main() {
       Connect("./sample.db")
   }
   

Answered By – M.abdelrhman

Answer Checked By – Gilberto Lyons (GoLangFix Admin)