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

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

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
}!

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. 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 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 string, 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(table string, 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 name 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

interface Connection {
mut:
	select(config SelectConfig, data QueryData, where QueryData) ![][]Primitive
	insert(table string, data QueryData) !
	update(table string, data QueryData, where QueryData) !
	delete(table string, where QueryData) !
	create(table string, fields []TableField) !
	drop(table string) !
	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

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

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 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]) 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 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
}

enum OrderType {
	asc
	desc
}

enum SQLDialect {
	default
	sqlite
}

enum StmtKind {
	insert
	update
	delete
}

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

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 SelectConfig {
pub mut:
	table      string
	is_count   bool
	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
	fields     []string
	types      []int
}

table - Table name is_count - Either the data will be returned or an integer with the count 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

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