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
letandconst. - Static Connection Pooling Array with 100+ ORM Queries.
- Uses only
system.nim, everything done viatemplate/macro,strutilsis 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.✅ CASEwith multipleWHENand 1ELSEwith 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 asDELETE FROMwithout aWHERE.WHERE IN,WHERE NOT IN, because is the same asJOIN, butJOINis 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 |
getValuecan return any specific arbitrary concrete type, depending on the arguments used (Optional).- Gatabase Sugar can return very specific concrete types (Optional).
Install
Comments
-- SQL Comments are supported, but stripped when build for Release. This is SQL.`--` "SQL Comments are supported, but stripped when build for Release. This is Nim."SELECT & FROM
SELECT *
FROM sometableselect '*'
`from` "sometable"SELECT somecolumn
FROM sometableselect "somecolumn"
`from` "sometable"SELECT DISTINCT somecolumnselectdistinct "somecolumn"MIN & MAX
SELECT MIN(somecolumn)selectmin "somecolumn"SELECT MAX(somecolumn)selectmax "somecolumn"COUNT & AVG & SUM
SELECT COUNT(somecolumn)selectcount "somecolumn"SELECT AVG(somecolumn)selectavg "somecolumn"SELECT SUM(somecolumn)selectsum "somecolumn"TRIM & LOWER
SELECT trim(lower(somestringcolumn))selecttrim "somestringcolumn"ROUND
SELECT round(somefloatcolumn, 2)selectround2 "somefloatcolumn"SELECT round(somefloatcolumn, 4)selectround4 "somefloatcolumn"SELECT round(somefloatcolumn, 6)selectround6 "somefloatcolumn"TOP
SELECT TOP 5 *selecttop 5WHERE
SELECT somecolumn
FROM sometable
WHERE power > 9000select "somecolumn"
`from` "sometable"
where "power > 9000"LIMIT & OFFSET
OFFSET 9
LIMIT 42offset 9
limit 42INSERT
INSERT INTO person
VALUES (42, 'Nikola Tesla', true, '[email protected]', 9.6)insertinto "person"
values 5Example:
insertinto "person"
values 5INSERT INTO person
VALUES ( ?, ?, ?, ?, ? )- The actual values are passed via
varargsdirectly using stdlib, Gatabase does not format values ever. - Nim code
values 5generatesVALUES ( ?, ?, ?, ?, ? ).
UPDATE
UPDATE person
SET name = 'Nikola Tesla', mail = '[email protected]'update "person"
set ["name", "mail"]Example:
update "person"
set ["name", "mail"]UPDATE person
SET name = ?, mail = ?- The actual values are passed via
varargsdirectly using stdlib, Gatabase does not format values ever. - Nim code
set ["key", "other", "another"]generatesSET key = ?, other = ?, another = ?.
DELETE
DELETE debtsdelete "debts"ORDER BY
ORDER BY ASCorderby "asc"ORDER BY DESCorderby "desc"CASE
CASE
WHEN foo > 10 THEN 9
WHEN bar < 42 THEN 5
ELSE 0
END`case` {
"foo > 10": "9",
"bar < 42": "5",
"else": "0"
}COMMENT
COMMENT ON TABLE myTable IS 'This is an SQL COMMENT on a TABLE'commentontable {"myTable": "This is an SQL COMMENT on a TABLE"}COMMENT ON COLUMN myColumn IS 'This is an SQL COMMENT on a COLUMN'commentoncolumn {"myColumn": "This is an SQL COMMENT on a COLUMN"}COMMENT ON DATABASE myDatabase IS 'This is an SQL COMMENT on a DATABASE'commentondatabase {"myDatabase": "This is an SQL COMMENT on a DATABASE"}GROUP BY
GROUP BY countrygroupby "country"JOIN
FULL JOIN tablenamefulljoin "tablename"INNER JOIN tablenameinnerjoin "tablename"LEFT JOIN tablenameleftjoin "tablename"RIGHT JOIN tablenamerightjoin "tablename"HAVING
HAVING beer > 5having "beer > 5"UNION
UNION ALLunion trueUNIONunion falseINTERSECT
INTERSECT ALLintersect trueINTERSECTintersect falseEXCEPT
EXCEPT ALL`except` trueEXCEPT`except` falseIS NULL
IS NULLisnull trueIS NOT NULLisnull falseDROP TABLE
DROP TABLE IF EXISTS tablenamedropTable "tablename"dropTableis part of Gatabase Sugar (Optional).
CREATE TABLE
CREATE TABLE IF NOT EXISTS kitten(
id INTEGER PRIMARY KEY,
age INTEGER NOT NULL DEFAULT 1,
sex VARCHAR(1) NOT NULL DEFAULT 'f',
name TEXT NOT NULL DEFAULT 'fluffy',
rank REAL NOT NULL DEFAULT 3.14,
);let myTable = createTable "kitten": [
"age" := 1,
"sex" := 'f',
"name" := "fluffy",
"rank" := 3.14,
]No default values:
CREATE TABLE IF NOT EXISTS kitten(
id INTEGER PRIMARY KEY,
age INTEGER,
sex VARCHAR(1),
name TEXT,
rank REAL,
);let myTable = createTable "kitten": [
"age" := int,
"sex" := char,
"name" := string,
"rank" := float,
]More examples:
CREATE TABLE IF NOT EXISTS kitten(
id INTEGER PRIMARY KEY,
age INTEGER NOT NULL DEFAULT 1,
sex VARCHAR(1),
);let myTable = createTable "kitten": [
"age" := 1,
"sex" := char,
]And more examples: https://github.com/juancarlospaco/nim-gatabase/blob/master/examples/database_fields_example.nim#L1
createTableis part of Gatabase Sugar (Optional).
Wildcards
- Nim
'*'➡️ SQL*. - Nim
'?'➡️ SQL?.
Anti-Obfuscation
Gatabase wont like Obfuscation, its code is easy to read and similar to Pretty-Printed SQL. nimpretty friendly. Very KISS.
Compiles Ok:
let variable = sqls:
select '*'
`from` "clients"
groupby "country"
orderby AscNullsLastFails to Compile:
let variable = sqls: select('*') from("clients") groupby("country") orderby(AscNullsLast)let variable = sqls: '*'.select() "clients".from() "country".groupby() AscNullsLast.orderby()let variable = sqls: select '*' from "clients" groupby "country" orderby AscNullsLastlet variable = sqls:select'*' from"clients" groupby"country" orderby AscNullsLast
This helps on big projects where each developer tries to use a different code style.
Your data, your way
Nim has template is like a literal copy&paste of code in-place with no performance cost,
that allows you to create your own custom ORM function callbacks on-the-fly,
like the ones used on scripting languages.
template getMemes(): string =
result = [].getValue:
select "url"
`from` "memes"
limit 1Then you do getMemes() when you need it
From this MyClass.meta.Session.query(Memes).all().filter().first() to this getMemes().
For Python Devs
Remember on Python2 you had like print "value"?, on Nim you can do the same for any function,
then we made functions to mimic basic standard SQL, like select "value" and it worked,
its Type-Safe and valid Nim code, you have an ORM that gives you the freedom and power,
this allows to support interesting features, like CASE, UNION, INTERSECT, COMMENT, etc.
When you get used to template it requires a lot less code to do the same than SQLAlchemy.
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy import Column, Integer, String, Boolean, Float
engine = create_engine("sqlite:///:memory:", echo=False)
engine.execute("""
create table if not exists person(
id integer primary key,
name varchar(9) not null unique,
active bool not null default true,
rank float not null default 0.0
); """
)
meta = MetaData()
persons = Table(
"person", meta,
Column("id", Integer, primary_key = True),
Column("name", String, nullable = False, unique = True),
Column("active", Boolean, nullable = False, default = True),
Column("rank", Float, nullable = False, default = 0.0),
)
conn = engine.connect()
ins = persons.insert()
ins = persons.insert().values(id = 42, name = "Pepe", active = True, rank = 9.6)
result = conn.execute(ins)
persons_query = persons.select()
result = conn.execute(persons_query)
row = result.fetchone()
print(row)import db_sqlite, gatabase
let db = open(":memory:", "", "", "")
db.exec(sql"""
create table if not exists person(
id integer primary key,
name varchar(9) not null unique,
active bool not null default true,
rank float not null default 0.0
); """)
exec [42, "Pepe", true, 9.6]:
insertinto "person"
values 4
let row = [].getRow:
select '*'
`from` "person"
echo rowSmart SQL Checking
It will perform a SQL Syntax checking at compile-time. Examples here Fail intentionally as expected:
exec []:
where "failure"Fails to compile as expected, with a friendly error:
gatabase.nim(48, 16) Warning: WHERE without SELECT nor INSERT nor UPDATE nor DELETE.
Typical error of making a DELETE FROM without WHERE that deletes all your data:
exec []:
delete "users"Compiles but prints a friendly warning:
gatabase.nim(207, 57) Warning: DELETE FROM without WHERE.
Typical bad practice of using SELECT * everywhere:
exec []:
select '*'Compiles but prints a friendly warning:
gatabase.nim(20, 50) Warning: SELECT * is bad practice.
Non-SQL wont compile, even if its valid Nim:
sqls:
discard
sqls:
echo "This is not SQL, wont compile"Gatabase Diagrams
Tests
$ nimble test
[Suite] Gatabase ORM Tests
[OK] let INSERT INTO
[OK] let SELECT ... FROM ... WHERE
[OK] let SELECT ... (comment) ... FROM ... COMMENT
[OK] let SELECT ... FROM ... LIMIT ... OFFSET
[OK] let INSERT INTO
[OK] let UNION ALL ... ORBER BY ... IS NOT NULL
[OK] let SELECT DISTINCT ... FROM ... WHERE
[OK] let INSERT INTO
[OK] const SELECT ... FROM ... WHERE
[OK] const SELECT ... (comment) ... FROM ... COMMENT
[OK] const SELECT ... FROM ... LIMIT ... OFFSET
[OK] const INSERT INTO
[OK] const UNION ALL ... ORBER BY ... IS NOT NULL
[OK] const INTERSECT ALL
[OK] const EXCEPT ALL
[OK] const SELECT DISTINCT ... FROM ... WHERE
[OK] var CASE
[OK] var SELECT MAX .. WHERE EXISTS ... OFFSET ... LIMIT ... ORDER BY
[OK] SELECT TRIM
[OK] SELECT ROUND
[OK] var DELETE FROM WHERE- Tests use a real database SQLite on RAM
":memory:"with a"person"table. +20 Tests. - CI uses GitHub Actions CI.
Requisites
- None.
Stars
FAQ
- This is not an ORM ?.
Object-relational mapping in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages.
Feel free to contribute to Wikipedia.
- Supports SQLite ?.
Yes.
- Supports MySQL ?.
No.
- Will support MySQL someday ?.
No.
- Supports Mongo ?.
No.
- Will support Mongo someday ?.
No.
- How is Parameter substitution done ?.
It does NOT make Parameter substitution internally, its delegated to standard library.
- This works with Synchronous code ?.
Yes.
- This works with Asynchronous code ?.
Yes.
- SQLite mode dont support some stuff ?.
We try to keep as similar as possible, but SQLite is very limited.