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

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

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

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 QueryData {
pub:
	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:
	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:
	name        string
	typ         int
	nullable    bool
	default_val string
	attrs       []VAttribute
	is_arr      bool
}