dartonic 0.0.10

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.

Dartonic

🍷 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.

---

Support 💖

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!

Table of Contents

• Getting Started
• Installation
• Connecting to a Database
• Defining Tables
  • SQLite Example
  • PostgreSQL Example
  • MySQL Example
• Working with Relationships
• Querying the Database
  • Simple Queries
  • Complex Queries
• Supported Methods & Examples
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • Join Queries
  • Filter Conditions
• Limitations & Unsupported Types
• Contributing
• License

---

Getting Started

Dartonic is designed to simplify your database interactions in Dart. With its fluent query API, you can build complex queries effortlessly. ✨

---

Installation

Add Dartonic to your pubspec.yaml:

dependencies:
  dartonic: ^0.0.10

Then run:

dart pub get

Or run:

dart pub get add dartonic

---

Connecting to a Database

Dartonic 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/database");
  

• 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.

---

Defining Tables

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.

SQLite Example

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(),
});

PostgreSQL Example

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(),
});

MySQL Example

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(),
});

---

Working with Relationships

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),
  '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);

---

Querying the Database

After synchronizing the tables using sync(), you can build and execute queries using the fluent API provided by Dartonic.

Simple Queries

• 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);
  

Complex Queries

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);

---

Supported Methods & Examples

Below are some examples demonstrating all available methods within Dartonic's query builder.

SELECT

Select columns by specifying a map:

final users = await db
    .select({
      'name': 'users.fullname',
      'age': 'users.birthday'
    })
    .from('users');
print(users);

INSERT

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

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

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);

Join Queries

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);

Filter Conditions

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);

---

Limitations & Unsupported Types

• SQLite Restrictions:

  • Some advanced SQL features like ILIKE are 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 RETURNING clause.

• 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.

---

Contributing

Contributions are very welcome! If you find bugs, have suggestions, or want to contribute new features, please submit an issue or a pull request.

---

License

🍷 Dartonic is released under the MIT License. See the LICENSE file for more details.

---

Made with ❤️ for Dart/Flutter developers! 🎯