Gatabase

• Works with ARC, ORC, --panics:on, --experimental:strictFuncs.

Use

• Gatabase is designed as 1 simplified Strong Static Typed Connection-Pooling Compile-Time SQL DSL Sugar. Nim mimics SQL. ~1000 LoC.
• Gatabase syntax is almost the same as SQL syntax, no new ORM to learn ever again, any SQL WYSIWYG is your GUI.
• You can literally Copy&Paste a SQL query from StackOverflow to Gatabase and with few tiny syntax tweaks is running.
• SQL is Minified when build for Release, Pretty-Printed when build for Debug. It can be assigned to let and const.
• Static Connection Pooling Array with 100+ ORM Queries.
• Uses only system.nim, everything done via template/macro, strutils is not imported, future-proof your code.

Support

• All SQL standard syntax is supported.
• ✅ -- Human readable comments, multi-line comments produce multi-line SQL comments, requires Stropping.
• ✅ COMMENT, Postgres-only.
• ✅ UNION, UNION ALL.
• ✅ INTERSECT, INTERSECT ALL.
• ✅ EXCEPT, EXCEPT ALL, requires Stropping.
• ✅ CASE with multiple WHEN and 1 ELSE with correct indentation, requires Stropping.
• ✅ INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.
• ✅ OFFSET.
• ✅ LIMIT.
• ✅ FROM, requires Stropping.
• ✅ WHERE, WHERE NOT, WHERE EXISTS, WHERE NOT EXISTS.
• ✅ ORDER BY.
• ✅ SELECT, SELECT *, SELECT DISTINCT.
• ✅ SELECT TOP, SELECT MIN, SELECT MAX, SELECT AVG, SELECT SUM, SELECT COUNT.
• ✅ SELECT trim(lower( )) for strings, SELECT round( ) for floats, useful shortcuts.
• ✅ DELETE FROM.
• ✅ LIKE, NOT LIKE.
• ✅ BETWEEN, NOT BETWEEN.
• ✅ HAVING.
• ✅ INSERT INTO.
• ✅ IS NULL, IS NOT NULL.
• ✅ UPDATE, SET.
• ✅ VALUES.
• ✅ DROP TABLE IF EXISTS.
• ✅ CREATE TABLE IF NOT EXISTS.

Not supported:

• Deep complex nested SubQueries are not supported, because KISS.
• TRUNCATE, because is the same as DELETE FROM without a WHERE.
• WHERE IN, WHERE NOT IN, because is the same as JOIN, but JOIN is a lot faster.

API Equivalents

Nim StdLib API | Gatabase ORM API -------------------|------------------ tryExec | tryExec exec | exec getRow | getRow getAllRows | getAllRows getValue | getValue tryInsertID | tryInsertID insertID | insertID execAffectedRows | execAffectedRows

Output

Output | Gatabase ORM API -----------|------------------ bool | tryExec Row | getRow seq[Row] | getAllRows int64 | tryInsertID int64 | insertID int64 | execAffectedRows SqlQuery | sqls any | getValue | | exec

• getValue can return any specific arbitrary concrete type, depending on the arguments used (Optional).
• Gatabase Sugar can return very specific concrete types (Optional).

Install

• nimble install gatabase

Comments

-- SQL Comments are supported, but stripped when build for Release. This is SQL.

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

`--` "SQL Comments are supported, but stripped when build for Release. This is Nim."

SELECT & FROM

SELECT *
FROM sometable

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

select '*'
`from` "sometable"

---

SELECT somecolumn
FROM sometable

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

select "somecolumn"
`from` "sometable"

---

SELECT DISTINCT somecolumn

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

selectdistinct "somecolumn"

MIN & MAX

SELECT MIN(somecolumn)

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

selectmin "somecolumn"

---

SELECT MAX(somecolumn)

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

selectmax "somecolumn"

COUNT & AVG & SUM

SELECT COUNT(somecolumn)

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

selectcount "somecolumn"

---

SELECT AVG(somecolumn)

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

selectavg "somecolumn"

---

SELECT SUM(somecolumn)

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

selectsum "somecolumn"

TRIM & LOWER

SELECT trim(lower(somestringcolumn))

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

selecttrim "somestringcolumn"

ROUND

SELECT round(somefloatcolumn, 2)

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

selectround2 "somefloatcolumn"

---

SELECT round(somefloatcolumn, 4)

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

selectround4 "somefloatcolumn"

---

SELECT round(somefloatcolumn, 6)

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

selectround6 "somefloatcolumn"

TOP

SELECT TOP 5 *

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

selecttop 5

WHERE

SELECT somecolumn
FROM sometable
WHERE power > 9000

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

select "somecolumn"
`from` "sometable"
where "power > 9000"

LIMIT & OFFSET

OFFSET 9
LIMIT 42

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

offset 9
limit 42

INSERT

INSERT INTO person
VALUES (42, 'Nikola Tesla', true, 'nikola.tesla@nim-lang.org', 9.6)

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

insertinto "person"
values 5

Example:

insertinto "person"
values 5

⬆️ Nim ⬆️          ⬇️ Generated SQL ⬇️

INSERT INTO person
VALUES ( ?, ?, ?, ?, ? )

• The actual values are passed via varargs directly using stdlib, Gatabase does not format values ever.
• Nim code values 5 generates VALUES ( ?, ?, ?, ?, ? ).

UPDATE

UPDATE person
SET name = 'Nikola Tesla