Go to h2 database

Is it possible to connect from Go code to h2 database http://www.h2database.com

First of all you need to run your DB server allowing connections from any host via TCP, PotgreSQL or Web (I have done this by using a linux shell script called runH2Server.sh):

#!/bin/bash
export PATH=$PATH:/tmp/H2DB/jre1.8/bin
export CLASSPATH=/tmp/H2DB/DBServer:/tmp/H2DB/DBServer/h2.jar
java -classpath $CLASSPATH org.h2.tools.Server -webAllowOthers -tcpAllowOthers -pgAllowOthers -baseDir /tmp/H2DB/DBServer

Running the script will produce following otuput:

# ./runH2Server.sh
Web Console server running at http://127.0.1.1:8082 (others can connect)
TCP server running at tcp://127.0.1.1:9092 (others can connect)
PG server running at pg://127.0.1.1:5435 (others can connect)

Now your server is ready for client connections, you could test it connecting your web browser to the IP and port specified, for example: http://192.168.1.130:8082/login.do

Be sure to download the Go postgres driver form "github.com/lib/pq".

Now, from another host (or the same if you want) you can use the following code to test your connection:

package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)
const (
host     = "192.168.1.130"
port = 5435
user     = "sa"
password = ""
dbname   = "/tmp/H2DB/DBServer/test"
)
func main() {
psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
"dbname=%s sslmode=disable", host, port, user, dbname)
db, err := sql.Open("postgres", psqlInfo)
if err != nil {
panic(err)
}
defer db.Close()
err = db.Ping()
if err != nil {
panic(err)
}
fmt.Println("Successfully connected!")
rows, err := db.Query("SHOW TABLES")
if err != nil {
panic(err)
}
fmt.Println("\n\n=== Tables in DB ===")
fmt.Printf("%10v | %15v\n", "tablename", "tableschema")
for rows.Next() {
var tablename string
var tableschema string
err = rows.Scan(&tablename, &tableschema)
if err != nil {
panic(err)
}
fmt.Printf("%10v | %15v\n", tablename, tableschema)
}
fmt.Println("\n\n=== Records in DB ===")
rows, err = db.Query("SELECT * FROM TEST ORDER BY ID")
if err != nil {
panic(err)
}
fmt.Printf("%10v | %15v\n", "ID", "NAME")
for rows.Next() {
var id int
var name string
err = rows.Scan(&id, &name)
if err != nil {
panic(err)
}
fmt.Printf("%10v | %15v\n", id, name)
}
}

Run it and you will have the following output:

C:\Go\PG_Client
λ go run PgDBClient.go
Successfully connected!
=== Tables in DB ===
tablename |     tableschema
TEST |          PUBLIC
=== Records in DB ===
ID |            NAME
1 |           Hello
2 |           World

According to http://www.h2database.com/html/advanced.html:

> ... it supports the PostgreSQL network protocol ... Support for the PostgreSQL network protocol is quite new and should be viewed as experimental. It should not be used for production applications.

So there is high chance that github.com/lib/pq driver will work with h2.

I have developed a "native" Apache H2 Database driver for Go.

https://github.com/jmrobles/h2go

In its day I tried to use the Postgres interface but I encountered problems diagnosing bugs in the SQL syntax.

I wanted to provide a pure go alternative that would directly access H2's TCP interface.