Define classes to power your queries by extending Table.
The Query factory helps you compose one-to-one SQL and execute them.
Columns you return are selected and ready for type-safe use!
Note
Library is experimental and documentation is in progress. Current README is geared as a tech demo to show what I'm working on! :)
Classes which extend Table can manage your data with high safety and convenience. This project aims to make using SQL in Javascript apps completely frictionless.
- No DSL or magic - Everything is plain JavaScript/TypeScript
- Type safety without decorators - Full type inference without complex setup
- Declarative and SQL-first - The API closely follows SQL semantics - No chaining! ⛓️💥
- Context agnostic - The query system handles database syntax specifics for you.
- Uber Extensible - Fields and Tables can be extended and customized to fit most needs.
- Performance - Very minimal overhead over actual SQL clients, memory efficient at high volume.
With the base SQL module, you can define your database schema using classes that extend Table. Field factories let you customize columns with types, defaults, validation, and more.
npm i @expressive/sql// src/database/tables.ts
import { Table, Str, Num, Bool, Time } from '@expressive/sql';
import { hash } from "./lib/passwords";
class User extends Table {
// Fields without nullable option default to not nullable
name = Str();
email = Str({ unique: true });
// Explicitly nullable fields
nickName = Str({ nullable: true });
// Custom type, length, and setter
password = Str({ type: "varchar", length: 40, set: hash });
// Fallback values (like SQL DEFAULT)
createdAt = Time({ fallback: "NOW" });
}
class Post extends Table {
authorId = Num();
title = Str();
content = Str();
// Boolean with fallback
published = Bool({ fallback: false });
// Time fields
createdAt = Time({ fallback: "NOW" });
updatedAt = Time({ nullable: true });
}
export { User, Post }Tables you create are type-safe out of the box! No special decorators or interface types needed.
// lib/database/tables.d.ts (built with tsc)
declare class Table extends Base {
id: Primary.Int;
}
declare class User extends Table {
name: Str.Text;
nickName: Str.Text & Nullable;
email: Str.Text;
password: Str.VarChar;
createdAt: Time.Timestamp & Optional;
}
declare class Post extends Table {
authorId: Num.Int;
title: Str.Text;
content: Str.Text;
published: Bool.Boolean & Optional;
createdAt: Time.Timestamp & Optional;
updatedAt: Time.Timestamp & Nullable;
}
export { Post, User };The library provides several field type factories to define your table columns:
Str()- String fields (text, varchar, etc.)Num()- Numeric fields (integer, float, decimal, etc.)Bool()- Boolean fieldsTime()- Date and time fieldsOne()- Foreign key relationship to another table
Each field type accepts options to customize behavior:
class Product extends Table {
// Basic string field
name = Str();
// Varchar with length limit
sku = Str({ type: 'varchar', length: 20 });
// Numeric with precision
price = Num({ type: 'numeric', precision: 10, scale: 2 });
// Boolean with default
inStock = Bool({ fallback: true });
// Nullable field
description = Str({ nullable: true });
// Unique constraint
barcode = Str({ unique: true, nullable: true });
}Pick an adapter which fits your needs:
# PostgreSQL
npm i @expressive/postgres pg
# PGLite (embedded PostgreSQL)
npm i @expressive/postgres @electric-sql/pglite
# SQLite
npm i @expressive/sqlite better-sqlite3
# MySQL
npm i @expressive/mysql mysql2For this example, we'll use Postgres:
// src/database/index.ts
import { PGConnection } from '@expressive/postgres';
import * as tables from './tables';
// Create a connection and link your tables
export async function connect() {
await new PGConnection(tables, {
host: process.env.DB_HOST,
port: 5432,
database: process.env.DB_NAME,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
});
}
// Reexport tables for convenience
export * from './tables';
// Export Query so everything is consolidated
export { Query } from '@expressive/sql';When you connect, all Table classes are validated against the database schema. If any mismatch is found, the connection will throw - ensuring you operate on the data you expect.
// PGLite (embedded)
import { PGLiteConnection } from '@expressive/postgres';
await new PGLiteConnection(tables, { dataDir: './data' });
// SQLite
import { SQLiteConnection } from '@expressive/sqlite';
await new SQLiteConnection(tables, './database.db');
// MySQL
import { MySQLConnection } from '@expressive/mysql';
await new MySQLConnection(tables, {
host: 'localhost',
database: 'myapp',
user: process.env.DB_USER,
password: process.env.DB_PASSWORD
});The Query function is a powerful factory which generates both the SQL query and the parser that transforms results into JavaScript objects with the shape you specify.
import { Query, Post, User } from './database';
async function getPosts() {
return await Query(where => {
const post = where(Post);
const author = where(User);
// Join tables by equating fields
where(post.authorId).equal(author.id);
// Filter with WHERE clauses
where(post.published).equal(true);
// Return the shape you want
return {
title: post.title,
author: author.name,
content: post.content,
date: post.createdAt
};
});
}The return type is automatically inferred from your selection:
declare function getPosts(): Promise<{
title: string;
author: string;
content: string;
date: Date;
}[]>// Return specific fields as an object
const results = await Query(where => {
const user = where(User);
return {
name: user.name,
email: user.email
};
});
// Return entire table
const users = await Query(where => {
const user = where(User);
return user;
});
// Return a single field
const emails = await Query(where => {
const user = where(User);
return user.email;
});
// Return nested objects (generates SQL aliases)
const results = await Query(where => {
const user = where(User);
return {
user: {
id: user.id,
name: user.name
},
contact: {
email: user.email
}
};
});
// Count query (no return = COUNT(*))
const count = await Query(where => {
const user = where(User);
where(user.email).equal('test@example.com');
// No return statement = COUNT(*)
});// Insert a user
const userId = await User.insert({
name: 'John Doe',
email: 'john@example.com',
password: 'hashedpassword123'
});
// Insert a post
await Post.insert({
title: 'Hello World',
content: 'This is my first post!',
published: true,
author: userId
});The Query factory can also be used to update records!
// John wants to set his account to private or something.
const count = await Query(where => {
const post = where(Post);
const user = where(User);
where(post.userId).equals(user.id);
where(user.email).equals("john@example.com");
where(post).update({
published: false
});
});
// count === 10Tables are defined as classes that extend the Table base class. Each property represents a piece of data, usually a column, in the database.
class Product extends Table {
name = Str();
price = Num({ type: 'numeric', precision: 10, scale: 2 });
inStock = Bool({ fallback: true });
description = Str({ nullable: true });
}The library provides several field types to define your table columns:
Str()- String fields (text, varchar, etc.)Num()- Numeric fields (integer, float, decimal, etc.)Bool()- Boolean fieldsTime()- Date and time fieldsOne()- Foreign key relationship to another table
Each field type accepts options to customize the field's behavior, such as nullable, unique, length, etc.
The query builder provides a fluent API for building SQL queries:
const query = Query(where => {
const user = where(User);
const post = where(Post);
// here, we declare a join by equating two fields.
where(post.author).equal(user.id);
// these are our where clauses.
where(user.name).equal('John');
where(post.published).equal(true);
// this is our selection.
return {
content: post.content,
author: user.name
};
});
// Execute the query
const results = await query;
// Use results as needed.
console.log(`Found ${results.length} results.`)
// results are implicitly the types you returned in your Query factory.
// The real selection knows where to put the values, even if deeply nested.
for(const { content, author } of results)
console.log(`${author} said: ${content}`)
/*
Found 5 results.
John said: Hello world!
John said: I love SQL!
John said: Expressive is next level!
John said: Wow I can do so much with this!
John said: SQL is the best!
*/
// You can also stringify a query to see generated SQL! 👀
console.log(query);
/*
SELECT "post"."content" as "content", "user"."name" AS "author"
FROM "post" AS "post"
JOIN "user" AS "user" ON "post"."author_id" = "user"."id"
WHERE "user"."name" = 'John'
AND "post"."published" = true
*/Sort results and limit the number of rows returned:
// Sort by one field
await Query(where => {
const user = where(User);
where(user.createdAt).desc();
return user;
});
// ORDER BY user.created_at DESC
// Multiple column sorting
await Query(where => {
const user = where(User);
where(user.email).asc(); // First by email
where(user.createdAt).desc(); // Then by created date
return user;
});
// ORDER BY user.email ASC, user.created_at DESC
// Limit results
await Query(where => {
const user = where(User);
where(10); // LIMIT 10
return user;
});
// Combine sorting and limits
await Query(where => {
const post = where(Post);
where(post.createdAt).desc();
where(5); // Get 5 most recent posts
return post;
});Joins are created by comparing fields from different tables:
const query = Query(where => {
const post = where(Post);
const user = where(User);
// This creates an INNER JOIN
where(post.authorId).equal(user.id);
// Additional WHERE clauses
where(post.published).equal(true);
where(user.email).equal('john@example.com');
return {
postTitle: post.title,
authorName: user.name,
content: post.content
};
});SELECT post.title AS "postTitle", user.name AS "authorName", post.content AS "content"
FROM post
INNER JOIN user ON post.author_id = user.id
WHERE post.published = true AND user.email = 'john@example.com'Filter your queries using various comparison operators. JavaScript values are automatically converted based on field type:
const query = Query(where => {
const post = where(Post);
// Equal
where(post.published).equal(true);
// Not equal
where(post.title).not('Hello');
// Greater than
where(post.createdAt).over(new Date('2023-01-01'));
// Greater than or equal
where(post.views).over(100, true);
// Less than
where(post.createdAt).under(new Date('2024-01-01'));
// Less than or equal
where(post.views).under(1000, true);
// IN operator
where(post.status).in(['draft', 'published']);
return post;
});By default, multiple conditions are combined with AND:
await Query(where => {
const user = where(User);
// These are ANDed together
where(user.email).equal('test@example.com');
where(user.createdAt).over(new Date('2023-01-01'));
return user;
});
// WHERE user.email = 'test@example.com' AND user.created_at > '2023-01-01'To create OR conditions, nest them in a group:
await Query(where => {
const user = where(User);
// This creates an OR group
where(
where(user.role).equal('admin'),
where(user.role).equal('moderator')
);
return user;
});
// WHERE user.role = 'admin' OR user.role = 'moderator'Build complex logic using nested groups. Groups alternate between AND/OR based on nesting level:
Query(where => {
const post = where(Post);
where(
where(post.title).equal('Hello'),
where(
where(post.published).equal(true),
where(post.createdAt).over(new Date('2023-01-01'))
)
);
return post;
});Translates to:
SELECT post.*
FROM post
WHERE post.title = 'Hello'
OR (post.published = true AND post.created_at > '2023-01-01')Query(where => {
const item = where(Item);
// Must be in stock
where(item.inStock).equal(true);
// AND (red OR blue)
where(
where(item.color).equal('red'),
where(item.color).equal('blue')
);
// AND (small OR medium OR large)
where(
where(item.size).equal('small'),
where(item.size).equal('medium'),
where(item.size).equal('large')
);
return item;
});WHERE item.in_stock = true
AND (item.color = 'red' OR item.color = 'blue')
AND (item.size = 'small' OR item.size = 'medium' OR item.size = 'large')Map JavaScript property names to different database column names:
class User extends Table {
firstName = Str({ column: 'first_name' });
lastName = Str({ column: 'last_name' });
}
// Use JavaScript property names in your code
await User.insert({
firstName: 'John',
lastName: 'Doe'
});Customize the table name using the Primary factory:
import { Primary } from '@expressive/sql';
class BlogPost extends Table {
id = Primary({
tableName: "legacy_blog_posts"
});
title = Str();
content = Str();
}Fields automatically validate data based on their type and constraints:
class Product extends Table {
price = Num({ type: 'numeric', precision: 5, scale: 2 });
}
// This will throw a validation error
await Product.insert({ price: 1000.123 });
// Error: Value of `price` exceeds precision (5,2)Transform data when reading from or writing to the database:
import { hash } from './lib/passwords';
class User extends Table {
// Hash password before storing
password = Str({
set: (value) => hash(value),
get: (value) => value // Return as-is from DB
});
// Format data on read
email = Str({
get: (value) => value.toLowerCase()
});
}
const user = await User.insert({
password: 'plaintext123', // Automatically hashed
email: 'USER@EXAMPLE.COM'
});
console.log(user.email); // "user@example.com"Perform mathematical operations in queries using the function parameter:
const query = Query((where, fn) => {
const { add, sub, mul, div, neg } = fn;
const item = where(Item);
return {
total: mul(item.price, item.quantity),
discounted: mul(item.price, sub(1, item.discount)),
taxed: add(item.price, mul(item.price, 0.1)),
negated: neg(item.price)
};
});Nest expressions for complex calculations:
Query((where, fn) => {
const { add, mul } = fn;
const item = where(Item);
// (price + 5) * quantity
return mul(add(item.price, 5), item.quantity);
});
// SELECT (item.price + 5) * item.quantity FROM itemUse template functions for custom SQL expressions:
Query((where, fn) => {
const item = where(Item);
return {
custom: fn(`${item.price} * (${item.quantity} + 5)`)
};
});
// SELECT item.price * (item.quantity + 5) AS "custom"View the generated SQL by stringifying a query:
const query = Query(where => {
const user = where(User);
const post = where(Post);
where(post.authorId).equal(user.id);
where(user.name).equal('John');
where(post.published).equal(true);
return {
content: post.content,
author: user.name
};
});
console.log(String(query));
/*
SELECT
post.content AS "content",
user.name AS "author"
FROM post
INNER JOIN user ON post.author_id = user.id
WHERE user.name = 'John' AND post.published = true
*/
const results = await query; // Execute when readyThe library includes additional capabilities:
- Transactions - Atomic operations across multiple queries
- Connection pooling - Efficient database connection management
- Schema validation - Automatic checking against database schema
- Custom field types - Extend base field types for specific needs
- Query composition - Build reusable query fragments
This documentation covers core features. Additional topics like
transactions, migrations, schema management, and more are being documented!
Contributions are welcome! Please feel free to submit a Pull Request.
This project is licensed under the MIT License.