ORM
(This is still in an alpha state)
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}')
}
// You can select just the fields you need. The result still has type `[]Customer`,
// and non-selected fields keep their zero values.
partial_customers := sql db {
select id, name from Customer where nr_orders > 0
}!
println(partial_customers)
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'
}!
For more examples and the docs, see vlib/orm.
Troubleshooting compilation problems with SQLite
On any platform (Windows, Linux, macOS), you can run:
v vlib/db/sqlite/install_thirdparty_sqlite.vsh
This downloads the SQLite amalgamation source and places it in
v/thirdparty/sqlite. V will then compile it automatically
during your build.
On Linux, you can also install the system development package
instead:
- Debian/Ubuntu:
sudo apt install -y libsqlite3-dev
- Fedora/RHEL:
sudo dnf -y install sqlite-devel
- Arch:
sudo pacman -S sqlite
Interactive SQLite CLI
V includes a built-in SQLite CLI (v sqlite) as a V-native
replacement for sqlite3:
v sqlite mydb.db
It provides a full readline REPL with history and tab completion,
9 output modes, .dump, .import/.export, .backup, session
control, and schema tools. Run .help inside the REPL for the
full command list.
Convenience Methods
The db.sqlite module includes helper methods for common queries:
db.tables()! // list all user table names
db.columns('users')! // column names for a table
db.schema('users')! // CREATE statement(s)
db.db_size()! // file size in bytes
Using the sqlite VPM package
V also maintains the sqlite VPM package.
It wraps an SQLite amalgamation, but otherwise has the same API as db.sqlite.
Its benefit is that you do not need to install a separate system-level SQLite package or
library on your system, which can be harder on Windows or musl-based systems.
Its downside is that it can make compilation a bit slower, since it compiles SQLite from C
in addition to your own code.
To install it, run:
v install sqlite
Then, in your code, use this:
import sqlite
instead of:
import db.sqlite