ORM

V has a powerful, concise ORM baked in! Create tables, insert records, manage relationships, all regardless of the DB driver you decide to use.

Nullable

For a nullable column, use an option field. If the field is non-option, the column will be defined with NOT NULL at table creation.

struct Foo {
    notnull  string
    nullable ?string
}

Attributes

Structs

• [table: 'name'] explicitly sets the name of the table for the struct
• [comment: 'table_comment'] explicitly sets the comment of the table for the struct
• [index: 'f1, f2, f3'] explicitly sets fields of the table (f1, f2, f3) as indexed

Fields

• [primary] sets the field as the primary key

• [unique] gives the field a UNIQUE constraint

• [unique: 'foo'] adds the field to a UNIQUE group

• [skip] or [sql: '-'] field will be skipped

• [sql: type] where type is a V type such as int or f64

• [serial] or [sql: serial] lets the DB backend choose a column type for an auto-increment field

• [sql: 'name'] sets a custom column name for the field

• [sql_type: 'SQL TYPE'] explicitly sets the type in SQL

• [default: 'raw_sql'] inserts raw_sql verbatim in a "DEFAULT" clause whencreating a new table, allowing for SQL functions like CURRENT_TIME. For raw strings, surround raw_sql with backticks (`).

• [fkey: 'parent_id'] sets foreign key for an field which holds an array

• [references] or [references: 'tablename'] or [references: 'tablename(field_id)']

• [comment: 'field_comment'] set comment

• [index] creates index

Usage

[!NOTE] > For using the Function Call API for orm, please check Function Call API.

Here are a couple example structs showing most of the features outlined above.

import time

@[table: 'foos']
struct Foo {
    id          int         @[primary; sql: serial]
    name        string
    created_at  time.Time   @[default: 'CURRENT_TIME']
    updated_at  ?string     @[sql_type: 'TIMESTAMP']
    deleted_at  ?time.Time
    children    []Child     @[fkey: 'parent_id']
}

struct Child {
    id        int    @[primary; sql: serial]
    parent_id int
    name      string
}

To use the ORM, there is a special interface that lets you use the structs and V itself in queries. This interface takes the database instance as an argument.

import db.sqlite

db := sqlite.connect(':memory:')!

sql db {
    // query; see below
}!

When you need to reference the table, simply pass the struct itself.

import models.Foo

struct Bar {
    id int @[primary; sql: serial]
}

sql db {
    create table models.Foo
    create table Bar
}!

Create & Drop Tables

You can create and drop tables by passing the struct to create table and drop table.

import models.Foo

struct Bar {
    id int @[primary; sql: serial]
}

sql db {
    create table models.Foo
    drop table Bar
}!

Insert Records

To insert a record, create a struct and pass the variable to the query. Again, reference the struct as the table.

foo := Foo{
    name:       'abc'
    created_at: time.now()
    // updated_at defaults to none
    // deleted_at defaults to none
    children: [
        Child{
            name: 'abc'
        },
        Child{
            name: 'def'
        },
    ]
}

foo_id := sql db {
    insert foo into Foo
}!

If the id field is marked as sql: serial and primary, the insert expression returns the database ID of the newly added object. Getting an ID of a newly added DB row is often useful.

When inserting, [sql: serial] fields, and fields with a [default: 'raw_sql'] attribute, are not sent to the database when the value being sent is the default for the V struct field (e.g., 0 int, or an empty string). This allows the database to insert default values for auto-increment fields and where you have specified a default.

Select

You can select rows from the database by passing the struct as the table, and use V syntax and functions for expressions. Selecting returns an array of the results.

result := sql db {
    select from Foo where id == 1
}!

foo := result.first()

result := sql db {
    select from Foo where id > 1 && name != 'lasanha' limit 5
}!

result := sql db {
    select from Foo where id > 1 order by id desc
}!

ORM select expressions also support built-in aggregate functions. count keeps its legacy syntax, while the other aggregates use SQL-like function calls.

total_age := sql db {
    select sum(age) from Foo
}!

average_age := sql db {
    select avg(age) from Foo where id > 1
}!

lowest_name := sql db {
    select min(name) from Foo
}!

highest_created_at := sql db {
    select max(created_at) from Foo
}!

sum, avg, min, and max return options so empty result sets can surface SQL NULL as none. count continues to return int.

Transactions

ORM transactions work with both sql tx {} and the function-call API.

import orm

orm.transaction[int](mut db, fn (mut tx orm.Tx) !int {
    user := User{
        name: 'Alice'
    }
    sql tx {
        insert user into User
    }!
    return tx.last_id()
})!

For manual control, start a transaction explicitly and commit or roll it back yourself.

import orm

mut tx := orm.begin(mut db)!
sql tx {
    update User set name = 'Bob' where id == 1
}!
tx.commit()!

Nested transactions use savepoints instead of a second BEGIN.

import orm

orm.transaction[int](mut db, fn (mut tx orm.Tx) !int {
    tx.transaction[int](fn (mut nested orm.Tx) !int {
        sql nested {
            delete from User where id == 2
        }!
        return 0
    })!
    return 0
})!

Transaction helpers use each driver's default transaction mode. v1 does not expose isolation levels or SQLite begin-mode configuration yet.

Update

You can update fields in a row using V syntax and functions. Again, pass the struct as the table.

sql db {
    update Foo set updated_at = time.now() where name == 'abc' && updated_at is none
}!

Note that is none and !is none can be used to select for NULL fields.

Delete

You can delete rows using V syntax and functions. Again, pass the struct as the table.

sql db {
    delete from Foo where id > 10
}!

time.Time Fields

It's definitely useful to cast a field as time.Time so you can use V's built-in time functions; however, this is handled a bit differently than expected in the ORM. time.Time fields are created as integer columns in the database. Because of this, the usual time functions (current_timestamp, NOW(), etc) in SQL do not work as defaults.

Example

import db.pg

struct Member {
    id         string @[default: 'gen_random_uuid()'; primary; sql_type: 'uuid']
    name       string
    created_at string @[default: 'CURRENT_TIMESTAMP'; sql_type: 'TIMESTAMP']
}

fn main() {
    db := pg.connect(pg.Config{
        host: 'localhost'
        port: 5432
        user: 'user'
        password: 'password'
        dbname: 'dbname'
    })!

    defer {
        db.close()
    }

    sql db {
        create table Member
    }!

    new_member := Member{
        name: 'John Doe'
    }

    sql db {
        insert new_member into Member
    }!

    selected_members := sql db {
        select from Member where name == 'John Doe' limit 1
    }!
    john_doe := selected_members.first()

    sql db {
        update Member set name = 'Hitalo' where id == john_doe.id
    }!
}

Function Call API

You can utilize the Function Call API to work with ORM. It provides the capability to dynamically construct SQL statements. The Function Call API supports common operations such as Create Table/Drop Table/Insert/Delete/Update/Select, and offers convenient yet powerful features for constructing WHERE clauses, SET clauses, SELECT clauses, and more.

A complete example is available here.

Below, we illustrate its usage through several examples.

​​1. Define your struct​​ with the same method definitions as before:

@[table: 'sys_users']
struct User {
    id      int      @[primary;serial]
    name    string
    age     int
    role    string
    status  int
    salary  int
    title   string
    score   int
    created_at ?time.Time @[sql_type: 'TIMESTAMP']
}

​​2. Create a database connection​​:

    mut db := sqlite.connect(':memory:')!
    defer { db.close() or {} }

3. Create a QueryBuilder​​ (which also completes struct mapping):

    mut qb := orm.new_query[User](db)

4. Create a database table​​:

    qb.create()!

5. Insert multiple records​​ into the table:

    qb.insert_many(users)!

6. Delete records​​ (note: delete() must follow where()):

    qb.where('name = ?','John')!.delete()!

7. Query records​​ (you can specify fields of interest via select):

// Returns []User with only 'name' populated; other fields are zero values.
    only_names := qb.select('name')!.query()!

8. Update records​​ (note: update() must be placed last):

    qb.set('age = ?, title = ?', 71, 'boss')!.where('name = ?','John')!.update()!

9. Query aggregate values​​:

    total_age := qb.sum('age')!
    average_score := qb.avg('score')!
    first_name := qb.min('name')!
    latest_created_at := qb.max('created_at')!
    count := qb.count()!

    assert total_age.as_int()? == 42
    assert average_score.as_f64()? == 9.5
    assert first_name.as_string()? == 'Alice'
    assert latest_created_at.as_time()? == created_at

sum, avg, min, and max return an AggregateValue. Use as_int(), as_f64(), as_string(), or as_time() to unwrap the typed value, or check has_value for empty result sets. count returns int.

9. Drop the table​​:

    qb.drop()!

10. Chainable method calls​​: Most Function Call API support chainable calls, allowing easy method chaining:

    final_users :=
    qb
        .drop()!
        .create()!
        .insert_many(users)!
        .set('name = ?', 'haha')!.where('name = ?', 'Tom')!.update()!
        .where('age >= ?', 30)!.delete()!
        .query()!

11. Writing complex nested WHERE clauses​​: The API includes a built-in parser to handle intricate WHERE clause conditions. For example:

    where('created_at IS NULL && ((salary > ? && age < ?) || (role LIKE ?))', 2000, 30, '%employee%')!

Note the use of placeholders ?. The conditional expressions support logical operators including AND, OR, ||, and &&.

fn apply_tenant_filter(table Table, where QueryData) QueryData

apply_tenant_filter appends the configured tenant filter condition to where.

fn begin(mut conn TransactionalConnection) !Tx

begin starts a new ORM transaction on the provided connection.

fn clear_current_tenant_id()

clear_current_tenant_id clears the current tenant id used by global tenant filtering.

fn configure_tenant_filter(config TenantFilterConfig)

configure_tenant_filter configures the global ORM tenant filter behavior.

fn new_query[T](conn Connection) &QueryBuilder[T]

new_query create a new query object for struct T

fn orm_select_gen(cfg SelectConfig, q string, num bool, qm string, start_pos int, where QueryData) string

Generates an sql select stmt, from universal parameter orm - See SelectConfig q, num, qm, start_pos - see orm_stmt_gen where - See QueryData

fn orm_stmt_gen(sql_dialect SQLDialect, table Table, q string, kind StmtKind, num bool, qm string,
	start_pos int, data QueryData, where QueryData) (string, QueryData)

Generates an sql stmt, from universal parameter q - The quotes character, which can be different in every type, so it's variable num - Stmt uses nums at prepared statements (? or ?1) qm - Character for prepared statement (qm for question mark, as in sqlite) start_pos - When num is true, it's the start position of the counter

fn orm_table_gen(sql_dialect SQLDialect, table Table, q string, defaults bool, def_unique_len int, fields []TableField, sql_from_v fn (int) !string,
	alternative bool) !string

Generates an sql table stmt, from universal parameter table - Table struct q - see orm_stmt_gen defaults - enables default values in stmt def_unique_len - sets default unique length for texts fields - See TableField sql_from_v - Function which maps type indices to sql type names alternative - Needed for msdb

fn set_current_tenant_id(tenant_id Primitive)

set_current_tenant_id sets the current tenant id used by global tenant filtering.

fn set_tenant_filter_enabled(enabled bool)

set_tenant_filter_enabled enables or disables global tenant filtering.

fn transaction[T](mut conn TransactionalConnection, f fn (mut Tx) !T) !T

transaction runs a callback inside a transaction and commits or rolls back automatically.

fn with_tenant[T](tenant_id Primitive, callback fn () !T) !T

with_tenant executes callback with a temporary tenant id and enabled tenant filtering.

fn with_tenant_value[T](tenant_id Primitive, callback fn () T) T

with_tenant_value executes callback with a temporary tenant id and enabled tenant filtering.

fn without_tenant_filter[T](callback fn () !T) !T

without_tenant_filter executes callback with tenant filtering temporarily disabled.

fn without_tenant_filter_value[T](callback fn () T) T

without_tenant_filter_value executes callback with tenant filtering temporarily disabled.

interface Connection {
mut:
	select(config SelectConfig, data QueryData, where QueryData) ![][]Primitive
	insert(table Table, data QueryData) !
	update(table Table, data QueryData, where QueryData) !
	delete(table Table, where QueryData) !
	create(table Table, fields []TableField) !
	drop(table Table) !
	last_id() int
}

Interfaces gets called from the backend and can be implemented Since the orm supports arrays aswell, they have to be returned too. A row is represented as []Primitive, where the data is connected to the fields of the struct by their index. The indices are mapped with the SelectConfig.field array. This is the mapping for a struct. To have an array, there has to be an array of structs, basically [][]Primitive

Every function without last_id() returns an optional, which returns an error if present last_id returns the last inserted id of the db

interface TransactionalConnection {
	Connection
mut:
	orm_begin() !
	orm_commit() !
	orm_rollback() !
	orm_savepoint(name string) !
	orm_rollback_to(name string) !
	orm_release_savepoint(name string) !
}

TransactionalConnection extends Connection with transaction primitives.

type Primitive = Null
	| bool
	| f32
	| f64
	| i16
	| i64
	| i8
	| int
	| string
	| time.Time
	| u16
	| u32
	| u64
	| u8
	| InfixType
	| []Primitive

fn (qb_ &QueryBuilder[T]) reset() &QueryBuilder[T]

reset reset a query object, but keep the connection and table name

fn (qb_ &QueryBuilder[T]) where(condition string, params ...Primitive) !&QueryBuilder[T]

where create a where clause, it will AND with previous where clause. valid token in the condition include: field's names, operator, (, ), ?, AND, OR, ||, &&, valid operator incldue: =, !=, <>, >=, <=, >, <, LIKE, ILIKE, IS NULL, IS NOT NULL, IN, NOT IN example: where('(a > ? AND b <= ?) OR (c <> ? AND (x = ? OR y = ?))', a, b, c, x, y)

fn (qb_ &QueryBuilder[T]) or_where(condition string, params ...Primitive) !&QueryBuilder[T]

or_where create a where clause, it will OR with previous where clause.

fn (qb_ &QueryBuilder[T]) order(order_type OrderType, field string) !&QueryBuilder[T]

order create a order clause

fn (qb_ &QueryBuilder[T]) limit(limit int) !&QueryBuilder[T]

limit create a limit clause

fn (qb_ &QueryBuilder[T]) offset(offset int) !&QueryBuilder[T]

offset create a offset clause

fn (qb_ &QueryBuilder[T]) select(fields ...string) !&QueryBuilder[T]

select create a select clause

fn (qb_ &QueryBuilder[T]) set(assign string, values ...Primitive) !&QueryBuilder[T]

set create a set clause for update

fn (qb_ &QueryBuilder[T]) query() ![]T

query start a query and return result in struct T

fn (qb_ &QueryBuilder[T]) count() !int

count start a count query and return result

fn (qb_ &QueryBuilder[T]) sum(field string) !AggregateValue

sum returns the sum of the field values as an AggregateValue.

fn (qb_ &QueryBuilder[T]) min(field string) !AggregateValue

min returns the smallest field value as an AggregateValue.

fn (qb_ &QueryBuilder[T]) max(field string) !AggregateValue

max returns the largest field value as an AggregateValue.

fn (qb_ &QueryBuilder[T]) avg(field string) !AggregateValue

avg returns the average field value as an AggregateValue.

fn (qb_ &QueryBuilder[T]) insert[T](value T) !&QueryBuilder[T]

insert insert a record into the database

fn (qb_ &QueryBuilder[T]) insert_many[T](values []T) !&QueryBuilder[T]

insert_many insert records into the database

fn (qb_ &QueryBuilder[T]) update() !&QueryBuilder[T]

update update record(s) in the database

fn (qb_ &QueryBuilder[T]) delete() !&QueryBuilder[T]

delete delete record(s) in the database

fn (qb_ &QueryBuilder[T]) create() !&QueryBuilder[T]

create create a table

fn (qb_ &QueryBuilder[T]) drop() !&QueryBuilder[T]

drop drop a table

fn (qb_ &QueryBuilder[T]) last_id() int

last_id returns the last inserted id of the db

enum AggregateKind {
	none
	count
	sum
	avg
	min
	max
}

enum JoinType {
	inner      // INNER JOIN - returns only matching rows
	left       // LEFT JOIN - returns all left rows, NULL for non-matching right
	right      // RIGHT JOIN - returns all right rows, NULL for non-matching left
	full_outer // FULL OUTER JOIN - returns all rows from both tables
}

JoinType represents the type of SQL JOIN operation

enum MathOperationKind {
	add // +
	sub // -
	mul // *
	div // /
}

enum OperationKind {
	neq         // !=
	eq          // ==
	gt          // >
	lt          // <
	ge          // >=
	le          // <=
	orm_like    // LIKE
	orm_ilike   // ILIKE
	is_null     // IS NULL
	is_not_null // IS NOT NULL
	in          // IN
	not_in      // NOT IN
}

enum OrderType {
	asc
	desc
}

enum SQLDialect {
	default
	mysql
	pg
	sqlite
}

enum StmtKind {
	insert
	update
	delete
}

struct AggregateValue {
pub:
	has_value bool
	value     Primitive = Null{}
}

fn (value AggregateValue) as_int() ?int

as_int returns the aggregate value as int, or none when it is null or not numeric.

fn (value AggregateValue) as_f64() ?f64

as_f64 returns the aggregate value as f64, or none when it is null or not numeric.

fn (value AggregateValue) as_string() ?string

as_string returns the aggregate value as string, or none when it is null or not a string.

fn (value AggregateValue) as_time() ?time.Time

as_time returns the aggregate value as time.Time, or none when it is null or not a time.

struct InfixType {
pub:
	name     string
	operator MathOperationKind
	right    Primitive
}

struct JoinConfig {
pub mut:
	kind         JoinType
	table        Table
	on_left_col  string // Column from main table (e.g., 'user_id')
	on_right_col string // Column from joined table (e.g., 'id')
}

JoinConfig holds configuration for a JOIN clause in a SELECT query

struct Null {}

struct QueryBuilder[T] {
pub mut:
	meta                  []TableField
	valid_sql_field_names []string
	conn                  Connection
	config                SelectConfig
	data                  QueryData
	where                 QueryData
}

struct QueryData {
pub mut:
	fields      []string
	data        []Primitive
	types       []int
	parentheses [][]int
	kinds       []OperationKind
	auto_fields []int
	is_and      []bool
}

Examples for QueryData in SQL: abc == 3 && b == 'test' => fields[abc, b]; data[3, 'test']; types[index of int, index of string]; kinds[.eq, .eq]; is_and[true]; Every field, data, type & kind of operation in the expr share the same index in the arrays is_and defines how they're addicted to each other either and or or parentheses defines which fields will be inside () auto_fields are indexes of fields where db should generate a value when absent in an insert

struct Savepoint {
mut:
	inner &SavepointInner = unsafe { nil }
}

Savepoint is a manual savepoint handle created from an active transaction.

fn (mut sp Savepoint) rollback() !

rollback rolls back to the savepoint and releases it.

fn (mut sp Savepoint) release() !

release releases the savepoint without rolling back.

struct SelectConfig {
pub mut:
	table           Table
	aggregate_kind  AggregateKind
	aggregate_field string
	has_where       bool
	has_order       bool
	order           string
	order_type      OrderType
	has_limit       bool
	primary         string = 'id' // should be set if primary is different than 'id' and 'has_limit' is false
	has_offset      bool
	has_distinct    bool
	fields          []string
	types           []int
	joins           []JoinConfig // JOIN clauses for this query
}

table - Table struct aggregate_kind - Select rows or return a single aggregate value has_where - Select all or use a where expr has_order - Order the results order - Name of the column which will be ordered order_type - Type of order (asc, desc) has_limit - Limits the output data primary - Name of the primary field has_offset - Add an offset to the result fields - Fields to select types - Types to select joins - JOIN clauses for this query

struct Table {
pub mut:
	name  string
	attrs []VAttribute
}

struct TableField {
pub mut:
	name        string
	typ         int
	nullable    bool
	default_val string
	attrs       []VAttribute
	is_arr      bool
}

struct TenantFilterConfig {
pub:
	enabled    bool   = true
	field_name string = default_tenant_filter_field_name
}

struct Tx {
mut:
	inner &TxInner = unsafe { nil }
}

Tx is an ORM transaction handle that can be used anywhere an orm.Connection is accepted.

fn (mut tx Tx) commit() !

commit commits an active transaction.

fn (mut tx Tx) rollback() !

rollback rolls back an active transaction.

fn (mut tx Tx) savepoint() !Savepoint

savepoint creates a manual savepoint inside an active transaction.

fn (mut tx Tx) transaction[T](f fn (mut Tx) !T) !T

transaction runs a nested transaction backed by a savepoint.

fn (mut tx Tx) select(config SelectConfig, data QueryData, where QueryData) ![][]Primitive

select forwards ORM select queries through the active transaction.

fn (mut tx Tx) insert(table Table, data QueryData) !

insert forwards ORM insert queries through the active transaction.

fn (mut tx Tx) update(table Table, data QueryData, where QueryData) !

update forwards ORM update queries through the active transaction.

fn (mut tx Tx) delete(table Table, where QueryData) !

delete forwards ORM delete queries through the active transaction.

fn (mut tx Tx) create(table Table, fields []TableField) !

create forwards ORM create queries through the active transaction.

fn (mut tx Tx) drop(table Table) !

drop forwards ORM drop queries through the active transaction.

fn (mut tx Tx) last_id() int

last_id forwards the last inserted id through the wrapped connection.