Built-in ORM and Databases

33.1 V's ORM (sql Blocks)

The ORM is checked at compile time (fields and types) and queries are parameterized (safe from SQL injection).

Works with db.sqlite, db.pg (PostgreSQL), db.mysql, and db.mssql (via ODBC). The same code works across databases.

Relations: struct fields that are arrays of other tables, with @[fkey: 'parent_id'].

The orm.QueryBuilder API (orm.new_query[User](db)) for dynamic queries (newer versions).

import db.sqlite

@[table: 'users']
struct User {
	id    int    @[primary; sql: serial]
	name  string @[unique]
	age   int
	email string @[sql_type: 'TEXT'; default: "''"]
}

fn main() {
	db := sqlite.connect('app.db') or { panic(err) }
	defer { db.close() or {} }

	sql db {
		create table User
	} or { panic(err) }

	ana := User{name: 'Ana', age: 30, email: '[email protected]'}
	sql db {
		insert ana into User
	} or { panic(err) }

	adults := sql db {
		select from User where age >= 18 order by name limit 10
	} or { []User{} }
	for u in adults { println('${u.id}: ${u.name}') }

	count := sql db {
		select count from User
	} or { 0 }
	println(count)

	sql db {
		update User set age = 31 where name == 'Ana'
	} or {}
	sql db {
		delete from User where age < 18
	} or {}
	sql db {
		drop table User
	} or {}
}

33.2 Raw SQL

Transactions: db.begin(), db.commit(), db.rollback() (API varies by driver).

Other stores: Redis (vredis via VPM), MongoDB (community drivers).

rows := db.exec('SELECT id, name FROM users WHERE age > 20') or { [] }
for row in rows { println(row.vals) }
db.exec_param('INSERT INTO users (name, age) VALUES (?, ?)', 'Bo') or {}
db.exec_param_many('SELECT * FROM users WHERE name = ? AND age = ?', ['Bo', '25']) or {}
// db.pg: pg.connect(pg.Config{host: 'localhost', user: 'postgres', password: '...', dbname: 'app'})