Driver authors using the shared SQL generators can target SQLite, PostgreSQL, MySQL, H2 and vsql connections with the built-in ORM dialect helpers. For .vsql, use orm.orm_select_gen_with_dialect, which spells a limit and an offset as OFFSET n ROWS FETCH FIRST n ROWS ONLY, and number the placeholders (num: true). orm.orm_table_gen then leaves out IF NOT EXISTS and indexes, which vsql does not have, and the comment before a @[unique: 'group'] constraint. A @[default] must be a value vsql accepts as a column default, such as a literal or CURRENT_TIMESTAMP.

Deprecation notice: orm_fn

Warning

The Function Call API (orm_fn; orm.new_query[T] / QueryBuilder) is deprecated and will be removed from the standard library after 2027-08-17, to be maintained in a separate repository. Prefer the built-in sql ORM syntax for new code. Compiler deprecation warnings begin on 2027-02-18, six months before the removal date; until then the compiler emits a migration notice. See https://github.com/vlang/v/issues/27001 for details.

Constructing infix expressions

orm.InfixType.right has type &orm.Primitive. When constructing an expression directly, store the operand as an orm.Primitive and pass its address:

import orm

operand := orm.Primitive(2)
expression := orm.InfixType{
    name: 'stock'
    operator: .add
    right: &operand
}

Code that previously assigned a Primitive value to right should use &operand. Read the operand with *expression.right. The reference gives recursive expressions a finite layout; SQL arithmetic and parameter binding keep their existing behavior.

Inserting records

Bind a struct literal to a variable before inserting it:

product := Product{name: 'b'}
sql db {
    insert product into Product
}!

An inline struct literal after insert is rejected during compilation, including a qualified literal imported from another module. A qualified table type also supports module names that coincide with SQL keywords, such as into.Product.

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, except scalar foreign keys declared with @[references], which default to nullable so omitted relations can be stored as NULL.

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
[unique_key: 'f1, f2, f3'] adds a composite UNIQUE constraint for the listed fields

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
[sql_select: 'SQL expression'] uses a custom expression in SELECT for the field
[default: 'raw_sql'] inserts raw_sql verbatim in a "DEFAULT" clause when creating a new table, allowing for SQL functions like CURRENT_TIME. A plain string default has to be surrounded with backticks (`), so that it is emitted as a properly quoted (and escaped) SQL string literal instead: [default: '\/dashboard`']producesDEFAULT '/dashboard', while [default: 'CURRENT_TIME']producesDEFAULT CURRENT_TIME. Single quotes are doubled for you. A backslash is kept verbatim, except on MySQL, where a backslash is rejected with an error: its meaning there depends on the server's NO_BACKSLASH_ESCAPESsql_mode, which cannot be known while generating the DDL. Usesql_typewith an explicitDEFAULT` clause for that case.
[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

Queries in array callbacks

sql expressions inside array callbacks can use the current it, including fields such as it.id. Nested callbacks use their own it binding.

Static where values can use function calls, member access and indexing. Structs and containers can be intermediate receivers, but the final value must be a primitive type, an enum or time.Time. For example, make_holders()[0].name can bind a string field, while make_holders()[0] cannot bind the whole struct. Option and Result receivers must be unwrapped with an or fallback before accessing their members or elements. Methods declared on collection aliases, including inherited alias methods, use their declared return types when checking the final value. Private alias methods remain accessible only within their declaring module, following ordinary method visibility rules. Alias conversions also follow type visibility. These checks apply during -check and compilation. Alias methods follow the same visibility and return type rules through pointer receivers. Mutable alias methods also require a receiver eligible under ordinary V mutability rules. Shared alias receivers require the same read or write locks as ordinary method calls.

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
}

sql_select is useful for backend-specific read transformations such as:

geom string @[sql_type: 'geometry'; sql_select: 'ST_AsEWKT(geom)']

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

Data Scope

orm.DB can wrap an orm.Connection with automatic scope filters. Scope filters are useful for request-level tenancy, soft deletes, and row-level access checks.

mut db := orm.new_db(raw_db, orm.DataScope{
    filters: [
        orm.QueryFilter{
            field: 'tenant_id'
            value: orm.Primitive(tenant_id)
            mode:  .dynamic
        },
        orm.QueryFilter{
            field: 'shop_id'
            value: orm.Primitive(shop_id)
            mode:  .dynamic
        },
        orm.QueryFilter{
            field:    'deleted_at'
            operator: .is_null
            mode:     .dynamic
        },
    ]
})

users := sql db {
    select from User
}!

QueryFilter.mode must be explicitly set to .static or .dynamic (there is no default — .unset causes a runtime error). Static filters are reserved for future compiler-generated scope clauses. The runtime orm.DB wrapper applies only filters explicitly marked with mode: .dynamic. Invalid dynamic filters return an error instead of being silently skipped.

Call db.unscoped() to return a new orm.DB value that skips all scope filters. Call db.unscoped('tenant_id') to skip only selected fields.

Raw SQL Execute

Use execute(query string) ![]orm.Row when you need a driver-agnostic raw SQL escape hatch outside the sql db {} DSL. It is part of the public orm.Connection interface, and both orm.DB.execute and orm.Tx.execute forward to the wrapped connection.

rows := db.execute("SELECT 1 AS answer, 'ok' AS status")!
assert rows[0].names == ['answer', 'status']
assert rows[0].vals == ['1', 'ok']

Each orm.Row stores column values in vals and column names in names; both arrays use the same column order. Queries that do not return a result set, such as DDL or DML statements, return an empty row array. Custom orm.Connection implementers must implement execute and should populate Row.names whenever the backend exposes result column metadata.

Tip

This guide uses the built-in db.sqlite module. If you want SQLite without first installing system-level SQLite development files, the V team also maintains the sqlite VPM package.

Install it with v install sqlite and change import db.sqlite to import sqlite. The package keeps the same API while bundling SQLite for you.

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. Table structs from imported modules can use the module name or its import alias as a qualifier. These references count as uses of the import, even when it is used only inside the SQL block.

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

You can insert a flat array of records in one statement. Bulk inserts currently support primitive, enum, and time.Time fields. Rows with serial or default fields fall back to per-row inserts so each row keeps normal default handling.

users := [
    User{
        id:   1
        name: 'Alice'
    },
    User{
        id:   2
        name: 'Bob'
    },
]

sql db {
    insert users into User
}!

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.

Upsert

upsert inserts a row or updates the matching row when one of the table's primary or unique keys already exists.

foo := Foo{
    name: 'abc'
}

sql db {
    upsert foo into Foo
}!

upsert currently supports flat ORM rows with primitive, enum, and time.Time fields.

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()

You can also select a subset of struct fields. The result type stays []Foo; the selected fields are populated and the rest keep their zero values. Use the V struct field names here; @[sql: 'column_name'] and @[sql_select: 'expr'] are still applied automatically.

partial := sql db {
    select id, name from Foo where id > 1
}!

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

Modules referenced in SQL table names or value expressions count as used imports. For example, time.now() inside an ORM update statement uses import time. Names inside ordinary or raw string literals and comments do not count as import usage. String interpolation expressions still count, such as ${time.now()}.

Dynamic ORM blocks can build WHERE and SET data conditionally. Commas between emitted dynamic where items are joined with AND; use && and || inside an item for explicit boolean conditions. Values may use indexed struct fields, such as name == members[0].name or id == members_by_name['Alice'].id.

The formatter preserves conditions, commas and comments in query-data blocks, including standalone sql { ... } declaration assignments while editing.

where_filter := {
    if name := req.name {
        name == name
    },
    id == user_id || tenant_id == tenant_id
}

rows := sql db {
    dynamic select from Foo where where_filter
}!

update_data := {
    name == new_name
}

sql db {
    dynamic update Foo set update_data where {
        id == user_id || tenant_id == tenant_id
    }
}!

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

You can update multiple rows from an array in one statement by using the array variable in each assigned value and in the key comparison.

updates := [
    User{
        id:   1
        name: 'Alicia'
    },
    User{
        id:   2
        name: 'Robert'
    },
]

sql db {
    update User set name = updates.name where id == updates.id
}!

For a Rails-style full-record save, load a struct, mutate it, then call orm.save. The helper uses the struct primary key, or an id field when present, for the WHERE clause and updates the remaining mapped fields automatically.

import orm

mut foo := (sql db {
    select from Foo where id == 1
}!).first()
foo.name = 'updated'
foo.updated_at = time.now()

orm.save(db, foo)!

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

Warning

This section documents the deprecated Function Call API (orm_fn; orm.new_query[T] / QueryBuilder). It is deprecated and will be removed from the standard library after 2027-08-17, to be maintained in a separate repository. Prefer the built-in sql ORM syntax for new code. Compiler warnings begin on 2027-02-18; until then the compiler emits a migration notice. See https://github.com/vlang/v/issues/27001 for details.

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

Create a QueryBuilder (which also completes struct mapping):

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

Create a database table:

	qb.create()!

Insert multiple records into the table:

	qb.insert_many(users)!

Delete records (note: delete() must follow where()):

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

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()!

Update records (note: update() must be placed last):

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

For a full-record update without spelling out each set(...) clause, use orm.save:

	selected := qb.where('name = ?', 'John')!.query()!
	mut john := selected.first()
	john.age = 72
	john.title = 'lead'
	orm.save(db, john)!

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.

To remove duplicate rows from a query, mark it as DISTINCT before query():

	distinct_roles := qb.select('role')!.distinct()!.query()!

Drop the table:

	qb.drop()!

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()!

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 &&. Named arrays can also be passed directly for IN clauses:

	user_ids := ['1', '2']
	users := qb.where('id IN ?', user_ids)!.query()!

Explicit relationships in the Function Call API

orm.new_query[T] loads scalar fields only. Use include to load a direct relationship and then_include to continue from the last included relationship:

mut parents := orm.new_query[Parent](db)
rows := parents.include('children')!.then_include('grandkids')!.query()!

A new include starts an independent path. Every segment is validated before it changes the builder. An invalid include leaves the previous path intact. query and reset clear all include paths and the current path cursor. Duplicate paths are loaded once. Arrays need an @[fkey] attribute; singular and optional struct relationships use the existing ORM lookup-column mapping.

This changes the Function Call API's previous implicit relationship loading: relationships are now populated only when requested. SQL-like sql db { ... } queries retain their existing implicit loading, including selects without an explicit field list. where continues to filter the root entity; includes do not add joins or related-entity predicates.

When an explicit selection omits a lookup key, the query fetches it for hydration without populating that unselected scalar field in the result. With distinct, the lookup key must be selected explicitly to avoid changing distinctness. Errors from explicitly requested relationship queries are returned to the caller.

Loading is currently per parent row: one included collection across N parents can execute N additional queries. This API controls which relationships load; future batching can reduce the number of database round trips.

README.md Unescape Escape

ORM