Embedding SQL
There are cases where you don’t want a separate server but embed immudb directly in the same application process, as a library.
immudb provides you a immutable embedded SQL engine which keeps all history, is tamper-proof and can travel in time. The SQL engine is mounted on top of the embedded key value store. The following illustrative example showcase how to initialize the SQL engine, write and read data in the scope of a SQL transaction.
1package main
2
3import (
4 "context"
5 "log"
6
7 "github.com/codenotary/immudb/embedded/sql"
8 "github.com/codenotary/immudb/embedded/store"
9)
10
11func handleErr(err error) {
12 if err != nil {
13 log.Fatal(err)
14 }
15}
16
17func main() {
18 ctx := context.Background()
19
20 // create/open immudb store at specified path
21 // multi-indexing must be enabled for the SQL engine
22 st, err := store.Open("data", store.DefaultOptions().WithMultiIndexing(true))
23 handleErr(err)
24 defer st.Close()
25
26 // initialize sql engine (specify a key-prefix to isolate generated kv entries)
27 engine, err := sql.NewEngine(st, sql.DefaultOptions().WithPrefix([]byte("sql")))
28 handleErr(err)
29
30 // CREATE DATABASE / USE DATABASE statements are not used here:
31 // they require a MultiDBHandler to be supplied via sql.DefaultOptions().WithMultiDBHandler(...),
32 // and are typically not needed for single-database embedded usage.
33
34 // a sql tx is created and carried over next statements
35 sqltx, _, err := engine.Exec(ctx, nil, "BEGIN TRANSACTION;", nil)
36 handleErr(err)
37
38 // ensure tx is closed (it won't affect committed tx)
39 defer engine.Exec(ctx, sqltx, "ROLLBACK;", nil)
40
41 // creates a table
42 _, _, err = engine.Exec(ctx, sqltx, `
43 CREATE TABLE journal (
44 id INTEGER,
45 date TIMESTAMP,
46 creditaccount INTEGER,
47 debitaccount INTEGER,
48 amount INTEGER,
49 description VARCHAR,
50 PRIMARY KEY id
51 );`, nil)
52 handleErr(err)
53
54 // insert some rows
55 _, _, err = engine.Exec(ctx, sqltx, `
56 INSERT INTO journal (
57 id,
58 date,
59 creditaccount,
60 debitaccount,
61 amount,
62 description
63 ) VALUES
64 (1, NOW(), 100, 0, 4000, 'CREDIT'),
65 (2, NOW(), 0, 50, 4100, 'DEBIT')
66 ;`, nil)
67 handleErr(err)
68
69 // query data including ongoing and unconfirmed changes
70 rowReader, err := engine.Query(ctx, sqltx, `
71 SELECT id, date, creditaccount, debitaccount, amount, description
72 FROM journal
73 WHERE amount > @value;
74 `, map[string]interface{}{"value": 100})
75 handleErr(err)
76
77 // ensure row reader is closed
78 defer rowReader.Close()
79
80 // selected columns can be read from the rowReader
81 cols, err := rowReader.Columns(ctx)
82 handleErr(err)
83
84 for {
85 // iterate over result set
86 row, err := rowReader.Read(ctx)
87 if err == sql.ErrNoMoreRows {
88 break
89 }
90 handleErr(err)
91
92 // each row contains values for the selected columns
93 log.Printf("row: %v\n", row.ValuesBySelector[cols[0].Selector()].RawValue())
94 }
95
96 // close row reader
97 rowReader.Close()
98
99 // commit ongoing transaction
100 _, _, err = engine.Exec(ctx, sqltx, "COMMIT;", nil)
101 handleErr(err)
102}If you need to change options like where things get stored by default, you can do that in the underlying store objects that the SQL engine is using.
Multi-database operations
The example above uses a single, default database — which is the typical setup for embedded usage. Statements like CREATE DATABASE and USE DATABASE are only available when the SQL engine is configured with a MultiDBHandler via sql.DefaultOptions().WithMultiDBHandler(...). Without one, executing those statements will fail with unspecified multidbHandler. Note also that the underlying store must be opened with store.DefaultOptions().WithMultiIndexing(true) for the SQL engine to operate.