✌️ vsql

vsql is a small SQL database that follows the SQL standard. It keeps a whole database in a single file (or in memory), and you can use it from a terminal, from any PostgreSQL client, or embedded in your own V program. It's written in pure V with no dependencies.

$ vsql cli shop.vsql
vsql> CREATE TABLE products (name VARCHAR(50), price NUMERIC(10, 2));
CREATE TABLE 1 (0 ms)

vsql> INSERT INTO products (name, price) VALUES ('Coffee', 4.50), ('Bagel', 2.25);
INSERT 2 (1 ms)

vsql> SELECT * FROM products ORDER BY name;
NAME   | PRICE
-------+------
Bagel  |  2.25
Coffee |  4.50
2 rows (1 ms)

Why vsql?

• Standard SQL. vsql follows the SQL:2016 standard rather than any one database's dialect, so what you learn here works elsewhere. Errors use the standard SQLSTATE codes, and the compliance table shows what's supported so far.
• One file, no setup. A database is a single file you can copy around, like SQLite. Use :memory: for a database that only lives in memory.
• Real transactions. START TRANSACTION, COMMIT and ROLLBACK work across connections and processes using MVCC, and a write-ahead log keeps the file safe if the process or the power stops.
• Talks to PostgreSQL clients. vsql server speaks the PostgreSQL wire protocol, so you can connect with psql or a GUI such as TablePlus.
• Made for V. Embed it in a V program as a library, use it with V's built-in ORM, add your own SQL functions, or expose your data as virtual tables.

Try It

1. Download

Download vsql for macOS, Linux or Windows from the latest release and unzip it. Nothing else needs to be installed.

Or, with Docker:

docker run -it --mount type=bind,source="$(pwd)",target=/db elliotchance/vsql cli /db/shop.vsql

2. Run some SQL

vsql cli shop.vsql

This creates shop.vsql if it doesn't exist. Type exit to leave. You can also load a file of SQL statements with vsql in shop.vsql < schema.sql, and dump a database as SQL with vsql out shop.vsql.

3. Connect with a PostgreSQL client

vsql server shop.vsql

Then, in another terminal:

psql -h 127.0.0.1 -p 3210

The server ignores the user name, password and database name. See the list of tested clients.

Use It From V

v install elliotchance.vsql

import elliotchance.vsql.vsql

fn main() {
	mut db := vsql.open('shop.vsql')!

	db.query('CREATE TABLE products (name VARCHAR(50), price NUMERIC(10, 2))')!
	db.query("INSERT INTO products (name, price) VALUES ('Coffee', 4.50), ('Bagel', 2.25)")!

	mut stmt := db.prepare('SELECT name, price FROM products WHERE price < :max_price ORDER BY name')!
	for row in stmt.query({
		'max_price': vsql.new_numeric_value('3.00')
	})! {
		println('${row.get_string('NAME')!} costs ${row.get_string('PRICE')!}')
	}
}

There's also a driver for V's ORM:

import elliotchance.vsql.vorm

struct Product {
	id    int @[primary; sql: serial]
	name  string
	price f64
}

fn main() {
	mut db := vorm.open('shop.vsql')!
	sql db {
		create table Product
	}!
}

See the V client library and V ORM docs, and the examples.

Good to Know

• It follows the standard closely. Unquoted names are upper case (products is PRODUCTS), there's no LIMIT (use FETCH FIRST 10 ROWS ONLY) and no NOW() (use CURRENT_TIMESTAMP). Use VALUES 1 + 2 to try an expression, since a SELECT needs a FROM. The FAQ has more.
• Not everything is there yet. For example, DISTINCT, HAVING, IN, CASE, subqueries in WHERE, UNIQUE and FOREIGN KEY constraints, and column defaults aren't supported yet. The compliance table has the full list.

Documentation

The full documentation is at vsql.readthedocs.io:

• Installing & updating
• CLI and server
• SQL reference, functions and SQLSTATE errors
• Transactions and how the file format works

Contributing

Bug reports, ideas and pull requests are all welcome. Start with the contributing guide and how the tests work. vsql is MIT licensed.