Databases (SQLite)

import { DatabaseSync } from 'node:sqlite'
 
const db = new DatabaseSync('./sqlite3.db')
 
db.exec('DROP TABLE IF EXISTS persons')
db.exec('CREATE TABLE persons (name TEXT)')
 
const insert = db.prepare('INSERT INTO persons VALUES (?)')
const names = ['alice', 'bob', 'charlie']
for (const name of names) {
  insert.run(name)
}
 
const select = db.prepare('SELECT rowid AS id, name FROM persons')
for (const row of select.all()) {
  console.log(row.id, row.name) // 1 alice / 2 bob / 3 charlie
}
 
db.close()

package main
 
import (
	"database/sql"
	"fmt"
 
	_ "github.com/mattn/go-sqlite3" // side-effect import: registers the "sqlite3" driver
)
 
func main() {
	db, err := sql.Open("sqlite3", "./sqlite3.db")
	if err != nil {
		panic(err)
	}
	defer db.Close()
 
	if _, err := db.Exec("DROP TABLE IF EXISTS persons"); err != nil {
		panic(err)
	}
	if _, err := db.Exec("CREATE TABLE persons (name TEXT)"); 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 {
		if _, err := stmt.Exec(name); err != nil {
			panic(err)
		}
	}
	if err := tx.Commit(); err != nil {
		panic(err)
	}
 
	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
		if err := rows.Scan(&id, &name); err != nil {
			panic(err)
		}
		fmt.Println(id, name) // 1 alice / 2 bob / 3 charlie
	}
	if err := rows.Err(); err != nil {
		panic(err)
	}
}

// Cargo.toml: rusqlite = { version = "0.40", features = ["bundled"] }
use rusqlite::Connection;
 
fn main() -> rusqlite::Result<()> {
    let mut conn = Connection::open("./sqlite3.db")?;
 
    conn.execute("DROP TABLE IF EXISTS persons", [])?;
    conn.execute("CREATE TABLE persons (name TEXT)", [])?;
 
    let tx = conn.transaction()?;
    {
        let mut stmt = tx.prepare("INSERT INTO persons VALUES (?1)")?;
        for name in ["alice", "bob", "charlie"] {
            stmt.execute([name])?;
        }
    }
    tx.commit()?;
 
    let mut stmt = conn.prepare("SELECT rowid AS id, name FROM persons")?;
    let rows = stmt.query_map([], |row| Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?)))?;
    for row in rows {
        let (id, name) = row?;
        println!("{id} {name}"); // 1 alice / 2 bob / 3 charlie
    }
 
    Ok(())
}

import SQLite3
 
var db: OpaquePointer?
sqlite3_open("./sqlite3.db", &db)
defer { sqlite3_close(db) }
 
sqlite3_exec(db, "DROP TABLE IF EXISTS persons", nil, nil, nil)
sqlite3_exec(db, "CREATE TABLE persons (name TEXT)", nil, nil, nil)
 
let sqliteTransient = unsafeBitCast(-1, to: sqlite3_destructor_type.self) // SQLITE_TRANSIENT: let sqlite3 copy the string
 
sqlite3_exec(db, "BEGIN", nil, nil, nil)
var insertStmt: OpaquePointer?
sqlite3_prepare_v2(db, "INSERT INTO persons VALUES (?)", -1, &insertStmt, nil)
for name in ["alice", "bob", "charlie"] {
    sqlite3_bind_text(insertStmt, 1, name, -1, sqliteTransient)
    sqlite3_step(insertStmt)
    sqlite3_reset(insertStmt)
}
sqlite3_finalize(insertStmt)
sqlite3_exec(db, "COMMIT", nil, nil, nil)
 
var selectStmt: OpaquePointer?
sqlite3_prepare_v2(db, "SELECT rowid AS id, name FROM persons", -1, &selectStmt, nil)
while sqlite3_step(selectStmt) == SQLITE_ROW {
    let id = sqlite3_column_int64(selectStmt, 0)
    let name = String(cString: sqlite3_column_text(selectStmt, 1))
    print(id, name) // 1 alice / 2 bob / 3 charlie
}
sqlite3_finalize(selectStmt)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
 
// Maven: org.xerial:sqlite-jdbc:3.53.4.0
void main() throws Exception {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite:./sqlite3.db")) {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("DROP TABLE IF EXISTS persons");
            stmt.execute("CREATE TABLE persons (name TEXT)");
        }
 
        conn.setAutoCommit(false);
        try (PreparedStatement insert = conn.prepareStatement("INSERT INTO persons VALUES (?)")) {
            for (String name : new String[] {"alice", "bob", "charlie"}) {
                insert.setString(1, name);
                insert.executeUpdate();
            }
        }
        conn.commit();
        conn.setAutoCommit(true);
 
        try (Statement stmt = conn.createStatement();
             ResultSet rows = stmt.executeQuery("SELECT rowid AS id, name FROM persons")) {
            while (rows.next()) {
                IO.println(rows.getInt("id") + " " + rows.getString("name")); // 1 alice / 2 bob / 3 charlie
            }
        }
    }
}