V has a powerful, concise ORM baked in! Create tables, insert records, manage relationships, all regardless of the DB driver you decide to use.
Driver authors using the shared SQL generators can target SQLite, PostgreSQL, MySQL, and H2-backed connections with the built-in ORM dialect helpers.
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
}
[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[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. 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[!NOTE] For using the Function Call API for
orm, please checkFunction 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
}!
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.
[!TIP] This guide uses the built-in
db.sqlitemodule. If you want SQLite without first installing system-level SQLite development files, the V team also maintains thesqliteVPM package.Install it with
v install sqliteand changeimport db.sqlitetoimport 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
}!
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
}!
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 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.
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
}!
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.
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.
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.
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.
You can delete rows using V syntax and functions. Again, pass the struct as the table.
sql db {
delete from Foo where id > 10
}!
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.
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
}!
}
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 {} }
QueryBuilder (which also completes struct mapping): mut qb := orm.new_query[User](db)
qb.create()!
qb.insert_many(users)!
delete() must follow where()): qb.where('name = ?','John')!.delete()!
select):// Returns []User with only 'name' populated; other fields are zero values.
only_names := qb.select('name')!.query()!
update() must be placed last): qb.set('age = ?, title = ?', 71, 'boss')!.where('name = ?','John')!.update()!
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)!
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()!
qb.drop()!
final_users :=
qb
.drop()!
.create()!
.insert_many(users)!
.set('name = ?', 'haha')!.where('name = ?', 'Tom')!.update()!
.where('age >= ?', 30)!.delete()!
.query()!
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%')!
?.
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()!