databases

Example of creating a table, inserting rows, and reading rows from a sqlite3 database

Node.js

const sqlite3 = require('sqlite3').verbose()
const db = new sqlite3.Database('./sqlite3.db')
db.serialize(() => {
  db.run('CREATE TABLE persons (name TEXT)')
  const stmt = db.prepare('INSERT INTO persons VALUES (?)')
  const names = ['alice', 'bob', 'charlie']
  for (let i = 0; i < names.length; i++) {
    stmt.run(names[i])
  }
  stmt.finalize()
  db.each('SELECT rowid AS id, name FROM persons', (err, row) => {
    if (err) {
      console.error(err)
      return
    }
    console.log(row.id, row.name)
  })
})
db.close()

Output

1 'alice'
2 'bob'
3 'charlie'

Go

package main
import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
)
func main() {
    db, err := sql.Open("sqlite3", "./sqlite3.db")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    _, err = db.Exec("CREATE TABLE persons (name TEXT)")
    if err != nil {
        panic(err)
    }
    tx, err := db.Begin()
    if err != nil {
        panic(err)
    }
    stmt, err := tx.Prepare("INSERT INTO persons VALUES (?)")
    if err != nil {
        panic(err)
    }
    defer stmt.Close()
    names := []string{"alice", "bob", "charlie"}
    for _, name := range names {
        _, err := stmt.Exec(name)
        if err != nil {
            panic(err)
        }
    }
    tx.Commit()
    rows, err := db.Query("SELECT rowid AS id, name FROM persons")
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
        var id int
        var name string
        err = rows.Scan(&id, &name)
        if err != nil {
            panic(err)
        }
        fmt.Println(id, name)
    }
    err = rows.Err()
    if err != nil {
        panic(err)
    }
}

Output

1 alice
2 bob
3 charlie