TQL

SELECT * FROM Table

Table.all()

SELECT * FROM Table WHERE field1 = 'value1'

Table.filter(field1 == "value1")

SELECT * FROM Table WHERE primary_key = 42

Table.get(42)

/// Shortcut for:

Table.filter(primary_key == 42)[0..1];

SELECT * FROM Table WHERE field1 = 'value1'

Table.get(field1 == "value1")

/// Shortcut for:

Table.filter(field1 == "value1")[0..1];

SELECT * FROM Table WHERE field1 = 'value1' AND field2 < 100

Table.filter(field1 == "value1" && field2 < 100)

SELECT * FROM Table WHERE field1 = 'value1' OR field2 < 100

Table.filter(field1 == "value1" || field2 < 100)

SELECT * FROM Table ORDER BY field1

Table.sort(field1)

SELECT * FROM Table ORDER BY field1 DESC

Table.sort(-field1)

SELECT * FROM Table LIMIT 0, 20

Table[0..20]

SELECT * FROM Table
WHERE field1 = 'value1'
  AND field2 < 100
ORDER BY field2 DESC
LIMIT 10, 20

Table.filter(field1 == "value1" && field2 < 100)
    .sort(-field2)[10..20]

INSERT INTO Table(field1, field2) VALUES('value1', 55)

Table.insert(field1 = "value1", field2 = 55)

UPDATE Table SET field1 = 'value1', field2 = 55 WHERE id = 1

Table.get(1).update(field1 = "value1", field2 = 55);

/// or

Table.filter(id == 1).update(field1 = "value1", field2 = 55);

DELETE FROM Table WHERE id = 1

Table.get(1).delete();

/// or

Table.filter(id == 1).delete()

SELECT AVG(field2) FROM Table

Table.aggregate(avg(field2))

SELECT AVG(field1) FROM Table1 GROUP BY field2

Table1.values(field2).annotate(avg(field1))

SELECT AVG(field1) as average FROM Table1
GROUP BY field2
HAVING average > 5

Table1.values(field2).annotate(average = avg(field1))
    .filter(average > 5)

SELECT AVG(field1) as average FROM Table1
WHERE field1 < 10
GROUP BY field2
HAVING average > 5

Table1.filter(field1 < 10).values(field2)
    .annotate(average = avg(field1)).filter(average > 5)

SELECT Table1.field1, Table2.field1 FROM Table1
INNER JOIN Table2 ON Table1.pk = Table2.fk

#[SqlTable]
struct Table1 {
    pk: PrimaryKey,
    field1: i32,
}

#[SqlTable]
struct Table2 {
    field1: i32,
    fk: ForeignKey<Table1>,
}

Table1.all().join(Table2)

SELECT * FROM Table1 WHERE YEAR(date) = 2015

Table1.filter(date.year() == 2015)

SELECT * FROM Table1 WHERE INSTR(field1, 'string') > 0

Table1.filter(field1.contains("string"))

SELECT * FROM Table1 WHERE field1 LIKE 'string%'

Table1.filter(field1.starts_with("string"))

SELECT * FROM Table1 WHERE field1 LIKE '%string'

Table1.filter(field1.ends_with("string"))

SELECT * FROM Table1 WHERE field1 IS NULL

Table1.filter(field1.is_none())

SELECT * FROM Table1 WHERE field1 REGEXP BINARY '\^[a-d]'

Table1.filter(field1.match(r"\^[a-d]"))

SELECT * FROM Table1 WHERE field1 REGEXP '\^[a-d]'

Table1.filter(field1.imatch(r"\^[a-d]"))

CREATE TABLE IF NOT EXISTS Table1 (
    pk INTEGER NOT NULL AUTO_INCREMENT,
    field1 INTEGER,
    PRIMARY KEY (pk)
)

#[SqlTable]
struct Table1 {
    pk: PrimaryKey,
    field1: i32,
}

Table1.create()