🍷 Dartonic Github
A database query builder inspired by Drizzle. It allows you to connect to various databases (SQLite, PostgreSQL, MySQL) and perform database operations using a fluent API.
If you find Dartonic useful, please consider supporting its development 🌟Buy Me a Coffee.🌟 Your support helps us improve the package and make it even better!
- Getting Started
- Installation
- Connecting to a Database
- Defining Tables
- Working with Relationships
- Querying the Database
- Supported Methods & Examples
- Limitations & Unsupported Types
- Contributing
- License
Dartonic is designed to simplify your database interactions in Dart. With its fluent query API, you can build complex queries effortlessly. ✨
Add Dartonic to your pubspec.yaml:
dependencies:
dartonic: ^0.0.10Then run:
dart pub getOr run:
dart pub get add dartonicDartonic supports multiple databases through connection URIs:
-
SQLite (in memory):
final dartonic = Dartonic("sqlite::memory:");
-
SQLite (from file):
final dartonic = Dartonic("sqlite:database/database.db");
-
PostgreSQL:
final dartonic = Dartonic("postgres://username:password@localhost:5432/postgres?sslmode=verify-full");
-
MySQL:
final dartonic = Dartonic("mysql://user:userpassword@localhost:3306/mydb");
Synchronize your tables:
void main() async {
final dartonic = Dartonic("sqlite::memory:", [usersTable, ordersTable]);
await dartonic.sync(); // Synchronize tables
}Get instance Dartonic in anywhere in your project:
final db = dartonic.instance;🚨 Note: Dartonic uses a singleton pattern – all instances refer to the same connection.
Dartonic is inspired by Drizzle and allows you to define table schemas conveniently. Below is an example of creating a SQLite table with custom column definitions.
Note: Some modifiers or functions may differ on SQLite. Check the SQLite documentation for supported default functions.
import 'package:dartonic/dartonic.dart';
import 'package:dartonic/columns.dart';
final usersTable = sqliteTable('users', {
'id': integer().primaryKey(autoIncrement: true),
'name': text().notNull(),
'age': integer(),
'email': text().notNull().unique(),
'created_at': timestamp().notNull().defaultNow(),
'updated_at': timestamp().notNull().defaultNow(),
});
final ordersTable = sqliteTable('orders', {
'id': integer().primaryKey(autoIncrement: true),
'user_id': integer(columnName: 'user_id'),
'total': integer(),
});Note: Some modifiers or functions may differ on PostgreSQL. Check the PostgreSQL documentation for supported default functions.
final usersTable = pgTable('users', {
'id': serial().generatedAlwaysAsIdentity(),
'name': varchar(length: 100).notNull(),
'age': integer(),
});Note: Auto increment is defined differently on MySQL. Ensure your
primaryKey()method is correctly implemented for MySQL.
final usersTable = mysqlTable('users', {
'id': integer().primaryKey(autoIncrement: true),
'name': varchar(length: 100).notNull(),
});Dartonic allows you to define relationships between tables. This makes it easier to perform related queries using JOINs. Relationships are defined through helper methods (for example, a relations function) which let you map the associations.
Here’s an example on how to define one-to-one and one-to-many relationships:
// Defining the base tables.
final usersTable = sqliteTable('users', {
'id': integer().primaryKey(autoIncrement: true),
8000
'name': text().notNull(),
'email': text().notNull().unique(),
});
final profileTable = sqliteTable('profile_info', {
'id': integer().primaryKey(),
'user_id': integer(columnName: 'user_id').references(() => 'users.id'),
'bio': text(),
});
final postsTable = sqliteTable('posts', {
'id': integer().primaryKey(autoIncrement: true),
'user_id': integer(columnName: 'user_id').references(() => 'users.id'),
'content': text(),
});
// Defining relationships.
// For one-to-one relationship: each user has one profileInfo.
final usersRelations = relations(
usersTable,
(builder) => {
'profileInfo': builder.one(
'profile_info',
fields: ['users.id'],
references: ['profile_info.user_id'],
),
},
);
// For one-to-many relationship: each user can have multiple posts.
final postsRelations = relations(
usersTable,
(builder) => {
'posts': builder.many(
'posts',
fields: ['users.id'],
references: ['posts.user_id'],
),
},
);
// Now you can initialize Dartonic with the main tables and include relationship meta-information.
final dartonic = Dartonic("sqlite://database.db", [
usersTable,
profileTable,
postsTable,
usersRelations,
postsRelations,
]);Once the relationships are defined, you can perform JOIN queries with ease:
// Example JOIN query: Get users with their profile bio.
final query = db
.select({
'userName': 'users.name',
'userEmail': 'users.email',
'bio': 'profile_info.bio',
})
.from('users')
.innerJoin('profile_info', eq("users.id", "profile_info.user_id"));
print(query.toSql());
final result = await query;
print(result);After synchronizing the tables using sync(), you can build and execute queries using the fluent API provided by Dartonic.
-
SELECT all columns:
final users = await db.select().from('users'); print(users);
-
SELECT specific columns using a map:
Here, the key represents the alias (renamed column) and the value represents the actual column.
final result = await db.select({ 'fieldId': 'users.id', 'fieldName': 'users.name', }).from('users'); print(result);
You can chain multiple methods to build complex queries with joins, filters, ordering, and pagination.
final complexQuery = db
.select({
'userName': 'users.name',
'orderTotal': 'orders.total'
})
.from('users')
.innerJoin('orders', eq("users.id", "orders.user_id"))
.where(gt("orders.total", 100))
.orderBy("users.name")
.limit(10)
.offset(0);
print(complexQuery.toSql());
final result = await complexQuery;
print(result);Below are some examples demonstrating all available methods within Dartonic's query builder.
Select columns by specifying a map:
final users = await db
.select({
'name': 'users.fullname',
'age': 'users.birthday'
})
.from('users');
print(users);Insert only or insert a record and return the full record as well as partial (only id):
// Insert only
await db
.insert('users')
.values({
'name': "Dan",
'age': 28
});
// Insert with returning
final insertedUser = await db
.insert('users')
.values({
'name': "Dan",
'age': 28
})
.returning();
print("Inserted with full RETURNING:");
print(insertedUser);
// Insert and return only id
final insertedPartial = await db
.insert('users')
.values({
'name': "Partial Dan",
'age': 30
})
.returning(['id']);
print("Inserted with partial RETURNING {'id': 1}");
print(insertedPartial);Update only or update a record and return the updated information:
// Update only
await db
.update('users')
.set({'name': "Daniel", 'age': 29})
.where(eq("users.id", 1));
// Update with returning
final updatedUser = await db
.update('users')
.set({'name': "Daniel", 'age': 29})
.where(eq("users.id", 1))
.returning();
print("Updated with full RETURNING:");
print(updatedUser);Delete only or delete a record and get the deleted row's data:
// Delete only
await db
.delete('users')
.where(eq("users.id", 3))
.returning();
// Delete with returning
final deletedUser = await db
.delete('users')
.where(eq("users.id", 3))
.returning();
print("Deleted with full RETURNING:");
print(deletedUser);Perform various types of JOINs:
// INNER JOIN example: users and orders
final joinQuery = db
.select({
'userName': 'users.name',
'orderTotal': 'orders.total'
})
.from('users')
.innerJoin('orders', eq("users.id", "orders.user_id"))
.where(gt("orders.total", 100));
print("SQL INNER JOIN with filter:");
print(joinQuery.toSql());
final joinResult = await joinQuery;
print(joinResult);You can use a variety of filter methods:
// Equality filter (eq)
final eqQuery = db.select().from("users").where(eq("users.age", 30));
print("SQL eq:");
print(eqQuery.toSql());
print(await eqQuery);
// Greater-than filter (gt)
final gtQuery = db.select().from("users").where(gt("users.age", 25));
print("SQL gt:");
print(gtQuery.toSql());
print(await gtQuery);
// In array filter
final inArrayQuery = db.select().from("users").where(inArray("users.age", [25, 35]));
print("SQL inArray:");
print(inArrayQuery.toSql());
print(await inArrayQuery);
// Between filter
final betweenQuery = db.select().from("users").where(between("users.age", 26, 34));
print("SQL between:");
print(betweenQuery.toSql());
print(await betweenQuery);
// Composite filter with AND
final andQuery = db.select().from("users").where(
and([gt("users.age", 25), lt("users.age", 35)])
);
print("SQL and:");
print(andQuery.toSql());
print(await andQuery);
// Composite filter with OR
final orQuery = db.select().from("users").where(
or([lt("users.age", 25), gt("users.age", 35)])
);
print("SQL or:");
print(orQuery.toSql());
print(await orQuery);-
SQLite Restrictions:
-
Some advanced SQL features like
ILIKEare not natively supported by SQLite. Although Dartonic generates the SQL, not all features will run as expected on SQLite. -
Ensure you are using SQLite version 3.35.0 or newer if you plan to use the
RETURNINGclause.
-
-
Other Databases:
-
PostgreSQL fully supports the majority of features such as
RETURNING,JOINs, and advanced filters. -
MySQL support might vary; confirm your MySQL version supports specific SQL clauses used by Dartonic.
-
Contributions are very welcome! If you find bugs, have suggestions, or want to contribute new features, please submit an issue or a pull request.
🍷 Dartonic is released under the MIT License. See the LICENSE file for more details.
Made with ❤️ for Dart/Flutter developers! 🎯