V has a built-in ORM (object-relational mapping) which supports SQLite, MySQL and Postgres,
but soon it will support MS SQL and Oracle.
V's ORM provides a number of benefits:
One syntax for all SQL dialects. (Migrating between databases becomes much easier.)
Queries are constructed using V's syntax. (There's no need to learn another syntax.)
Safety. (All queries are automatically sanitised to prevent SQL injection.)
Compile time checks. (This prevents typos which can only be caught during runtime.)
Readability and simplicity. (You don't need to manually parse the results of a query and
then manually construct objects from the parsed results.)
import db.sqlite
// sets a custom table name. Default is struct name (case-sensitive)
@[table: 'customers']
struct Customer {
id int @[primary; serial] // a field named `id` of integer type must be the first field
name string
nr_orders int
country ?string
}
db := sqlite.connect('customers.db')!
// You can create tables from your struct declarations. For example the next query will issue SQL similar to this:
// CREATE TABLE IF NOT EXISTS `Customer` (
// `id` INTEGER PRIMARY KEY,
// `name` TEXT NOT NULL,
// `nr_orders` INTEGER NOT NULL,
// `country` TEXT
// )
sql db {
create table Customer
}!
// insert a new customer:
new_customer := Customer{
name: 'Bob'
country: 'uk'
nr_orders: 10
}
sql db {
insert new_customer into Customer
}!
us_customer := Customer{
name: 'Martin'
country: 'us'
nr_orders: 5
}
sql db {
insert us_customer into Customer
}!
none_country_customer := Customer{
name: 'Dennis'
country: none
nr_orders: 2
}
sql db {
insert none_country_customer into Customer
}!
// update a customer:
sql db {
update Customer set nr_orders = nr_orders + 1 where name == 'Bob'
}!
// select count(*) from customers
nr_customers := sql db {
select count from Customer
}!
println('number of all customers: ${nr_customers}')
// V's syntax can be used to build queries:
uk_customers := sql db {
select from Customer where country == 'uk' && nr_orders > 0 order by id desc limit 10
}!
println('We found a total of ${uk_customers.len} customers matching the query.')
for c in uk_customers {
println('customer: ${c.id}, ${c.name}, ${c.country}, ${c.nr_orders}')
}
none_country_customers := sql db {
select from Customer where country is none
}!
println('We found a total of ${none_country_customers.len} customers, with no country set.')
for c in none_country_customers {
println('customer: ${c.id}, ${c.name}, ${c.country}, ${c.nr_orders}')
}
// delete a customer
sql db {
delete from Customer where name == 'Bob'
}!
Troubleshooting compilation problems with SQLite on Windows #
On Windows, if you get a compilation error, about a missing sqlite3.h file, you have to run:
v vlib/db/sqlite/install_thirdparty_sqlite.vsh once, then retry your compilation.
V also maintains a separate sqlite module, that wraps an SQLite amalgamation, but otherwise
has the same API as the db.sqlite module. Its benefit, is that with it, you do not need to
install a separate system level sqlite package/library on your system (which can be hard on
some systems like windows, or systems with musl for example).
Its negative is that it can make your compilations a bit slower (since it compiles SQLite
from C, in addition to your own code).