Skip to content

juancarlospaco/nim-gatabase

Repository files navigation

Gatabase

screenshot

screenshot

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

Use

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

Install

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, '[email protected]', 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', mail = '[email protected]'

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

update "person"
set ["name", "mail"]

Example:

update "person"
set ["name", "mail"]

⬆️ Nim ⬆️          ⬇️ Generated SQL ⬇️

UPDATE person
SET name = ?, mail = ?
  • The actual values are passed via varargs directly using stdlib, Gatabase does not format values ever.
  • Nim code set ["key", "other", "another"] generates SET key = ?, other = ?, another = ?.

DELETE

DELETE debts

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

delete "debts"

ORDER BY

ORDER BY ASC

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

orderby "asc"

ORDER BY DESC

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

orderby "desc"

CASE

CASE
  WHEN foo > 10 THEN 9
  WHEN bar < 42 THEN 5
  ELSE 0
END

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

`case` {
  "foo > 10": "9",
  "bar < 42": "5",
  "else":     "0"
}

COMMENT

COMMENT ON TABLE myTable IS 'This is an SQL COMMENT on a TABLE'

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

commentontable {"myTable": "This is an SQL COMMENT on a TABLE"}

COMMENT ON COLUMN myColumn IS 'This is an SQL COMMENT on a COLUMN'

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

commentoncolumn {"myColumn": "This is an SQL COMMENT on a COLUMN"}

COMMENT ON DATABASE myDatabase IS 'This is an SQL COMMENT on a DATABASE'

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

commentondatabase {"myDatabase": "This is an SQL COMMENT on a DATABASE"}

GROUP BY

GROUP BY country

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

groupby "country"

JOIN

FULL JOIN tablename

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

fulljoin "tablename"

INNER JOIN tablename

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

innerjoin "tablename"

LEFT JOIN tablename

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

leftjoin "tablename"

RIGHT JOIN tablename

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

rightjoin "tablename"

HAVING

HAVING beer > 5

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

having "beer > 5"

UNION

UNION ALL

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

union true

UNION

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

union false

INTERSECT

INTERSECT ALL

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

intersect true

INTERSECT

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

intersect false

EXCEPT

EXCEPT ALL

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

`except` true

EXCEPT

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

`except` false

IS NULL

IS NULL

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

isnull true

IS NOT NULL

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

isnull false

DROP TABLE

DROP TABLE IF EXISTS tablename

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

dropTable "tablename"

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,
);

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

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,
);

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

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),
);

⬆️ SQL ⬆️          ⬇️ Nim ⬇️

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


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 AscNullsLast

Fails 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 AscNullsLast
  • let 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 1

Then you do getMemes() when you need it❕. The API that fits your ideas.

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)

⬆️ CPython 3 + SQLAlchemy ⬆️          ⬇️ Nim 1.0 + Gatabase ⬇️

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 row

Smart SQL Checking

screenshot

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

Requisites

  • None.

Stars

Stars over time

FAQ

  • This is not an ORM ?.

Wikipedia defines ORM as:

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.

⬆️ ⬆️ ⬆️ ⬆️